Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Syntax error in SQL

Status
Not open for further replies.

PeterMAS

Technical User
Apr 3, 2012
18
GT
I am assembling fields into text using expressions.

The following code produces the error message: 3075 Syntax error (missing operator)in query expression '[tblEpr].[SsnID]=0 AND [tblEpr].[RecID]= & [Forms]![frmRec]![RecID] &'.

The message occurs on the final line of code below:

Code:
Dim strSQL As String
Dim db As DAO.Database
Dim qdfEpr0 As DAO.QueryDef
Set db = Application.CurrentDb
    
Set qdfEpr0 = db.QueryDefs("qcnEprSsn0No1")

strSQL = "SELECT * FROM qcnEpr WHERE [tblEpr].[SsnID]=0 AND [tblEpr].[RecID]= & [Forms]![frmRec]![RecID] & ;"
qdfEpr0.SQL = strSQL

Here is the SQL query within the VBA generated query above.

SQL:
SELECT tblEpr.RecID, tlkEprGrp.EprGrp, [EprCat] & IIf([Age]="n/a","",(" " & [Age])) & " ("+[EprCmnt]+")" & ": " & IIf([EntryPrice]=0,"Free",Format([EntryPrice],"Currency")) & IIf([IdReq]=No,""," (ID req.)") AS CatAgeEpr, tblEpr.SsnID, tblEpr.EprGrpID
FROM tlkEprGrp INNER JOIN (tlkEprCat INNER JOIN (tlkAge INNER JOIN tblEpr ON tlkAge.AgeID = tblEpr.AgeID) ON tlkEprCat.EprCatID = tblEpr.EprCatID) ON tlkEprGrp.EprGrpID = tblEpr.EprGrpID;

FYI: the expression in the query produces text something like this: Child under 16: Free (ID req.).

Formerly I was getting an error message about an Extra ) but when I stripped out the parentheses and quotes to simplify the query the error message changed to Syntax error.

The query seems to execute OK so If there is no solution, I will ignore the error, but I prefer to fix it.

Thank you
 
strSQL = "SELECT * FROM qcnEpr WHERE [tblEpr].[SsnID]=0 AND [tblEpr].[RecID]=[!]"[/!] & [Forms]![frmRec]![RecID] & [!]"[/!];"



Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Thanks for the suggestion, but it didn't help. I previously had the quotes, but removed them when searching online for a solution. I saw a post on another forum that said they are not required when the field (RecID) is numeric.
 
I found the problem. I need to check for a null with an Nz probably because the code is on the On Current event of a subform and the main form hasn't opened when the subform loads. I haven't previously struck this type of problem with queries using the Forms!frmMain!... type of reference so I guess it occurs when manipulating queries with VBA. Here's the modified line of code in case it's useful to someone.

Code:
strSQL = "SELECT * FROM qcnEpr WHERE [tblEpr].[SsnID]=0 AND [tblEpr].[RecID]=" & Nz([Forms]![frmRec]![RecID], 0) & ";"

Thanks
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top