Solved

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

Posted on 2014-09-22
3
194 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
Comment Utility
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 26

Expert Comment

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

Author Closing Comment

by:Fritz Paul
Comment Utility
Thanks. I was so stupid.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Input box criteria 3 18
SQL Database connect change in Access 2015 5 30
How calculate median 5 37
Resize text 4 15
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Familiarize people with the process of utilizing SQL Server functions 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 Microsoft Ac…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

743 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

15 Experts available now in Live!

Get 1:1 Help Now