Results 1 to 4 of 4
  1. #1
    Tuckejam is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2020
    Posts
    168

    Quotes, Brackets, Ampersands, and spaces

    I am starting to grasp the mechanics of VBA but am lost when it comes to the.... how it fits together?

    For example

    Dim SrchCrit as String
    Dim fldName as String


    Dim Srchval as String

    (i could post the whole code, but i think this will do for what I'm asking)

    SrchCrit = "[" & fldName & "] Like '*" & SrchVal & "*'"

    ok first the whole thing is wrapped in quotes, I think because its is putting together a text value or string

    I think brackets are used in case there is a space in the name that the brackets are surrounding, but is that the sole purpose of bracket use in VBA or is there other uses. and when why are () used

    Ampersand, i understand concatenation from excel but this is a bit different. in vba do we always wrap the concatenated values individually i.e. & something & & something else &

    and the spaces, there are spaces on both sides of the &, and then there are no spaces for the brackets "["& or for the wild card symbol "*", and whats the rule if i needed *[& is it wildcard then bracket or bracket then wildcard

    my concern and frustration is that I completely understand what this line of code does, but I could spend all day debugging this line if "["&* are in the wrong order.

    I don't actually need this line of code explained bit by bit. what I'm hoping to find is a decent reference somewhere, website or book that has a cheetsheet that would help me with symbols and spaces in all cases.

    Thanks for any help

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Well, I'll dive in and answer part(s) of it. For starters, in 20 years I don't think I've ever seen that method of concatenating ("&"), but in a brief test it appears to work. I've only used & and sometimes + (+ propogates Null, which sometimes you want).

    You're correct about the [], they would protect the process against spaces or symbols in the field name. To make a slight correction to "ok first the whole thing is wrapped in quotes". It is actually fixed text, surrounded by sets of double quotes, being concatenated together with variables, or non-fixed text. So there are sets of quotes surrounding the fixed text, not the whole thing. You may find this helpful, though it's more about delimiters.

    http://www.baldyweb.com/BuildSQL.htm

    You may find this useful, a common method of debugging that lets you see the final product produced by your concatenation:

    http://www.baldyweb.com/ImmediateWindow.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    & - is this your code or did the forum inject those? AFAIK, that's html and doesn't belong in a vba line of code as a rule. (as I moved through, I realize it's not the forum).
    ok first the whole thing is wrapped in quotes - no, it's not. If it was it would look something like this
    """[" & fldName & "] Like '*" & SrchVal & "*'""


    I think brackets are used in case there is a space in the name - True, or worse, begins with a number.
    when why are () used - Several reasons. When a function returns a value, the parameters need to be enclosed. When you want to group AND's and/or OR's. When you nest functions; etc.

    do we always wrap the concatenated values individually i.e. & something & & something else & - that's not wrapping and it's not the right syntax. Concatenation can be tricky, but I wouldn't use &amp, just &. Best you research some examples to get a feel for it. Try Google?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    Tuckejam is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2020
    Posts
    168
    Well S***

    sorry about the &amp


    when I originally posted it, the forum website gave me a Timed out error,

    Luckily I was able to get back to the page so I didnt have to re type me entire post, but it had converted my post to HTML (or what ever it did)

    AND it was right at the end of my lunch break and I needed to get back to something else so I quickly tried to restructure my post back to regular text

    I was just going to fast and missed some of the &amp, sorry about that

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Parameter value but no brackets!
    By elender in forum Queries
    Replies: 3
    Last Post: 02-02-2017, 02:57 PM
  2. Concatenate 2 field with brackets
    By jj1 in forum Access
    Replies: 4
    Last Post: 02-13-2016, 06:40 PM
  3. Replies: 4
    Last Post: 12-05-2014, 11:15 AM
  4. Replies: 1
    Last Post: 07-12-2013, 01:15 PM
  5. Search for brackets
    By stocktsi in forum Access
    Replies: 3
    Last Post: 11-14-2011, 06:18 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums