Syntax error in SQL

[PeterMAS]

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:

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.

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

 

[PHV]

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



Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PeterMAS]

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.

 

[PeterMAS]

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.

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

Thanks