Syntax error in IIF statement using nested Right function

[kbarrett]

I need to code a new field (FieldRep)in my Access database based on the values in another field (Sample). If the value in Sample ends with "R", then FieldRep = "FR"; otherwise FieldRep = "#". But I keep getting the "Syntax Error (Comma) in query expression" message. Can anyone tell me which comma is causing the error (or if I'm missing a comma)?

Here's my query:

FieldRep: IIf(Right([Sample],1)="R","FR","#")

Here's an example of how the values in Sample look:

HB13_02
HB13_16
HB13_02R
HB13_16R


Thank you.

[jimatqsi]

kbarrett,
Is the field being probed "Sample" or "SampleID"? Your code shows the latter but your question mentions the former twice.

Are there other fields in your query? It would be helpful to post the entire query source, if indeed this is part of a query. If so,go into SQL view mode of the query and copy the query text. Post it here. Be sure to use code tags (click the [Code/] button and paste your code between the tags).

Jim

[kbarrett]

Yes - I realized I initially posted the query with the wrong field name so I edited my post. The correct field name is "Sample".

Here is how the query looks in Design View:
FieldRep: IIf(Right([Sample],1)="R","FR","#")

Here is the SQL Code:

SELECT [Project name]+" 2013" AS StudyName, "Joe Graham (WDNR)" AS Contact, a_edd.Sample AS StationID, ['' ] AS Alternate_ID, a_edd.Sample AS LOCDESC, "R" AS EST_STN, a_edd.[Field ID] AS SampleID, IIf(Right([Sample],1)="R","FR","#") AS FieldRep, [CORE LOG XYZ_VALUES].Long_DD_NAD83 AS LongDD_83, [CORE LOG XYZ_VALUES].Lat_DD_NAD83 AS LatDD_83, [Upper Collection Depth]*2.54 AS [UDEPTH_(cm)], [Lower Collection Depth]*2.54 AS [LDEPTH_(cm)], [CORE LOG XYZ_VALUES].Time AS SAMPTIME, [CORE LOG XYZ_VALUES].Collected AS SAMPDATE, a_edd.[Analyte name] AS CHEMNAME, a_edd.Units, a_edd.[Lab Rep] AS LAB_MTHD_DUP, a_edd.[Final Qualifier] AS Final_QUALCODE, a_edd.Result AS CONC, a_edd.[Detection limit] AS MDL, a_edd.[Reporting limit] AS RL, a_edd.[Dilution factor] AS Dil_Factor, a_edd.[Analytical method] AS Analytical_Method, a_edd.[Lab name] AS Lab_Name, a_edd.[Measurement Basis] AS MEASBASIS
FROM a_edd, a_samplemaster, a_station, [CORE LOG XYZ_VALUES];
 

KBarrett

[jimatqsi]

This looks wrong
['' ] AS Alternate_ID,

If you're trying to make Alternate_ID a hard-coded space, just ' ' without the [] will work fine.

Jim

[kbarrett]

Is that what is causing the syntax error to occur?

[jimatqsi]

I get an error but I don't get an error message mentioning "comma"; so yes,it looks like an error from here.

[NeoPa]

I see absolutely nothing wrong with the original code for [FieldRep], so it looks like the problem is elsewhere as Jim indicated.

Curious why you would have focused on FieldRep in spite of there being nothing to indicate it had a problem?

[kbarrett]

I assume it was FieldRep because that was the last edit I made after which the error message appears. I just tested by removing that column and the query runs successfully. However your suggestion to look elsewhere is worth checking. Thanks

[NeoPa]

Let us know what you find.

Your reasoning makes sense. I see nothing wrong and I doubt I'm mistaken, but it can happen.