Date causing problem with filter?

  • Thread starter Thread starter AltaEgo
  • Start date Start date
A

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
 
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
 
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
 
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!
 
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
 
Thanks Allan,

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

Rob
 
Back
Top