Filter code

  • Thread starter Thread starter Sport
  • Start date Start date
S

Sport

I have found an example for filtering on a form, but I am not sure why the
double quotes were used. Can some on fill in this blank for me?

Take a Products form with a ProductCategory field. With an unbound combo in
the form's header, you could provide a simple interface to filter products
from one category. The combo would have these properties:
Name cboShowCat
ControlSource ' Leave blank.
RowSource tblProductCategory 'Your look up table.
AfterUpdate [Event Procedure]

Now when the user selects any category in this combo, its AfterUpdate event
procedure filters the form like this:

Private Sub cboShowCat_AfterUpdate()
If IsNull(Me.cboShowCat) Then
Me.FilterOn = False
Else
Me.Filter = "ProductCatID = """ & Me.cboShowCat & """"
Me.FilterOn = True
End If
End Sub
 
It depends on the type of field.

In the example you found, ProductCatID must have been a Text field.
The value to find needs to be placed in quotes for Text fields.
For example, the Filter string (like the WHERE clause of a query) needs to
end up looking like this:
ProductCatID = "DogFood"

But the whole thing needs to be in quotes. Access cannot make sense of:
"ProductCatID = "DogFood"" 'Error!!!
because when it comes to the quote mark before DogFood, it things the string
is closing, and does not know what to do with the rest of the line. To embed
a quote mark inside quotes, the convention is to double them up, so you
need:
"ProductCatID = ""DogFood"""

When concatening a value into a string, you use:
"ProductCatID = """ & SomeValue & """"

You can avoid this issue by using a single quote mark instead of double
quotes:
"ProductCatID = '" & SomeValue & "'"
but that fails on words that words that have apostrophies.

Just as the quote marks are the correct delimiter for Text fields, use # as
the delimiter for date fields, e.g.:
"OrderDate = #" & Date & "#"

Another way to try to avoid this whole delimiter thing is to use
BuildCriteria.

HTH
 
Back
Top