date query again

  • Thread starter Thread starter brigid
  • Start date Start date
B

brigid

Presently I have a query which, upon the user entering a
start date and an end date, returns all start dates and
end dates which fall in that range. However, if the user
just hits enter when prompted, it is recorded as no
entry. What I'm trying to do is set it up so that the
user can enter a start date and then just hit enter when
prompted form the end date, and it will automatically
default back to the start date, thus returning only the
entries for that given date. I hope this isn't too
confusing. Here is the code:

SELECT [calculated dates].calculatedFromDate, [calculated
dates].calculatedToDate
FROM [calculated dates]
WHERE ((([calculated dates].calculatedFromDate)>=[from
date] And ([calculated dates].calculatedFromDate)<=[to
date]) AND (([calculated dates].calculatedToDate)>=[from
date] And ([calculated dates].calculatedToDate)<=[To
Date]));

Thanks in advance!!
 
Try using the NZ function or the IIF function in your query. So in each place
where you have [To Date] substitute the expression

IIF([To Date] is Null,[From date],[To date])

SELECT [calculated dates].calculatedFromDate, [calculated
dates].calculatedToDate
FROM [calculated dates]
WHERE
((([calculated dates].calculatedFromDate)>=[from date]
And ([calculated dates].calculatedFromDate)<=IIF([To Date] is Null,[From
date],[To date]))
AND (([calculated dates].calculatedToDate)>=[from date]
And ([calculated dates].calculatedToDate)<=IIF([To Date] is Null,[From date],[To date])));
 
Back
Top