Access query on date/TIme field for date then there is no time?

B

Bank

I have a date/time field that I need to execute a date range search. The
field always has a date, but may not have a time. When I execute a between
date criteria I only get a few item when there are 100's in the range. Any
sugestions?

thanks
Bank
 
J

John Spencer

A dateTime field always has a time. The time is midnight if no time is input.

When it fails what is the date range you are looking for? If it is a one-day
range the problem may be that the field does actually have a time in it and your
format settings are "hiding" the time from you.

When you get just a few items, are they in the date range you have specified?
 
J

John Spencer

Can you post the actual SQL?

Also, check and make sure that datetime field is actually a date time field and
not a text field containing a string that looks like a date and a time.

One thing you might try

Field: RealDate: IIF([YourDateField] is Not Null,DateValue([YourDateField]),Null)
Criteria: Between #2005/01/01# and #2005/12/31#

That does two things - the calculated REALDate forces the value in the field to
be a date.
The criteria uses a date format that must be interpreted as Jan 1 2005 to Dec 31 2005.

If this works, then you can try entering dates in your standard method. If that
works, then you should be reasonably sure that the date you are searching for is
being treated as a string.
 
C

camilia

Hello,
I have a database that maintainthe equipments. I want to be able to run a
report to show me all the equipments that are due within 30 days for
maintenance
Hope some bodyy can help me with that.
Right now on my query I have <30, which doesn't do anything:-(

Thanks alot
 
Top