Pass multiple criteria with VBA


J

Jane

Hello all (again)

Please could someone tell me what I am doing wrong here. It "should" be
simple I think. But, of course this isn't working.
Note. Both of the criteria are numbers

I am trying to open a pop up form filtered with both the school and class so
I need to pass both criteria.

Private Sub ClassSelector_Click()

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmClassPoPUp"

stLinkCriteria = "[SchoolID] =" & Me![SchoolID]
stLinkCriteria = "[ClassID] =" & Me![ClassID]

DoCmd.OpenForm stDocName, , , stLinkCriteria

End Sub


Thank you for any help you can give

Jane Hollin
 
Ad

Advertisements

A

Administrator

Jane said:
Hello all (again)

Please could someone tell me what I am doing wrong here. It "should" be
simple I think. But, of course this isn't working.
Note. Both of the criteria are numbers

I am trying to open a pop up form filtered with both the school and class
so
I need to pass both criteria.

Private Sub ClassSelector_Click()

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmClassPoPUp"

stLinkCriteria = "[SchoolID] =" & Me![SchoolID]
stLinkCriteria = "[ClassID] =" & Me![ClassID]

DoCmd.OpenForm stDocName, , , stLinkCriteria

End Sub


Thank you for any help you can give

Jane Hollin
 
D

Damon Heron

You are assigning a new value to the variable twice. Try
stLinkCriteria = "[SchoolID] =" & Me![SchoolID] & " AND " & "[ClassID] =" &
Me![ClassID]

Damon
 
J

Jane

Dear Damon

That's perfect.

Thank you for your help

Jane



Damon Heron said:
You are assigning a new value to the variable twice. Try
stLinkCriteria = "[SchoolID] =" & Me![SchoolID] & " AND " & "[ClassID] =" &
Me![ClassID]

Damon

Jane said:
Hello all (again)

Please could someone tell me what I am doing wrong here. It "should" be
simple I think. But, of course this isn't working.
Note. Both of the criteria are numbers

I am trying to open a pop up form filtered with both the school and class
so
I need to pass both criteria.

Private Sub ClassSelector_Click()

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmClassPoPUp"

stLinkCriteria = "[SchoolID] =" & Me![SchoolID]
stLinkCriteria = "[ClassID] =" & Me![ClassID]

DoCmd.OpenForm stDocName, , , stLinkCriteria

End Sub


Thank you for any help you can give

Jane Hollin
 
M

Marc

I am not able to get this to work as my fields are both text. Can someone
help me get the quotes and ! correct for 2 text filed criteria using the same
example below?

Thanks.

Damon Heron said:
You are assigning a new value to the variable twice. Try
stLinkCriteria = "[SchoolID] =" & Me![SchoolID] & " AND " & "[ClassID] =" &
Me![ClassID]

Damon

Jane said:
Hello all (again)

Please could someone tell me what I am doing wrong here. It "should" be
simple I think. But, of course this isn't working.
Note. Both of the criteria are numbers

I am trying to open a pop up form filtered with both the school and class
so
I need to pass both criteria.

Private Sub ClassSelector_Click()

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmClassPoPUp"

stLinkCriteria = "[SchoolID] =" & Me![SchoolID]
stLinkCriteria = "[ClassID] =" & Me![ClassID]

DoCmd.OpenForm stDocName, , , stLinkCriteria

End Sub


Thank you for any help you can give

Jane Hollin
 
M

Marc

Can someone help with the below example if the 2 fields are text? I can't
get the quotes and ! correct.

Thanks!

Damon Heron said:
You are assigning a new value to the variable twice. Try
stLinkCriteria = "[SchoolID] =" & Me![SchoolID] & " AND " & "[ClassID] =" &
Me![ClassID]

Damon

Jane said:
Hello all (again)

Please could someone tell me what I am doing wrong here. It "should" be
simple I think. But, of course this isn't working.
Note. Both of the criteria are numbers

I am trying to open a pop up form filtered with both the school and class
so
I need to pass both criteria.

Private Sub ClassSelector_Click()

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmClassPoPUp"

stLinkCriteria = "[SchoolID] =" & Me![SchoolID]
stLinkCriteria = "[ClassID] =" & Me![ClassID]

DoCmd.OpenForm stDocName, , , stLinkCriteria

End Sub


Thank you for any help you can give

Jane Hollin
 
Ad

Advertisements

D

Dirk Goldgar

