Date causing problem with filter?

[AltaEgo]

I am trying to add a filter to an amend form.

In the header, I use the code below in a combobox (single column count).

The code dies on the line DoCmd.ApplyFilter , fltStr with the message "you
can't use the applyfilter action on this window".

How do I work around this?


Private Sub cboFindByDate_AfterUpdate()
Dim strCriteria As String
Dim fltStr As String

strCriteria = Me.CboFindByDate
fltStr = "xraydate ='" & mystr & "'"

DoCmd.ApplyFilter , fltStr


End Sub

 

[AltaEgo]

I'll revise that question
I now have it working using code below BUT it fails on the first selection
change (i.e. first time I use it results in blank; next time gets the
correct result).

Private Sub cboFindByDate_AfterUpdate()
Dim strCriteria As String
Dim fltStr

strCriteria = Me.CboFindByDate
fltStr = "xraydate = " & "#" & strCriteria & "#"

DoCmd.ApplyFilter , fltStr
Me.FilterOn = True

End Sub

 

[Rob Parker]

I tried your code in a test form, and got a couple of errors - neither of
which match the one you get.

I'm assuming that the rowsource for your combobox is something like:
SELECT DISTINCT xraydate FROM yourTable;
so that it will show a list of dates to choose from.

First, there's a compile error: mystr is not defined, in the line which
assigns a value to fltStr. I suspect that this should be strCriteria.

Next, if I make that change, then I get RTE 2501 "The ApplyFilter action was
cancelled", rather than the error you report. That's because the date must
be delimited with # characters, rather than ' characters. If I make that
change, it works as expected. So, try this:

Private Sub cboFindByDate_AfterUpdate()
Dim strCriteria As String 'misleading if it's a DateTime datatype
Dim fltStr As String

strCriteria = Me.CboFindByDate
fltStr = "xraydate = #" & strCriteria & "#"
DoCmd.ApplyFilter , fltStr
End Sub

Note also that, unless you have your system date set to mm-dd-yyyy, there
may be other problems with this (as for me, here is Australia, with default
dd-mm-yyyy). If you need help with that, see the following page on Allan
Browne's site:
http://allenbrowne.com/ser-36.html

HTH,

Rob

 

[AltaEgo]

Rob,
Thank you. My 'here' is also Australia.

I remembered the '#' around the date after writing and found the compile
error. This fix everything except that it does not work the first time a
date is selected in the combo (no records visible). Second time a date is
chosen (even the same date) it works like a charm. This has me totally
bamboozled.

My excuse for not remembering to wrap the date in '#' characters is I've
been away from Access programming for about eight years. You can forget a
lot of information in that time!

 

[Rob Parker]

All I can say is - it works for me, first-time, everytime (apart from the
problem with inconsistent date formats).

Sorry, I've got no idea what the problem you're experiencing might be.

Rob

 

[Allen Browne]

Rob,
Thank you. My 'here' is also Australia.

See:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

Identifies 3 cases where Access is likely to misunderstand our Australian
date format.

 

[Rob Parker]

Thanks Allan,

I'd referenced this page from your site in my original reply to the OP.

Rob

 

[AltaEgo]

I moved to another PC - problem gone!
--
Steve

Thanks Allan,

I'd referenced this page from your site in my original reply to the OP.

Rob