Date handling (1 Viewer)

[Jonny45wakey]

Hi I have a query called qryAppointments which has the date of appointment [ApptStart]

I would like to run separate queries to filter to records which are due 1 day greater than todays date
and another query which filters to >=7 days than todays date.

I've tried multiple date handlers but its not working

Any help appreciated.

Thanks

Jonny

 

[tvanstiphout]

Remember that Access is created by Microsoft, a US company. So you must use the US date format (or ISO) when using literal dates.
But in your case you can use the DateAdd function to add 1 day or 7 days to today. Something like:
select * from myTable where ApptStart > DateAdd('d', 1, Date())

 

[RonPaii]

What is not working?
What have you tried.

Access dates include time unless you take pains to remove the time.

Today does not end until midnight
If TheDate is > Now + 1 then

if TheDate is #3/27/26" and now is #3/26/26 11:03:02 AM# then FALSE

 

[Gasman]

I would just add/subtract the required number to the Date()
If you happened to include the time in the date field (always using Now() perhaps), then you can use DateValue() function.

 

[LarryE]

filter to records which are due 1 day greater than todays date

Query Criteria: > Date() + 1

filters to >=7 days than todays date

Query Criteria: >= Date() + 7

 

[Mark_]

Are you removing the TIME when saving ApptStart?
If you set ApptStart to NOW you will get date and time, but setting it to DATE just saves the date.

Unless you don't save time, >= DATE()+1 will, in effect, give you everything for the current day as it won't return anything from tomorrow that has a time attached.