?
Solved

VBA syntax error

Posted on 2011-10-13
4
Medium Priority
?
323 Views
Last Modified: 2012-05-12
Hi,

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?
0
Comment
Question by:samprg
  • 3
4 Comments
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 2000 total points
ID: 36965952


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

mx
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 2000 total points
ID: 36965956
A string criteria must be surrounded with double quotes.  Chr(34) is a double quote.

mx
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36965976
<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)

0
 
LVL 75
ID: 36965980
Actually, it was a typo - assuming 'a' is a variable


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

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

830 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