i have a query that limits the search results by their date. i have a form that has 2 date/time pickers both bound to a field start and end date respectively. The query criteria for the date is "Between [Forms]![frmMain]![StartDate] And [Forms]![frmMain]![EndDate]". This works fine except for the fact that any items whose date is equal to the start date are ignored. i.e. majority of searches are limited to first to last of month so any items with a date of 01/07/2005 are ignored, the only way to include these items is to change the start date to a day prior, e.g. 30/06/2005. Is this an issue with the date/time picker or is this how the between/and statement functions?
Thanks

check and make sure that the start and end fields on the table are in date format (they probably are)

It is more likely that the date picker fields on the form are not in date format

I suspect on your SYSTEM DATE format.
Check it, it must be DD/MM/YYYY

Nope not the system format, in fact it must be in AMERICAN format.

MM/DD/YYYY

I think the problem is a bit more subtle. A date field is not just a date, it's a date and time. And really, although it looks like a date and time when formatted, it's really a double precision decimal. The integer part is the date and the decimal part is the fraction of the day that is converted to h:m:s.

When you start doing comparisons between two date fields, you get into trouble with these fractions, and you have to decide how to handle this.

Now, although I am happy with this explanation being accurate, it is usually the dates at the end of the period that get missed, because 31/7/2005 12:25:10 is after 31/7/2005. But I hope this is enough to help you solve your problem.

Neileg, Not to Hyjack the thread, but I am having a problem with doing a Query that involves Date and Time. Is there a way to do a Query and a Date and Time field only ONLY the date. I would like to Count the occurance of a date but becuase the field also has time within it, it does not see 1/1/2005 12:00pm as 1/1/2005 1:00pm. So each date is coming up as 1 count, as suppose to the 2 I want for 1/1/2005.

Use the DateValue() function to obtain only the date. Use TimeValue() to obtain only the time.

Where DateValue(YourDate) Between DateValue(dt1) and DateValue(dt2);

Embarassing how simply the solution is when you see it. I didn't even know about that function. After hours of playing around with it, it was solved in less than 2 minutes.

Pat you are a good man. Thanks.

My husband might disagree;)

PS, I blame this lack of knowledge of functions directly on Microsoft. They have so messed up the help feature in Access that it is virtually impossible for a person to find a function whose name he does not know! Earlier versions used to have entries for categories so you could get all the "date" functions or all the "math" functions or all the "string" functions in one list. That made it much easier to find appropriate functions even when you didn't know their name.

I posted a db in the Samples (I think) that lists all/most of the functions I was able to identify and categorizes them so you have a chance at finding the appropriate one. Search for it if you are interested.

"Embarassing" twice now.

Sorry about that. Trying to become a regular here and pick up some knowledge. Hopefully I will only be making coding errors and not making people errors.

Sorry, tell him I said Hi.