between [date] and [date] problem

[ajb_1976]

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

 

[Smart]

RE; between [date] and [date] problem

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

 

[MStef]

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

 

[namliam]

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

MM/DD/YYYY

 

[neileg]

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.

 

[NoFrills]

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.

 

[NoFrills]

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.

 

[NoFrills]

"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.