Querying a date/time field

Topic

New Reply

Using <=Now()+30 to show me the next 30 days in a query. I am using this on a date/time field, formatted to 1/16/2010. For some reason when I query on this particular table it will not work. To get it to work I have to use a between #1/16/2010 # and #2/15/2010 #. I really don't want to go that route because I will have to change the date everytime it's run. Is there something else I can use? Is it a formatting issue with the field? Thanks.

Reply | Quote

Replies

What do you mean by “does not work?”
Does it produce an error? return too many results? too few results?

Now() returns the current date and time, Date() returns just the current date. That is why my previous post suggested the Date() function.
The Time component will add extra digits after the decimal point, and so could change the results returned a little.

Reply | Quote

You need to use the DateAdd function. Something like this should work:

<=DateAdd("d",+30,Now()))

Reply | Quote

You need to use the DateAdd function. Something like this should work:

<=DateAdd("d",+30,Now()))

Dates are stored in Access just as numbers, so you can use the DateAdd function, but to add a number of days you can just also use plain arithmetic.

Reply | Quote

Dates are stored in Access just as numbers, so you can use the DateAdd function, but to add a number of days you can just also use plain arithmetic.

Hi,

Maybe so, I won’t deny it, but after using a few DBs and languages and getting some issues sometimes, I have resorted to a common, always working strategy – use the date handling functions to perform date calculations.

Reply | Quote

By not work I should clarify. I mean it will show all the 2010 data not the next 30 days that I am looking for. I tried <=DateAdd("d",+30,Now()) . It gave me all tha data for 2010. Any other solutions? Thanks so much..

Reply | Quote

Do you mean all the 2011 Dates? Did you put in the extra test to exclude dates that have come and gone?

You need =Date() to exclude past dates.

Reply | Quote