Date Filter based off Text Box

  • Thread starter Thread starter mattmanp
  • Start date Start date
M

mattmanp

I have 4 text boxes set up that you type in a date in short dat
format. The four date data fields in my table are all set to shor
date format. When you press the button to filter I build a strin
that I set Me.Filter equal to, here is a string it built

"[ServiceDate] = #06/10/05# And [ReceiveDate] = #06/13/05# An
[CompletedDate] = #06/18/05# And [ScannedDate] = #06/13/05#

When I do any of the individually the ServiceDate filters correctl
but the rest all come up empty, as it does with any combination. Th
string above should yield atlest one record

When I set [ReceiveDate], [CompletedDate], and [ScannedDate] I ha
been using Now() whereas for [ServiceDate] I had been using an inpu
masked textbox to get the data. When I do this, I get just 06/10/0
for [ServiceDate] but would get the date and time for the othe
three, until I switched to Short Date format. Could that be why thos
three are coming up empty

Thanks for the help
 
If you have been using =Now(), the fields will contain a time component as
well as the date. That's why they don't match.

Change the defautl from =Now() to =Date().

Create a query to update the fields so they lose the time component. In
query design view, chose Update on the Query menu. Access adds an Update row
to the grid. Drag ServiceDate into the grid. In the Update row, enter:
DateValue([ServiceDate])
Likewise, in the Update row under ReceiveDate, enter:
DateValue([ReceiveDate])
Run the query.

Now the query has only date values (not date and time values), you should
find they will match.
 
Thanks, that's what I was assuming was the problem, but I was hopin
if you had a format on the data it would compare it as the formatte
version, but that would be too easy wouldn't it ;). Thanks again

Mat
 
Back
Top