Results 1 to 13 of 13
  1. #1
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    stLinkCriteria exp (2002 SP-2)

    From what I gather in VBA Help, it's possible to use multiple field criteria expressions. I suppose one also has to have some understanding as to how it all works though. In the following expression I'm making a syntax error in adding the second criteria. In looking at the VBA example (not at all like my expression - of course) I can't determine where I'm going wrong. Little help?
    stLinkCriteria = "[Name]=" & "'" & Me![txtShipperConsignee2] & And [City] = '" & " Me![ShConCity2] & "'"
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: stLinkCriteria exp (2002 SP-2)

    You must keep track of which parts of your where condition should be inside the quotes (the fixed parts) and which outside (the variable parts.) There is no closing single quote after [txtShipperConsignee2], no double quote before And, and a superfluous double quote before Me![ShConCity2].

    stLinkCriteria = "[Name]=" & Chr(34) & Me![txtShipperConsignee2] & Chr(34) & " And [City] = " & Chr(34) & Me![ShConCity2] & Chr(34)

    I have used Chr(34) to insert quotes in stLinkCriteria, for I find the code with single and double quotes next to each other confusing to read.

  3. #3
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: stLinkCriteria exp (2002 SP-2)

    Does this work :


    <pre>stLinkCriteria = "Name=" & Me![txtShipperConsignee2] & " and City=" & Me.ShConCity2 & ""</pre>


  4. #4
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: stLinkCriteria exp (2002 SP-2)

    <img src=/S/munch.gif border=0 alt=munch width=19 height=17> . . .digesting.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  5. #5
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: stLinkCriteria exp (2002 SP-2)

    Hans:

    Your code works perfectly (of course), and I see where the use of Chr(34) makes things much easier to read. To further my understanding (and screw up your eyesight) is the use of Chr(34) the same as:
    stLinkCriteria = "[Name]=" & "'" & Me![txtShipperConsignee2] & "'" & " And [City] = '" & "'" & Me![txtShConCity2] & "'"

    I presume Chr(34) is for a single quote, is there also a Chr(??) for a double quote; or would one bother to use it? Also, I tried to look up these Chr(**) in Help, but could not locate a list of them. When you have a moment, I'd certainly appreciate some direction as to where I might find them. Thanks once again for the assist!
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  6. #6
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: stLinkCriteria exp (2002 SP-2)

    Dave:

    Thanks for the reply! As you can see, HansV beat you to the punch! I did try your solution (it's much shorter) but got a "Syntax error (missing operator) in query expression".
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  7. #7
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: stLinkCriteria exp (2002 SP-2)

    Hans always beats be to the punch, and he's never wrong.

    But one day ?? <img src=/S/sarcasm.gif border=0 alt=sarcasm width=15 height=15>

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: stLinkCriteria exp (2002 SP-2)

    Chr(34) is in fact a double quote. If you want to create a string within a string, you can't just enclose the inner string in double quotes, because double quotes are already used to enclose the outer string. There are three methods:<pre>strWhere = "LastName = '" & Me.LastName & "'"
    strWhere = "LastName = """ & Me.LastName & """"
    strWhere = "LastName = " & Chr(34) & Me.LastName & Chr(34)</pre>

    As you see, you can use single quotes, double double quotes and Chr(34). The latter is slightly longer, but more readable IMHO.

    You can find ASCII tables all over the net using Google, for example http://www.ascii.cl.

  9. #9
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: stLinkCriteria exp (2002 SP-2)

    Hans:

    Not surprising that I'm confused! Do all three of those examples return the same result? Maybe I'm confused about inner and outer strings? It seems to me that the 2nd and 3rd example are identical, but the 1st seems to be completely different than the other two (single instead of double quotes). BTW, thanks for the link!
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: stLinkCriteria exp (2002 SP-2)

    All three examples have the same effect. In a SQL string and in a where-condition, you can use either single or double quotes to delimit a string value.

  11. #11
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: stLinkCriteria exp (2002 SP-2)

    Hans:

    Got it! In VBA you can use either/or sometimes, but not always. Sometimes you can miss a single ' and end up in the ditch, or not. Makes perfect sense to me! <img src=/S/grin.gif border=0 alt=grin width=15 height=15> Thanks for all your extra tutelage; it is always appreciated.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  12. #12
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: stLinkCriteria exp (2002 SP-2)

    just to add - although you've gotten your solution, I like to define quote = Chr(34) and use quote in the SQL string to make it even easier to read:

    " & quote & [something] & quote & "

    fer example.

  13. #13
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: stLinkCriteria exp (2002 SP-2)

    Steve:

    Thanks for your input! That sounds like a reasonable method, but in my db it might cause some "difficulty" in reading code. I have a txtbx that appears throughout the db that is named "Quote" and, although in code it would probably appear as [Quote], I'd rather not risk befuddling my already over-taxed brain cell (not a typo) <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •