Filter by Date

[Maestro]

Using te same method as I mentioned recently I'm trying to have a field on my form to enable me to filter records by their date. Code I'm using is the same as the text variables on the After_Update as follows:

Dim strFilter As String
strFilter = "[ExpiryDate] = '" & Me.srcExpDate & "'"
DoCmd.ApplyFilter , strFilter

The problem is that because the table holds a date variable for this value, all I get when I enter anything in this field is a run time error. How can I get this to wotk properly.

And, for a bonus point

, if that code above were for a normal text variable, how would I modify it to incorporate the 'like' operator. Again, all my attempts seem to result in run time errors.

 

[Chris RR]

Off the top of my head:
strFilter = "[ExpiryDate] = #" & Me.srcExpDate & "#"

And I would make sure that srcExpDate is formatted as a date/time field.

Let's try for extra points:

strFilter = "[FIRST_NAME] like *" & Me.txtFIRST_NAME & "*"



[This message has been edited by Chris RR (edited 11-12-2001).]

 

[Maestro]

Thanks, Chris. That works much better now EXCEPT that dates have to be entered in American format mm/dd/yy to be recognised, and my organisation is UK based so we'll have to remember to swap the format around each time we use it.

Like operator still gives a run time error unfortunately. I never thought it'd be so difficult to get such a simple thing to work.

 

[Rich]

Use a parameter query and keep the UK date format.