Marc said:
I am not able to get this to work as my fields are both text. Can someone
help me get the quotes and ! correct for 2 text filed criteria using the
same
example below?

Thanks.

Damon Heron said:
You are assigning a new value to the variable twice. Try
stLinkCriteria = "[SchoolID] =" & Me![SchoolID] & " AND " & "[ClassID]
=" &
Me![ClassID]


The correct quoting depends on whether you expect the values you intend to
embed in the criteria string will contain quotes, and what sort of quotes.
For example, if SchoolID and ClassID will never contain the single-quote
character ('), then you can do this:

stLinkCriteria = _
"[SchoolID] = '" & Me![SchoolID] & _
"' AND [ClassID] = '" & Me![ClassID] & "'"

If Me![SchoolID] = "FOO" and Me![ClassID] = "BAR", then that results in this
value for stLinkCriteria:

[SchoolID] = 'FOO' AND [ClassID] = 'BAR'

On the other hand, if the values might contain the single-quote character,
but will never contain the double-quote character ("), then you can't do the
above, but can do this:

stLinkCriteria = _
"[SchoolID] = """ & Me![SchoolID] & _
""" AND [ClassID] = """ & Me![ClassID] & """"

The double-quotes are themselves doubled-up within the quoted string
literal -- """" returns a value of ". Using the above statement with values
of "FOO" and "BAR", would yield this value for stLinkCriteria:

[SchoolID] = "FOO" AND [ClassID] = "BAR"

If the values to be embedded might contain either the single-quote or the
double-quote, then you have to protect the quotes by doubling up the quotes
inside the values. Here's one way:

Const Q As String = """"
Constr QQ As String = Q & Q

stLinkCriteria = _
"[SchoolID] = " & _
Q & Replace(Me![SchoolID], Q, QQ) & Q & _
" AND [ClassID] = " & _
Q & Replace(Me![ClassID], Q, QQ) & Q

If, then Me![SchoolID] had the value of FOO"BAR, and Me![ClassID] had the
value of BAZ, that would result in:

[SchoolID] = "FOO""BAR" AND [ClassID] = "BAZ"

.... which would successfully filter for FOO"BAR.
 
Ad

Advertisements

M

Marc

got it! Thanks for your help - well done!

Dirk Goldgar said:
Marc said:
I am not able to get this to work as my fields are both text. Can someone
help me get the quotes and ! correct for 2 text filed criteria using the
same
example below?

Thanks.

Damon Heron said:
You are assigning a new value to the variable twice. Try
stLinkCriteria = "[SchoolID] =" & Me![SchoolID] & " AND " & "[ClassID]
=" &
Me![ClassID]


The correct quoting depends on whether you expect the values you intend to
embed in the criteria string will contain quotes, and what sort of quotes.
For example, if SchoolID and ClassID will never contain the single-quote
character ('), then you can do this:

stLinkCriteria = _
"[SchoolID] = '" & Me![SchoolID] & _
"' AND [ClassID] = '" & Me![ClassID] & "'"

If Me![SchoolID] = "FOO" and Me![ClassID] = "BAR", then that results in this
value for stLinkCriteria:

[SchoolID] = 'FOO' AND [ClassID] = 'BAR'

On the other hand, if the values might contain the single-quote character,
but will never contain the double-quote character ("), then you can't do the
above, but can do this:

stLinkCriteria = _
"[SchoolID] = """ & Me![SchoolID] & _
""" AND [ClassID] = """ & Me![ClassID] & """"

The double-quotes are themselves doubled-up within the quoted string
literal -- """" returns a value of ". Using the above statement with values
of "FOO" and "BAR", would yield this value for stLinkCriteria:

[SchoolID] = "FOO" AND [ClassID] = "BAR"

If the values to be embedded might contain either the single-quote or the
double-quote, then you have to protect the quotes by doubling up the quotes
inside the values. Here's one way:

Const Q As String = """"
Constr QQ As String = Q & Q

stLinkCriteria = _
"[SchoolID] = " & _
Q & Replace(Me![SchoolID], Q, QQ) & Q & _
" AND [ClassID] = " & _
Q & Replace(Me![ClassID], Q, QQ) & Q

If, then Me![SchoolID] had the value of FOO"BAR, and Me![ClassID] had the
value of BAZ, that would result in:

[SchoolID] = "FOO""BAR" AND [ClassID] = "BAZ"

... which would successfully filter for FOO"BAR.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top