Error 3075: Syntax missing operator

1
votes

After many trial and errors run, I cannot seem to fix the syntax error. I am trying to concatenate some data and have been using Allen Browne (where you can find the code for ConcatRelated) and I got the SQL from another stackoverflow question but they had different data types.

The following is the SQL for the query that I am trying to run, which is surprisingly producing the correct results as well as this error which makes the query useless. (StmntNd is Text field and Assmt_Group is a Number field)

SELECT sub.[StmntNd], sub.[Assmt_Group], sub.[StmntDes], ConcatRelated("Num_Code", "tbl_Property", "[StmntNd] = '" & sub.[StmntNd] & "' AND [Assmt_Group] = " & sub.[Assmt_Group], "num_Code") AS concat_num_code
FROM (SELECT q.[StmntNd], q.[Assmt_Group], q.[StmntDes] FROM tbl_Property AS q GROUP BY q.[StmntNd], q.[Assmt_Group], q.[StmntDes])  AS sub
ORDER BY sub.StmntNd, sub.Assmt_Group;

This is the error that I am currently receiving: enter image description here

Thank you very much for any help. No matter what combination of quotes and apostrophes I use, I seem to keep getting an error.

1
ms-accessvba
Is this occurring on records where the fields are null? – LiamH
Come to think of it, yeah it might only be doing it with the null fields. There are some records that will be null in the fields I want to query although I wouldn't want these null records showing up – user3105414

1 Answers

1
votes

try this

SELECT sub.[StmntNd], sub.[Assmt_Group], sub.[StmntDes], ConcatRelated("Num_Code", "tbl_Property", "[StmntNd] = '" & sub.[StmntNd] & "' AND [Assmt_Group] = " & sub.[Assmt_Group], "num_Code") AS concat_num_code
FROM (SELECT q.[StmntNd], q.[Assmt_Group], q.[StmntDes] FROM tbl_Property AS q 
GROUP BY q.[StmntNd], q.[Assmt_Group], q.[StmntDes])  AS sub
Where q.[StmntNd] is Not Null
ORDER BY sub.StmntNd, sub.Assmt_Group;

Add where clause to the Column, I assumed column as q.[StmntNd]