stLinkCriteria Statement with multiple criteria

[imelda1ab]

I'm still learning and I'm still struggling. How do I write a
stLinkCriteria statement to include "this AND that"? By the same
token, what if I wanted to use instead a "this OR that" statement?
Same syntax? Below is what I would use if it worked the same way it
works logically in my head )

Thanks!!

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmSupportStaff"
stLinkCriteria = "[To] = 'Doe, Jane'" AND "[City]= 'Chicago'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

 

[Stuart McCall]

I'm still learning and I'm still struggling. How do I write a
stLinkCriteria statement to include "this AND that"? By the same
token, what if I wanted to use instead a "this OR that" statement?
Same syntax? Below is what I would use if it worked the same way it
works logically in my head )

Thanks!!

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmSupportStaff"
stLinkCriteria = "[To] = 'Doe, Jane'" AND "[City]= 'Chicago'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

The AND keyword needs to be inside the stLinkCriteria string. Try this:

stLinkCriteria = "[To] = 'Doe, Jane' AND [City]= 'Chicago'"

And yes, the OR keyword is used the same way.

 

[John W. Vinson]

I'm still learning and I'm still struggling. How do I write a
stLinkCriteria statement to include "this AND that"? By the same
token, what if I wanted to use instead a "this OR that" statement?
Same syntax? Below is what I would use if it worked the same way it
works logically in my head )

Thanks!!

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmSupportStaff"
stLinkCriteria = "[To] = 'Doe, Jane'" AND "[City]= 'Chicago'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

The quotes are the tricky part. A text field's criterion must be delimited by
quotemarks, either ' or "; the latter is preferable if the criteiron might
contain an apostrophe (names such as O'Niell for example). Otherwise you need
to piece the strLinkCriteria string out of a mixture of variables and
constants. It would be rather rare to have Jane Doe and Chicago hardcoded into
your VBA, but if that's what you want, the final outcome would need to be

[To] = 'Doe, Jane' AND [City] = 'Chicago'

so your expression would need to be

stLinkCriteria = "[To] = 'Doe, Jane' AND [City]= 'Chicago'"

including the word AND in the string.

For the more common case where the criteria come from a form, and allowing for
the possibility of an apostrophe in either criterion, you can include a
doublequote within a doublequote delimited string by using a double
doublequote (how's THAT for doubletalk):

stLinkCriteria = "[To] = """ & Me!txtName & _
""" AND [City] = """ & Me!txtCity & """"

This stiches together four components: a literal text string

[To] = "

the value in the form control txtName; a literal text string

" AND [City] = "

the value in the form control txtCity; and a literal text string consisting of
just a doublequote character.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[imelda1ab]

I'm still learning and I'm still struggling.  How do I write a
stLinkCriteria statement to include "this AND that"?  By the same
token, what if I wanted to use instead a "this OR that" statement?
Same syntax?  Below is what I would use if it worked the same way it
works logically in my head )

Dim stDocName As String
Dim stLinkCriteria As String

   stDocName = "frmSupportStaff"
   stLinkCriteria = "[To] = 'Doe, Jane'"  AND "[City]= 'Chicago'"
   DoCmd.OpenForm stDocName, , , stLinkCriteria

The quotes are the tricky part. A text field's criterion must be delimited by
quotemarks, either ' or "; the latter is preferable if the criteiron might
contain an apostrophe (names such as O'Niell for example). Otherwise you need
to piece the strLinkCriteria string out of a mixture of variables and
constants. It would be rather rare to have Jane Doe and Chicago hardcodedinto
your VBA, but if that's what you want, the final outcome would need to be

[To] = 'Doe, Jane' AND [City] = 'Chicago'

so your expression would need to be

  stLinkCriteria = "[To] = 'Doe, Jane'  AND [City]= 'Chicago'"

including the word AND in the string.

For the more common case where the criteria come from a form, and allowing for
the possibility of an apostrophe in either criterion, you can include a
doublequote within a doublequote delimited string by using a double
doublequote (how's THAT for doubletalk):

stLinkCriteria = "[To] = """ & Me!txtName & _
        """ AND [City] = """ & Me!txtCity & """"

This stiches together four components: a literal text string

[To] = "

the value in the form control txtName; a literal text string

" AND [City] = "

the value in the form control txtCity; and a literal text string consisting of
just a doublequote character.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

I tried what I thought was every variation of that and even with the
suggestions above I still receive a "Type Mismatch" error. I'm so
confused, grateful for the help, but so confused.

 

[imelda1ab]

I'm still learning and I'm still struggling.  How do I write a
stLinkCriteria statement to include "this AND that"?  By the same
token, what if I wanted to use instead a "this OR that" statement?
Same syntax?  Below is what I would use if it worked the same way it
works logically in my head )
Thanks!!
Dim stDocName As String
Dim stLinkCriteria As String
   stDocName = "frmSupportStaff"
   stLinkCriteria = "[To] = 'Doe, Jane'"  AND "[City]= 'Chicago'"
   DoCmd.OpenForm stDocName, , , stLinkCriteria

The quotes are the tricky part. A text field's criterion must be delimited by
quotemarks, either ' or "; the latter is preferable if the criteiron might
contain an apostrophe (names such as O'Niell for example). Otherwise you need
to piece the strLinkCriteria string out of a mixture of variables and
constants. It would be rather rare to have Jane Doe and Chicago hardcoded into
your VBA, but if that's what you want, the final outcome would need to be

[To] = 'Doe, Jane' AND [City] = 'Chicago'

so your expression would need to be

  stLinkCriteria = "[To] = 'Doe, Jane'  AND [City]= 'Chicago'"

including the word AND in the string.

For the more common case where the criteria come from a form, and allowing for
the possibility of an apostrophe in either criterion, you can include a
doublequote within a doublequote delimited string by using a double
doublequote (how's THAT for doubletalk):

stLinkCriteria = "[To] = """ & Me!txtName & _
        """ AND [City] = """ & Me!txtCity & """"

This stiches together four components: a literal text string

[To] = "

the value in the form control txtName; a literal text string

" AND [City] = "

the value in the form control txtCity; and a literal text string consisting of
just a doublequote character.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

I tried what I thought was every variation of that and even with the
suggestions above I still receive a "Type Mismatch" error.  I'm so
confused, grateful for the help, but so confused.- Hide quoted text -

- Show quoted text -

I walked around the block, deleted everything I had and retyped from
scratch and it worked perfectly. Thanks guys - you rock the casbah!