Date handling

[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.

 

[Ken Sheridan]

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.

That's not the case, as can be seen in the immediate window, as of today 27th March.

dtmAppointment = #2026-03-28 14:30#
? dtmAppointment >= VBA.Date+1
True

The inclusion of a non-zero time of day in the appointment date/time value is irrelevant as the expression will evaluate to True for any date/time value on or after #2026-03-28 00:00:00#

The same is true if the criterion is >= 7 days from today:

dtmAppointment = #2026-04-03 14:30#
? dtmAppointment >= VBA.Date+7
True

Where the time of day does come into play is when defining a range. Often this will be done like this:

WHERE AppointmentDate BETWEEN #2026-04-01# AND #2026-04-30#

Which will not return any appointments on the last day of the range as these will have time of day elements greater than zero. Only if an appointment was scheduled for midnight at the start of 30th April would it be returned, which is unlikely. To avoid this the range should be defined as:

WHERE AppointmentDate >= #2026-04-01# AND AppointmentDate < #2026-05-01#

Normally parameters would be used of course:

WHERE AppointmentDate >= [Enter start date:] AND AppointmentDate < [Enter end date:]+1

 

[Ken Sheridan]

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.

To return all appointments for the day following the current date a query would be along these lines:

SELECT *
FROM Appointments
WHERE AppointmentDate >= DATE()+1
AND AppointmentDate < DATE()+2;

To return all appointments on or later than the seventh date from the current date:

SELECT *
FROM Appointments
WHERE AppointmentDate >= DATE()+7;

The fact that the appointment dates might contain non-zero time of day elements is immaterial, as the DATE function returns the date and time at midnight at the start of the current date, so the comparisons will be made with that precise date/time value. It is sometimes mistakenly thought that the DATE function returns a value to a precision of one day. That is not the case.