# Thread: Date/time code (Access 97)

1. ## Date/time code (Access 97)

<img src=/S/scratch.gif border=0 alt=scratch width=25 height=29> We have a helpdesk database that marks each entry for date and time. I need to pull information for all entries created between 6 pm and 6 am. I tried setting up a qry use the time field and specifying 'Between #6:00:00 PM# And #6:00:00 AM#. However the results appear to run from 6 am to 6 pm. Reversing the am/pm doesn't clarify the situation. Any suggestions?
Thanks

2. ## Re: Date/time code (Access 97)

I believe the problem here is that 6pm is on a different day that 6am - ie you want to see all records between 6/20/2001 06:00 PM and 6/21/2001 06:00 AM. Unfortunately, Access embeds the time into a field as a date - even if you strip out the time - so it gets a bit tricky. You will probably need to have something that calculates your date values based on the current time. The potential gotcha then becomes the correctness of the PC clock date. Life is never simple, is it!

3. ## Re: Date/time code (Access 97)

Your problem is that you need to tell Access that the time starts at 6:00pm on one day, and ends at 6:00am THE NEXT DAY! You didn't say whether you were using 1 field or 2 for date and time, so I'll assume you have one field. I would use this formula in my selection criteria, given a specific starting date:

Between (#9/20/01# + .75) AND (#9/20/01# + 1.25)

This becomes clearer if you understand how Access keeps a date/time field. It is a scaled integer, in which left side of decimal is # of days and right side is the fractional part of a full day. 6pm is 3/4 of the way to a full day. 1.25 is 1 full day + 6 hours of another day.

4. ## Re: Date/time code (Access 97)

I would set up a calculated field in the query like this:

<pre>C1: Hour([DT])
</pre>

then use a criteria like this:

<pre>Between 18 And 25
Between 0 And 6
</pre>

5. ## Re: Date/time code (Access 97)

Yuck! I have two fields one for the date which uses a code that allows the user to specify the time period so from Aug 1 to Aug 31 and then another field for the time in which I want anything not between 6 am and 6 pm - maybe that would work. If I change the code to read before 6 am and after 6 pm? Hmmm.

6. ## Re: Date/time code (Access 97)

OK - I did use 2 separate fields - one for date and one for time. Since I'm not trying to rewrite the database just run a report from it - this code won't work - although I appreciate the suggestion. Thanks.
Maybe if I specify everything NOT between 6 am and 6 pm?

7. ## Re: Date/time code (Access 97)

No problem. Just add the 2 fields together!

8. ## Re: Date/time code (Access 97)

Hunh? In the query?

9. ## Re: Date/time code (Access 97)

OK - I'm still thinking about this one.

10. ## Re: Date/time code (Access 97)

Yes - it turns out you can add to date/time fields together to get a true date value if you have the date in one field and the hours, minutes and seconds in another. You simply create an expression in the query that sums the two fields together.

One thing to think about here however is how you deal with month and year boundaries. I presume you are using a parameter query to let the user put in the month, or they are putting it in a form which you then reference. Do you want to get data for September 1between midnight and 6am and exclude date from August 1 between midnight and 6am (or vice versa) to give you the kind of month the user expects? It will make your problem more complex if you do. Same thing for years, though you may not care all that much about New Years eve and New Years day.

11. ## Re: Date/time code (Access 97)

Yep. Create an expression like this: [YourDateField] + [YourTimeField]. Then use the previous selection criteria I gave you against this expression.

12. ## Re: Date/time code (Access 97)

It works, and you don't have to split the date and time into two different fields.

13. ## Re: Date/time code (Access 97)

OK - I got the time section to work by breaking up the time values as suggested in the query. Thank you.

14. ## Re: Date/time code (Access 97)

OK - the time field is now working properly. I broke it down into two pieces of criteria: one from 6 pm to midnight; one from midnight to 6 am. However the date field criteria isn't working. It's set to >#01/01/2001# but gives me everything from Jan 2000 [the beginning of the data]. Because these fields are essentially the same can I only have one in a query?

15. ## Re: Date/time code (Access 97)

Finally, I got it to work with both date and time. The date problem had to do with the fact that I hadn't lined up the date criteria with both of the time criteria. Anyway, I got it to work now - switched the view over to SQL and realized what the problem was. Thanks to everyone for their help. Much appreciated.

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•