Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS ACCESS & have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for

Sponsored Links:

Related Messages:
Count Overdue Items?
I have a database for booking items in and out and would like the database to automatically count the number of items that are overdue.

I have a COMPLETED field which is a check box, when ticked completes the booking, if it is not ticked then the booking is incomplete.

I also have the "Date Returned" which is the date the customer is supposed to return the items.

So using these two fields how can I count the number of overdue items?

if COMPLETED=false and Date Returned<Date() +1

that's basically the logical expression I think would work, but I don't know the code/method to implement this into my database :x please help!

View Replies !   View Related
Dates In A Query
I have a report that once clicked a form comes up to choose the dates one would like to filter by. It has a Start Date and End Date. After the user picks the Start Date and End Date the report will be shown for those dates. The record source for this report is a query

I wanted to use this same date picker form for another report. I included the same expression into my query but I keep getting an error.

Field: Expr2: [LogDate] Between [forms]![frmWhatDatesWorkLog].[txtRecvStart] And [forms]![frmWhatDatesWorkLog].[txtRecvEnd] Or [forms]![frmWhatDatesWorkLog].[txtRecvStart] Is Null Or [forms]![frmWhatDatesWorkLog].[txtRecvEnd] Is Null

Criteria: True

When I tried that I get the following error: "The Microsoft Jet database engine does not recognize '[forms]![frmWhatDatesWorkLog].[txtRecvStart]' as a valid field name or expression.

I don't know why I get this because I use the exact same thing in the other query and it works perfectly fine.

Any suggestions?

View Replies !   View Related
Help With Dates In Query
Hi All

This is pretty urgent so your help would be greatly appreciated! I need to do a query where i can find companies who were telemarketed between the dates of 01/01/2006 and 31/02/2006. When i type this in the criteria box however, it fails to do anything. PLEASE HELP!!!

Many thanks

View Replies !   View Related
Dates In Query
I am trying to create a query in Access to select records from a table depending on the value in a Date field.

I need the query to select records which have dates between or equal to 01/09/last_year and 31/07/this_year.

Can anyone advise how I can do this?

Thanks in advance if you can help.


View Replies !   View Related
Help With Dates In Query
Hi all...

I have two queries feeding into one query that gives me a desired result. However I need it to do this..

Right now is show everything that has an incident On 07/04/2006 ( #07/04/2006# ) and before.. that part works.... What I would like it to show is if it has a hit for any 07/04/YEAR..... so if there is an incident for 07/04/2005 and an incident for 07/03/2004 show me...

Right now all of my dates look like this in the criteria section of the queries:
(one query looks for same locations on 7/4/2006, one query looks for that same location on or before 7/4/2006 and the last one tells me if there are any matchs)

q1= #7/4/2006#
q2= <=#7/4/2006#
q3= <=#7/4/2006#

How do I show for any year??

View Replies !   View Related
Dates Query
Hi there,

I'm making a database for a small library collection. I want to make a query for all overdue books, so if the Date which the book is due has passed, it will come up in the query. What do I put beneath the 'Date due' field?

So If 1 day has passed that date, it will be overdue.

Thanks for any help.

View Replies !   View Related
Query With Dates, Pls!

i want to create a booking system but i've a problem

i've these tables


_ car_id
_ car_name


_ book_id
_ book_start
_ book_end
_ car_id_fk

and the following data:

car 1: booked from 11.01.2007 until 14.01.2007
car 1: booked from 19.01.2007 until 24.01.2007
car 2: booked from 02.01.2007 until 28.01.2007
car 3: booked from 25.01.2007 until 29.01.2007
car 4: no booking

how can i make if i want that a query show the available cars
for the period (example): 12.01.2007 - 16.01.2007 ??

'cause i've found something and it'll show me car 3 and car 1 available..but the problem is that car 1 is still out on the 12th of january..(cause this query will show only the cars already booked in other periods that don't interfere with the request).

How can i make a query that shows the cars available and NOT those periods??

