IIF Statements in a Query - Syntax Error Message

[andy49]

I can't replicate the error using your SQL.


All my fields are in Table 1 and they are all text fields



Can you confirm this is the case with your fields

[CP611]

Hi,

The ones in Bold are new fields that I am adding but the fields that I am selecting from Table1 are all currently text fields. I have a lot more columns but didn't add those in as that would be very long, I have the same problem further along whenever I try to add a statement with a comma in. It will let me type the IIF statement in, I only get the query when I try to run it

[June7]

You are able to create and save the query object but it errors when you try to open? Very bizarre, never encountered this issue. Can you try the db on another computer?

[CP611]

I can create the query, but I can't save or run it. I haven't tried it on another computer but I could try. It's very frustrating!!

[ssanfu]

This is the actual SQL?

SELECT 
Null AS SERIALNUMBER, 
Null AS CONTACTTYPE, 
Null AS TITLE,  
[Table1].[First Name], 
[Table1].[Last Name],
[Table1].Address & "|"  & [Address2] AS [=Expr],   '<<-- WHAT??? Probably invalid name. Change the pipe to a dash
[Table1].[Suburb/City], 
[Table1].County,  
[Table1].[Postal Code],
 [Table1].Country,  
IIf([Gender]="F","Female","Male") AS Expr
FROM Table1;

I would use/try

SELECT 
Null AS SERIALNUMBER, 
Null AS CONTACTTYPE, 
Null AS TITLE,  
[Table1].[First Name], 
[Table1].[Last Name],
[Table1].Address & " - "  & [Address2] AS StreetAddress,     '<- set name as StreetAddress 
[Table1].[Suburb/City], 
[Table1].County,  
[Table1].[Postal Code],
 [Table1].Country,  
IIf([Gender]="F","Female","Male") AS Expr1   '<- added the 1
FROM Table1;

I would also recommend removing the spaces and special characters in the field names. Use only letters and numbers (exception is the underscore)

[CP611]

Thank you. It worked in the end, if I didn't select the table from which is was supposed to come from, happy days!