Query date range

[Sue]

I have a query asking for records whose field (CombinedDate) is not null.
I'd like be able to pull out only records whose CombinedDate occurs within
the next 2 weeks.

I have trouble creating expressions for dates & don't really catch when to
use DateDiff, DateAdd, or something on the order of Date()+.

Appreciate your help.

 

[John Spencer]

Field: CombinedDate
Criteria: Between Date() and DateAdd("d",13,Date())

Use DateAdd when you want to create a date.
Use DateDiff when you want to determine the difference in two dates
Use Date() + when you want to add a specific number of days or partial days
to a date.

DateAdd allows you to add time intervals to a date to get another date (so
you can add Years, weeks, months, days, hours, minutes, etc). Date() +
allows you to add days (and partial days) to a date to get another date, but
no other intervals.

So you could do the above criteria as
Criteria: Between Date() and DateAdd("d",13,Date())
Or
Criteria: Between Date() and Date() + 13


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Sue]

That returns a void query even though one of the dates in question in 5/16
(which is < 14 days from today).
Any other suggestions?

 

[John Spencer]

Is CombinedDate a datetime field? Or is it a text field that contains
characters that make up a date?

You could try the following and if it works then CombinedDate is a text
field.

Field: DateValue(CombinedDate)
Criteria: Between Date() and DateAdd("d",13,Date())

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[ncsue0514]

Is CombinedDate a datetime field? Or is it a text field that contains
characters that make up a date?

You could try the following and if it works then CombinedDate is a text
field.

Field: DateValue(CombinedDate)
Criteria: Between Date() and DateAdd("d",13,Date())

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

No - I checked. The fields (DOB & DOA) from which combineddate is
derived are both dates - mm/dd/yyyy formatting - as is combineddate.