thx in advance

View Replies !   View Related
Between Dates Query
Im trying to build form with two calendar controls which asks query for records between two dates.
Calendar control works fine, no problem with that, but my query isnt.

What im trying to do:
I have a table with records, each record has field with date of creation (for example 2007.11.25.) formated as date/time (im working with latvian local settings)

In my form, I have a two text fields which referes to each calendar control, so in query (whith is trigered by comand button) i have formula like this:

Between [Forms]![Form1]![text5] And [Forms]![Form1]![text7]

Everything seams to be fine (for me :)) but if im searching dates between 2007.01.01. and 2007.03.01. im getting not only correct records between those dates, but also random records from last year.

I have tryed many diferent formulas, but nothing is working, Im stuck in this.

Ofcourse, my problem is that Im not good with VBA :) I dont understand it at all :)

Sorry if someone allredy posted solution of my little problem somewhere in Jungles of these Treads :)

View Replies !   View Related
Help With Query And Dates

I've created a query that finds all of the dates for specific types (monthly, quarterly, semiannual, annual) of evaluations. It works great, shows all evaluation dates for each person.

What I really need it to do is just show the most recent (i.e. latest) evaluation date for each person. Each person could have 10 or more records; just need the latest one. Eventually want to update a table that has a last evaluation field on it for every person.

I have set the top values to 1, but that just showed the latest of all the evaluations ( person). I couldn't seem to find any clues in the forums. Anyone have some suggestions/pointers?

I've attached the query and main tables.


View Replies !   View Related
Between Dates Query
Hi everyone, I am a relatively inexperienced access user and I am having problems with a query on my database.

Im creating a hotel reservation booking system and I want to run a query that will show active bookings on a certain date. For example, if i enter the date 2/1/2008 I want it to show bookings made from dates that span this date.


Booking Start Date Booking End Date
31/12/2007 4/1/2008
1/1/2008 3/1/2008

So now if I enter 2/1/2008, I want these 2 records to appear. Any advice about how to do this? It all seems a bit complicated to me

View Replies !   View Related
Query With Dates
Dates drive me nuts!!

attached is a db. I'd like a query to count BMdetentions and BmRTC in a 30 day period where the count is >2.

for the life of me I can't seem to figure it out.

Any help is appreciated

View Replies !   View Related
Can You Query A Range Of Dates With A Range Of Dates
I have a client that wants to enter a range of dates in a query of when they will call that person back. Then they want to be able to type in a range of dates and have a make table query show them all the people that fall in between these two this even possible???
Joe March 3 to March 8
Mary March 4 to March 9
John March 5 to March 10

So if they type into the query March 3 to March 6 all three people should show up because one of the dates specified lies within the parameters they are asking I am out of ideas


View Replies !   View Related
Probelm With Query Using Dates
I am having an annoying problem that I can't understand. I have the following query:

