Multiple Filters on one Command button

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JKaz - 10 May 2007 21:24 GMT

Hello,

I am using Access 2002. I have a form that I created for the users to enter
two parameters that will open another form.

Here are my steps:
1) I created the Parameter form with both parameters(filters).
2) I added a command button using the wizard.
3) The wizard only seems to let me choose one of the two fields to filter.
4) I looked at the VB code and here is what I saw:

Dim stLinkCriteria As String

stDocName = "Pick List C"

stLinkCriteria = "[Pref List ID]=" & Me![Text2]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command6_Click:
Exit Sub

5) I think I want to add the follwing LinkCriteria:

stLinkCriteria = "[Facility]=" & Me![Text0]

6) I am trying to add it as follows:

stLinkCriteria = "[Pref List ID]=" & Me![Text2] AND
stLinkCriteria = "[Facility]=" & Me![Text0]
DoCmd.OpenForm stDocName, , , stLinkCriteria

However, I do not know the proper (or any) syntax in VB.

Can someone please help?

1) Am I using the correct method?
2) What is the proper syntax?

Thanks in Advance

Jeff

Reply to this Message

Douglas J. Steele - 10 May 2007 21:34 GMT

stLinkCriteria = "[Pref List ID]=" & Me![Text2] & " AND " & _
"[Facility]=" & Me![Text0]

This assumes that both Pref List ID and Facility are numeric fields. If
they're text fields, you need quotes around the values.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> Hello,
>
[quoted text clipped - 38 lines]
>
> Jeff

Reply to this Message

JKaz - 11 May 2007 14:32 GMT

Doug,

Thanks so much. The Facility is actually a text. So do you mean the users
will have to include the quotes (like I did), or is there a way for me to add
the quotes within the code so it's transparent to them?

Thanks

Jeff

> stLinkCriteria = "[Pref List ID]=" & Me![Text2] & " AND " & _
> "[Facility]=" & Me![Text0]
[quoted text clipped - 44 lines]
> >
> > Jeff

Reply to this Message

Douglas J. Steele - 12 May 2007 12:57 GMT

stLinkCriteria = "[Pref List ID]=" & Me![Text2] & " AND " & _
"[Facility]='" & Me![Text0] & "'"

Exagerated for clarity, that's

stLinkCriteria = "[Pref List ID]=" & Me![Text2] & " AND " & _
"[Facility]= ' " & Me![Text0] & " ' "

Actually, though, that may not be the best choice. If the Facility can have
an apostrophe in it ((O'Hare), that will lead to problems. You may be better
off using:

stLinkCriteria = "[Pref List ID]=" & Me![Text2] & " AND " & _
"[Facility]='" & Replace(Me![Text0], "'", "''") & "'"

or

stLinkCriteria = "[Pref List ID]=" & Me![Text2] & " AND " & _
"[Facility]=""" & Me![Text0] & """"

or

stLinkCriteria = "[Pref List ID]=" & Me![Text2] & " AND " & _
"[Facility]=" & Chr$(34) & Me![Text0] & Chr$(34)

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> Doug,
>
[quoted text clipped - 57 lines]
>> >
>> > Jeff

Reply to this Message