Syntax Error 3075 when running pre-built query in Access

Question

I am trying to use code from an old thread to fit my data: Concatenate fields from one column in one table into a single, comma delimited value in another table

I have a table with 3600 records similar to that shown below for 677 unique AcousticTagCode:

AcousticTagCode PITtag  bkm Type    SiteCode
5004.24 3D6.001569C022  ap  STND    IC3
5004.24 3D6.001569C022  ap  STND    IC2
5004.24 3D6.001569C022  ap  STND    IC1
5004.24 3D6.001569C022  ap  STND    RGD1
5004.24 3D6.001569C022  ap  STND    RGU1
5004.24 3D6.001569C022  ap  STND    ORS1
5004.24 3D6.001569C022  ap  STND    WC1
5004.24 3D6.001569C022  ap  STND    WC2
5004.24 3D6.001569C022  ap  STND    WC3
5010.04 3D6.001569C01C  ap  STND    IC3
5010.04 3D6.001569C01C  ap  STND    IC2
5010.04 3D6.001569C01C  ap  STND    IC1
5010.04 3D6.001569C01C  ap  STND    RGD1

AcousticTagCode is Numeric, all other fields are text.

When I try to run

SELECT AcousticTagCode, ConcatRelated("SiteCode", "tblAllDetectionSites", 
    "AcousticTagCode = " & [AcousticTagCode]) AS Sites
    FROM tblAllDetectionSites;`

I receive the error:

Error 3075: Syntax error (missing operator) in query expression 'AcousticTagCode = '.

I have tried placing a ' like so:

SELECT AcousticTagCode, ConcatRelated("SiteCode", "tblAllDetectionSites", 
    "AcousticTagCode = '" & [AcousticTagCode]) AS Sites
    FROM tblAllDetectionSites;

But receive this error:

Error 3075: Syntax error in string in query expression 'AcousticTagCode = "

I'm not super familiar with SQL queries so as much help as possible would be appreciated!

End result should provide a line for each AcousticTagCode and all the SiteCodes associated with that TagCode. (I haven't added the DISTINCT operator yet as it slows everything down)

Yes, some records have a PITtag but no Acoustic Tag, do I need to remove those? — Taylor Spaulding, Apr 10 '19 at 16:42

score 1 · Accepted Answer · answered Apr 10 '19 at 16:47

I would suggest changing the ConcatRelated function to:

ConcatRelated
(
    "SiteCode", 
    "tblAllDetectionSites", 
    iif
    (
        [AcousticTagCode] is null, 
        "AcousticTagCode is null", 
        "AcousticTagCode = " & Str([AcousticTagCode])
    )
)

This should solve two problems:

Accounts for records where AcousticTagCode is null (grouping such records together).
Uses the Str function to convert the Double value held by the AcousticTagCode field to avoid the effect of regional settings.

Syntax Error 3075 when running pre-built query in Access

1 Answers1