SELECT Bookinfx.Key, Bookinfx.[Park Name], Bookinfx.[Start Date], Bookinfx.[Accom Type]
FROM Bookinfx
WHERE (((Bookinfx.[Park Name])="TORBAY") AND ((Bookinfx.[Start Date])>=#01/01/2005#) AND ((Bookinfx.[Start Date])<=#31/12/2005#));

Which I am using to find out bookings for a specific holiday park for a specific year. However, if I try to alter the parameters to find monthly figures, only some months work and others (feb, april, june, sept, nov) do not. When I try and use these months it tells me that there is a syntax error in the date in the query expression.

Any help gratefully received! :confused:

ps maybe the answer would be a different query that returned the total number of bookings for each month for a particular year and park? But if this is the case I'm not sure how to do that!

View Replies !   View Related
Crosstab Query - Dates?

I have a crosstab query with the following column which calculates the number of days an employee took to perform a task. Problem is that it is also counting weekends and holidays.

Field: LogDate
Table: tblWorkLog
Total: Count
Corsstab: Value

Is there anyway to exclude weekends and holidays from that value?

I have a table, tblHolidays, with the field, HolidayDate. Is there a function that I can use like the WeekDay and use that with the HolidayDate field so that I can only get the Number of Work days?


Sorry I would also like to clarify something I have only one date field. What's happening is the user logs in and inputs what he did for the day, and the date is logged.

So the crosstab query sums up those days, but I would like to exclude weekends and holidays.

View Replies !   View Related
Crosstab Query Dates
I print a report based on Query1, with the selection criteria for dates being taken from 2 fields on a form. The selection criteria reads:-

>=[Forms]![DTdates]![StartDate] And <=[Forms]![DTdates]![EndDate]

Simple and it works fine.

I now need to base a second report on a Crosstab query based on Query1. Follow me?

Now, I have tried using the selection criteria in both Query1 and in Crosstab_Query1 and I get the error message:- "The Microsoft Jet database does not recognise 'Forms!DTdates!StartDate' as a valid field name or expression."

How do I get round this or can I?

The bottom like is I want to fire off printing the report from dates input to a form.

Help please.

View Replies !   View Related
Calender Dates In Query
I`m using a calender to pick a start date and end date shown in two combo boxes. I can store this value in a table with fields StartDate and EndDate I want the user to be able to pick the dates and show query records between these dates I thought i could use something like - Between"StartDate","tblDate" And" EndDate","tblDate" but it brings a syntax error anyone help ?

View Replies !   View Related
Query Math With Dates
I want to have a query that will subtract a Due Date from todays date and tell me how many days are left/passed in either a positive number or negative just like the DateDiff Function. how do i do this?

View Replies !   View Related
Problem Using Dates In A Query
I have a form where the user can enter a start date and an end date
into two textboxes. The default for both textboxes is today's date.
The user can then view or print a report of transactions between those
two dates. I would like for the default to be the current day's

The report will not pick up any transactions from a table if the dates
entered are the same date. If the dates are different, it will get the
transactions. For example, a txtStartDate of 8/29/2006 and a txtEndDate
of 8/29/2006 will not pick up any data. A txtStartDate of 8/28/2006 and
a txtEndDate of 8/29/2006 will get the transactions.

Here is my query:

SELECT Transactions.*, Hoods.*
FROM Hoods INNER JOIN Transactions ON
WHERE ([Transactions].[TDate] Between
[Forms]![DateQuery].[txtStartDate] And
ORDER BY [Transactions].[TDate];

The report displaying the transactions is based on the query.

I'm almost certain that the reason for my problem is that the dates
are formatted as long in the table - 8/29/2006 11:13:00 AM. What would
be the easiest solution? If I format the start and end dates on my form
as long, I will need to use a default of today's date at 12:00 am for
the start date. How would I do that? I could also reformat the date in
the query so it will do an accurate search. What is the best way to do
that? Another option would be to change the date format in the
transaction table. I would rather not change it.

Thanks for any help.

View Replies !   View Related
Received Dates Query
I want to run a query that returns only records where we haven't received 2 items for them from a possible 4. I can't seem to get my head around it!
At the moment I have:-
Recd Date 1Recd Date 2Recd Date 3Recd Date 4
Is Null Is Null
Is Null Is Null
Is Null Is Null
Is Null Is Null
Is Null Is Null
Is Null Is Null

But obviously this isn't correct as it would return all records! I only want to show records, which haven't got 2 received dates.
I thought about counting the number of received dates in a record but wasn't sure how to code this up?

Any ideas gratefully received!

View Replies !   View Related
Between Dates Query Not Working
I have a query built by someone else that I think is the root of my problem. The query runs and selects Officers of an Insurance Company based on the dates in a table stating when they were officers. The problem is one of the date (enddate) is blank since he/she may be a current officer. The query reads ">[RPT_NAIC_AF1]![BeginDate] And <=[RPT_NAIC_AF1]![EndDate]" It is not returning all records after the start date which has no enddate. If I change the "And" to an "Or" I get the records I want, and a lot more so I know the problem lies here. Help please! I am a novice user but willing to learn.

View Replies !   View Related
Query Count Between Two Dates

I think this might be a typical question for query builders, so I apologize in advance for asking something so basic.

I have a form with two controls (start_date) and (finish_date).

Is there a way that I can create a query that will count the number of times a "source" has been entered into a table?

For example, I have a database where potential customers call and ask about our services. We ask them "Where did you hear about us?", hence the "source" field (which is a drop down combo box to normalize that field's data).
With this record is their "dateofcall" which is (obviously) a date field.

I'd like to create a report that will count the number of times a "source" has been entered between two dates "dateofcall" (the start and finish date above).

I have tried many types of queries and haven't found any success. The nice thing about the two form controls is that I can use those two controls for a variety of all types of queried reports. (the user enters a start and a finish date, fires a command button that generates a given report between those two dates). And it works well!

Can anyone help? I'd be most appreciative!


View Replies !   View Related
Query Start And End Dates
I'm new in Databases and Access, so my problem is very simple.
I have a table with three fields: Name (Prim. Key), Start Date (Prim. Key) and Cost.

I want to obtain a query with this fields:
Start Date
End Date (where the End Date would be one day before the next start date)

Thank you

View Replies !   View Related
Revise Dates Query
Can some help me with a query in access 2003 that looks at dates in this format example: 10/22/2007 and changes them to 10/22/07.

Thanks a million in advance!!!!

View Replies !   View Related
Is This The Proper Way To Query Dates
If I have 2 date fields (start_date and end_date)

And I want o create a query, that captures everything either on or betwen those 2 dates.

Would my query be

On the start date field.


Then on the end Date


I think it is, but just want to make sure that I'm not leaving data out.

View Replies !   View Related
Problems With Dates In Query
I have the following SQL code in one of my queries that generates a report(this is only a section of a much longer query).

HAVING (((tblSegments.sold) Between #12/1/2004# And #12/17/2004#)

its fairly simple and straight forward however when I run the query it will not pull up the following date:

12/17/2004 8:19:36 AM

If I cange the date selection the 12/18/04 it will select this date and include it in the query. Is there something with having the time in the field and using Between in the query?

View Replies !   View Related
Getting Most Recent Dates Query, Help
HI, I have a table with multiple records of inspections, so there are multiples of inspection_num and each has a different date. I only want to extract the most recent inspections? I have an example of the table below.

Inspection_num Completed_Date

1000 01/01/04
1000 04/01/03
1000 01/01/03
1003 01/01/04
1003 02/01/04
1004 01/01/04

So I would only like to extract the following:
1000 01/01/04
1003 02/01/04
1004 01/01/04



View Replies !   View Related
Help With Dates, Query And Report?
I have this pretty difficult thing that I would love to get done.

In table the user can set two dates, the begin date for a period and the end date for that same period. The time between the two dates is the time employee has been working.

Now, I'd need the query to show the first of ALL dates on one person and the LAST date. So, if the person has been working first between 1.1.2008 and 31.1.2008 and then between 1.2.2008 and 29.2.2008, the report should show that the person has been working 1.1.2008 - 29.2.2008.

I've tried all I could think of, but can't figure out how this is done.

Also, I've been wondering if it's possible to make the query show if the employee has been away, as in, if the person hadn't been working for couple weeks, let's say 1.3.2008 and 14.3.2008, but then worked between 15.3.2008 and 31.3.2008, the report would show that the person has been working
1.1.2008 - 29.2.2008
15.3.2008 - 31.3.2008.

Heh, that's whole lot of things to do. I hope that was enough information for someone to be able to help me!

View Replies !   View Related
How Would I Build This Query? Where/MAX Dates
select student from tbl_TableName where max(date_borrowed) <= dateadd("d",-730,Date())
group by student

??? How do I set this up? and do the max date_borrowed?


View Replies !   View Related

Copyright 2005-08, All rights reserved