VBA syntax error

Posted on 2011-10-13
Last Modified: 2012-05-12

I have this line of vba code:
Result = "SELECT * FROM MTB WHERE EnvD= " & a    
a is a string that has spaces in it such as "My Env One"

Any help with the syntax please?
Question by:samprg
    LVL 75

    Accepted Solution


    Result = "SELECT * FROM MTB WHERE EnvD= " & Chr(34) & "a" & Chr(34)

    LVL 75

    Assisted Solution

    by:DatabaseMX (Joe Anderson - Access MVP)
    A string criteria must be surrounded with double quotes.  Chr(34) is a double quote.

    LVL 61

    Expert Comment

    <a is a string that has spaces in it such as "My Env One" >

    Just a note here - this is answer is already accepted, but if you are dealing with a variable as you described, not a literal "a", the syntax is a bit different (you probably worked this out independently):

    This works for values that cannot contain single quotes (no names like O'Brien)
    Result = "SELECT * FROM MTB WHERE EnvD= '" & a & "'"

    This is better when that cannot be guaranteed:
    Result = "SELECT * FROM MTB WHERE EnvD= " &  chr(34) & a & chr(34)

    LVL 75

    Expert Comment

    by:DatabaseMX (Joe Anderson - Access MVP)
    Actually, it was a typo - assuming 'a' is a variable

    Result = "SELECT * FROM MTB WHERE EnvD= " & Chr(34) & a & Chr(34)

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now