Why cannot I not find a date? 1

[Petemush]

I have an enormous piece of code for searching through records and returning a filtered form.

I build up a filter string and then apply it to the form.

I have a date field in the filtered form so the filter string that is generated for it is like the following:

(([Date of Birth] = #07/08/82#))

This doesn't work even though this date does exist. What's more confusing is

(([Date of Birth] < #07/08/82#)) and
(([Date of Birth] > #07/08/82#))

both work and filter the form correctly. I've tried using Like instead of = but nothing changes. Can anyone help?

 

[StarPassing]

In your code after the Me.Filter=&quot;[Date of Birth] = #07/08/82#&quot; you need to add a new line: Me.FilterOn = True

 

[Petemush]

Thanks but I realise that and already have the necessary line.

Cheers,

Pete

 

[cmmrfrds]

The datatime data type has a date and time part. If the date was updated in the database with the time part filled in then looking for just the date would not return an equal condition. To test this display your dates and see if time is also part of the date. You can mask out the time part of the date by using Format - you will need to check syntax but I believe it looks like. Format(yourdate,&quot;Short Date&quot = #7/10/02# then it will ignore the time part.

 

[Petemush]

No I don't use the time part of the date/time. All my dates are set to short date. And if that were the case surely
(([Date of Birth] < #07/08/82#)) shouldn't work yet it does.

Any more ideas anyone? I'm sure this shouldn't be so difficult!

Cheers,

Pete

 

[danvlas]

If you're building the filter from VBA code, try the format function
...(([Date of Birth] < #&quot; & Format(YourDate,&quot;mm/dd/yyyy&quot & &quot;#))

or

...(([Date of Birth] < #&quot; & Format(YourDate,&quot;Medium Date&quot& &quot;#))

If it doesn't help, try a trick:
(([Date of Birth] <= #07/08/82#)And ([Date of Birth] >= #07/08/82#))
This is equivalent to having [Date of Birth] = #07/08/82#

Dan

 

[Petemush]

Thank you so much Dan, for some reason it works correctly when I change the format to medium date rather than short date. Any idea why?

Once again thanks, and thanks to starpassing and cmmrfrds for their suggestions too!

Cheers,

Pete

 

[danvlas]

When passing dates to SQL through VBA you MUST use US/English format.

Using Medium Date is like passing a literal month, which forces the program to evaluate it correctly.

Dan