Solved

Syntax error in VBA SQL. I can't see the problem.

Posted on 2014-09-22
3
197 Views
Last Modified: 2014-09-22
Can you see a syntax error here please. I get message below.

Dim rstIncomesSQL As String
Dim rstIncomes As DAO.Recordset

Dim strClientId As String

strClientId = "4105095040088"

rstIncomesSQL = "SELECT tblIncomeAndInvestments.* FROM tblIncomeAndInvestments WHERE (tblIncomeAndInvestments.[IncGrowProp])='Income' AND (tblIncomeAndInvestments.[CLIENTID)]= '" & strClientId & "';"

Set rstIncomes = db.OpenRecordset(rstIncomesSQL, dbOpenSnapshot)

Open in new window


Syntax error message
This however works fine:

rstIncomesSQL = "SELECT tblIncomeAndInvestments.* FROM tblIncomeAndInvestments WHERE (tblIncomeAndInvestments.CLIENTID)= '" & strClientId & "';"

Open in new window


The problem comes when I add the

(tblIncomeAndInvestments.[IncGrowProp])='Income' AND   section. "Income" is a value in IncGrowProp field.

Here is a snap of the table
tblIncomeAndInvestments.JPG
0
Comment
Question by:Fritz Paul
3 Comments
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40336296
You've got [CLIENTID)] - open hard bracket - CLIENTID - close soft bracket - close hard bracket.

You need [CLIENTID]) - open hard bracket - CLIENTID - close hard bracket - close soft bracket
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 40336301
You've got a typo:
tblIncomeAndInvestments.[CLIENTID)]
should be:
tblIncomeAndInvestments.[CLIENTID])
0
 

Author Closing Comment

by:Fritz Paul
ID: 40336354
Thanks. I was so stupid.
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

829 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