Pass multiple criteria with VBA

[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

 

[Administrator]

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

 

[Damon Heron]

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

Damon

 

[Jane]

Dear Damon

That's perfect.

Thank you for your help

Jane

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

Damon

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

 

[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.

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

Damon

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

 

[Marc]

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

Thanks!

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

Damon

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

 

[Dirk Goldgar]

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.

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.

 

[Marc]

got it! Thanks for your help - well done!

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.

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)