Default Date Range

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have created a table that has a “date†field in it. I created a report
using a query that ask the user what date range they would like to see. In
the query I have in the Criteria section under the “date†field – Between
[Enter Start date] and [Enter End Date]. This works great when they enter a
date.

But when they forget to enter a date can I have it default to:
Start Date – Current Date less one day
End Date – Current Date.

Please help.
 
Mybe you can try that

SELECT MyTable2.StartDate
FROM MyTable2
WHERE MyTable2.StartDate Between IIf(IsNull([a]),"date-1",[a]) And
IIf(IsNull(),"date",)
 
To get JET to recognise the Date() function, I think you need:

WHERE MyTable2.StartDate
BETWEEN IIf(IsNull([a]), Date()-1, [a])
AND IIf(IsNull(),Date(), )

Wat ount for non-zero time component also.

HTH
Van T. Dinh
MVP (Access)
 
I have created a table that has a “date” field in it. I created a report
using a query that ask the user what date range they would like to see. In
the query I have in the Criteria section under the “date” field – Between
[Enter Start date] and [Enter End Date]. This works great when they enter a
date.

But when they forget to enter a date can I have it default to:
Start Date – Current Date less one day
End Date – Current Date.

Please help.
Click to expand...

Ofer made a slight mistake - rather than "date" use the Date()
function. You can also simplify the expression a bit by using the NZ()
function:
= NZ([Enter Start Date:], Date() - 1) AND < NZ([Enter End Date:], Date() + 1)
Click to expand...

The end date plus one allows for the possibility that your table date
field contains a time component.

John W. Vinson[MVP]
 
Back
Top