423,688 Members | 1,889 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,688 IT Pros & Developers. It's quick & easy.

Syntax error in IIF statement using nested Right function

P: 7
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:

Expand|Select|Wrap|Line Numbers
  1. 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.
May 29 '14 #1
Share this Question
Share on Google+
8 Replies


Expert 100+
P: 1,219
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
May 29 '14 #2

P: 7
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:
Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM a_edd, a_samplemaster, a_station, [CORE LOG XYZ_VALUES];
  3.  
KBarrett
May 29 '14 #3

Expert 100+
P: 1,219
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
May 29 '14 #4

P: 7
Is that what is causing the syntax error to occur?
May 29 '14 #5

Expert 100+
P: 1,219
I get an error but I don't get an error message mentioning "comma"; so yes,it looks like an error from here.
May 29 '14 #6

NeoPa
Expert Mod 15k+
P: 31,084
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?
May 29 '14 #7

P: 7
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
Jun 3 '14 #8

NeoPa
Expert Mod 15k+
P: 31,084
Let us know what you find.

Your reasoning makes sense. I see nothing wrong and I doubt I'm mistaken, but it can happen.
Jun 4 '14 #9

Post your reply

Sign in to post your reply or Sign up for a free account.