Hi Steve,
AND needs to be in the quotes...
Me.Filter = "OfficerLastName ='" & OfficerLastNameCbo & "' AND
OfficerFirstName = '" & OfficerFirstNameCbo & "'"
I like to use the line continuation (space underscore) at logical points
on long lines to make statements easier to read... I also like to use a
variable instead of assigning to the property directly -- this way, if
there is a problem, you can test the string.
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
dim mFilter as string
mFilter = "OfficerLastName ='" & OfficerLastNameCbo & "'" _
& " AND OfficerFirstName = '" & OfficerFirstNameCbo & "'"
Me.Filter = mFilter
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
after you set the filter, you need to requery the form for it to take effect
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Me.Requery
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
.... but what if the user wants to filter by just last name or just first
name or both? what if something isn't filled out? here is some generic
info on form filters...
Set Form Filter
It would be best to build a filter string for the form (as opposed to
imbedding a parameter in a query)
put comboboxes, textboxes, listboxes, etc on the form (i put then in the
header). Assign this to the AfterUpdate event of each one...
=SetFormFilter()
then put this code behind the form
'~~~~~~~~~~~~~~~
Private Function SetFormFilter()
dim mFilter as string
mFilter = ""
If not IsNull(me.text_controlname ) Then
mfilter = "[TextFieldname]= '" _
& me.controlname_for_text & "'"
end if
If not IsNull(me.date_controlname ) Then
if len(mFilter) > 0 then mFilter = mFilter & " AND "
mfilter = mfilter & "[DateFieldname]= #" _
& me.controlname_for_date & "#"
end if
If not IsNull(me.numeric_controlname ) Then
if len(mFilter) > 0 then mFilter = mFilter & " AND "
mfilter = mfilter & "[NumericFieldname]= " _
& me.controlname_for_number
end if
if len(mfilter) > 0 then
me.filter = mfilter
me.FilterOn = true
else
me.FilterOn = false
end if
me.requery
End Function
'~~~~~~~~~~~~~~~
me.controlname_for_number refers to the NAME property of a control on
the form you are behind (Me. represents the form -- kinda like "me" for
me is not "me" for you
)
delimiters are used for data that is not a number
quote marks ' or " for text
number signs # for dates
mfilter is a string that is being built for each condition -- but if
nothing is specified in the filter control (IsNull), then that addition
to the filter string is skipped.
finally, when the filter string is done, it is applied to your form.
That means that as you flip through records, ONLY records matching that
filter will show
Then, put another command button on the form
Name --> btnShowAll
OnClick --> [Event Procedure]
'~~~~~~~~~~~~~~~
me.filteron = false
me.requery
'~~~~~~~~~~~~~~~
Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
clueless_steve said:
Hello
I am trying to filter a form by 2 fields using the me.filter command.
Currently I have
Me.Filter = "OfficerLastName ='" & OfficerLastNameCbo & "'"
which filters the form to show only the officer with the same lastname as
selected in the combo box OfficerLastNameCbo. This code is linked to the
afterupdate command of OfficerLastNameCbo. OfficerLastName is a field in a
table.
But now I want to filter by the first name as well so I want to add the
following to the end of the Me.Filter line
AND "OfficerFirstName = '" & OfficerFirstNameCbo & "'"
but I can't seem to get the syntax right.
Any suggestions gratefully received.
Steve
