Multiple Filters on one Command button

[Guest]

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

 

[Douglas J. Steele]

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.

 

[Guest]

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]

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

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

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

 

[Douglas J. Steele]

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)



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

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]

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

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

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