show all records when criteria for DATE field is null.

[Homegrownandy]

I've been doing a bit of reading and I'm yet to find an answer. If I want to see all results for a blank text string its easy.

Like "*" & [Forms]![forname]![Comboname] & "*" Or Is Null

something along those lines will do the trick. However. Doing this with dates. I cant see anything useful. This is what I'm using:

<[Forms]![Expirey_dates_all]![Date_Filter]

I'm assuming its possible and if it is, its probably easy. Anyone done this before?

This is the last part of this form and ill be happy to say I'm never opening it again. Its been one of the more difficult ones haha.

[andy49]

Date between function andy? Use two dates that must catch all data?

[Homegrownandy]

If there is no date entered then I want it to show all. Otherwise show values before the date.

I have a workaround. On an update event I change the year to 2099 in the textbox. But I don't think its obvious or makes much sense to the user.

[andy49]

But if you set that in vba they'd never know?

[Homegrownandy]

I'm sure this could work. But I cant think how to implement it. at the moment the form query references the textbox.

Are you suggesting to use the SQL in VBA then use an if statement to see if the textbox contains the value?

[andy49]

Well what about just making the text invisible when they press the button If the txt box value is null?

[Homegrownandy]

or, if the textbox =2099.

Could work. Ill have a go tomorrow. Thanks for the input.

[andy49]

How about this:

Private Sub cmdSearch_Click()
Dim myfilter As String
myfilter = "Year([trandate]) > " & CInt(Nz([Txtsearch], 1900))
Me.Filter = myfilter
Me.FilterOn = True
End Sub

or similar