search on daterange when fromDate = toDate

[camillaj]

Can someone please help me with this neverending date problem in Access.

Dates are stored as mm/dd/yyyy time:min:sec.

I have a daterange search that retrieves records from 'fromDate' and to 'toDate'. 'fromDate' and 'toDate' are only day, month, and year.

The search works fine if 'fromDate' and 'toDate' are different, but if they are the same (user only want records from one particular date), then it doesn't retrieve any records. This is part of my query:

&quot;DateCreated >= #&quot; & fromDate & &quot;# AND DateCreated <= #&quot; & toDate & &quot;# &quot;

The reason is probably because dates are stored with the time as well. Then, DateCreated will never be equal to 'fromDate'. How can I handle this? I do not really have to store time in DB, but that seems to be necessary in access??!

Can someone please help me with this.
Appreciated

 

[dhookom]

DateValue([DateCreated]) >= #&quot; & fromDate & &quot;# AND DateValue([DateCreated]) <= #&quot; & toDate & &quot;# &quot;

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[TonyJollans]

Hi camillaj,

You are absolutely right in saying that it is the times which are causing your problems. An alternative to Duane's use of the date portion only would be to ask for date/times less than the day after your end date ..

&quot;DateCreated >= #&quot; & fromDate & &quot;# AND DateCreated < DateAdd(&quot;&quot;d&quot;&quot;,1,#&quot; & toDate & &quot;#)&quot;


Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.