Putting "today's date" in a criteria code 1

[misha8765]

Thank you in advance for your time.

I have a date field that states information as such:
10/30/2006 10:00:00 AM

Every morning I am looking to pull today's appointments.

The following code works, but I have to change the dates every morning:

Between #10/30/2006 8:00:00 AM# And #10/30/2006 5:00:00 PM#

How can I use the same code wihtout having to change the date every moring?

 

[AlexCuse]

Look at the DateAdd function and use 'Date()'. This will allow you to set both sides of your between criteria based on the current date.

Hope this helps,

Alex


A wise man once said
"The only thing normal about database guys is their tables".

 

[misha8765]

Thank you for your help.

I tried:

Between #'date()' 8:00:00 AM# And #'date()' 5:00:00 PM#

It doesn't work. Any other ideas?

 

[AlexCuse]

You need to use DateAdd function, you need to remove quotes from around Date(), you need to ditch the # signs, and you need to stop entering the time. If you read the help file on DateAdd it will be very helpful to you. In the meantime try this though:

BETWEEN DateAdd("h",8,Date()) AND DateAdd("h",17,Date())

A little background - Date() returns the very first time for today, ie 12 am. The first part of your between is that date/time plus 8 hours, or 8 am. The second part is date/time plus 17 hours, or five P.M.

Hope this helps,

Alex

A wise man once said
"The only thing normal about database guys is their tables".