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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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?
 
John,
I am doing a between request using a start and end date. The test is for
one year. I receive items only for the year range. Some items retreived
have the date and time, some only show the date. I get 15 items when there
are hundreds with a date, no time, entered within the date range.

Thanks,
Scott
 
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.
 
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
 
Do you have a datetime field that shows when maintenance is due?
Assumptions:
Datetime field that specifies date due for maintenance.
You want those items that have maintenance scheduled in the next (future) 30
days.

Field: DateDue
Criteria: Between Date() and Date()+30

If you want those that are overdue (for the last 30 days)
Criteria: Between Date()-30 and Date()

If you want those that are overdue at 30 or more days
Criteria: <Date()-30
 
Thanks for your respond
The MaintDueDate is set up as a text in my table
I tried Between Date() and Date()+30 and it shows me one equiment that was
due in Feb and couple equipments in March.
I need to run a report every month to get all the equipments that are due in
that month.
I have been working on this for a long time and I am not getting anywhere:-(
 
So if you run the report in February of 2006 you want to show all equipment
with a MaintDueDate in February and if you run the report in July 2007 then
you want all equipment with a MaintDueDate in July of 2007.

If that is correct

Field: MaintDueDate
Criteria: Between DateSerial(Year(Date()),Month(Date()),1) And
DateSerial(Year(Date()),Month(Date())=1,0)

That will get every MaintDueDate record in the current month of the current
year.
 
Hi John,
Yes, when I run the report lets say on March 1st, I want to see all the
equipments that are due in March and same thing in April
I tried what you suggested to do, but it gave me all the records in December.
Am I missing something???
Thanks alot for your help
 
Well, in a way you are missing something. I had a typing error. I had an =
sign instead of a + sign. That error caused the computer to search for
everything between Feb 1 2006 and November 30, 2005.

Field: MaintDueDate
Criteria: Between DateSerial(Year(Date()),Month(Date()),1) And
DateSerial(Year(Date()),Month(Date())+1,0)
 
I tried it and now I get equipments that are due in 2007. I want to get all
the equipments that are due in 2006
 
I don't see any typing error this time in what I entered.

Back to basics.
-- Is MaintDueDate a DATETIME field? Open the table in design mode and look
at the Type of the field.

-- Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message
 
I didn't have the field as date/time field
It worked.
Thanks alot.
I also have another report that I need to run all the equipments that are
due for calibration in 60 days. I assume that I will set the same criteria
except for 60 dyas???
Thanks so much John:-)
 
Back
Top