IIF Statements in a Query - Syntax Error Message

[CP611]

I'm using an Append Query and want to change if it says F to Female and M to Male. My current formula says: IIf([Gender]="F","Female","Male") but when I try to run the query it comes up with the error message: Syntax error (comma) in query expression. I've tried putting a ; instead of a , as someone else suggested but still doesn't work. Any ideas?! It's driving me crazy! (I am a complete novice when it comes to Access) thanks

[June7]

An APPEND query adds records, an UPDATE changes values.

Post your complete SQL statement.

[CP611]

I really don't know much about Access, I'm trying to teach myself! I'm basically inputting data onto one table and using an append query to add it to another table but in a different order and with concatenations. So for example in my input table I've got two columns Address line 1 and Address line 2 and using the append query I've bought them both together into one column but with a Pipe added using this Expression: [Address] & "|" & [Address2]. I thought using the IIF statement in a similar way I could append the output table to change F to Female. Is this the right way to do it?

[June7]

What you are doing is not conventional. Instead of saving to another table, should just do those calcs in a SELECT query.

Have you completed an introductory tutorial book?

[CP611]

I did look at the select query but the problem that I had with this is in second table (that I am currently appending to) has extra columns that weren't in the first table. Is there a way with a select query that this can be done?

No I haven't completed a introductory tutorial book, I've just been using Google!

[Bulzie]

To agree with what June7 said, query would seem to be the way to go. Queries are the power of databases like MS Access. Gives you a lot of flexibility to manipulate the data without having to change the underlying data in the table. I really don't see anything wrong with your IIf statement. Could be issue with the field in 2nd table you are trying to append it to? Is that gender field in 2nd table a text field? And yes you can have other fields not in the table in a Select query.

[CP611]

Yes the Gender field in the 2nd table I'm trying to append to is a text field. How would I go back adding fields into a select query that aren't in the original table that I'm selecting from? I've tried to do it before and it didn't come out with any data

[Bulzie]

These new fields not tied to a table are just variables so if you wanted to add the current date, in a column you would put CurrDate: Date() or if you wanted a field to combine 2 other fields would be like FullName: [FirstName] & " " & [LastName]. So you put the name you want to call it, then a colon, space and then what you want the field to hold.

[CP611]

Ok great I'll give that a go. Some of these new fields will not have any data in them, is that an issue? Because basically I want the table created from the select query to then be exported into excel and then the data is imported onto our database using an import tool which has a ready made spreadsheet which we just copy and paste data on to and import. Therefore some of the fields brought out from the select query don't contain any data (as they are filled in our database) but we will need the new headers to be there in the correct order. Thanks for your help so far

[Bulzie]

You should just be able to do one of the following:
NewField: ""
NewField: " "
NewFIeld: null

[CP611]

Thank you for your help I'll give that a go and see what happens!

[June7]

Query can be exported to Excel. So far, nothing really justifies second table.

You want to manipulate the data, export to Excel, then import into a different db?

[Geo21]

Could you post the whole query?

[CP611]

I've managed to create what I want from just using a select query, however I am still getting the same error message when I put in the IIF statement of: IIf([Gender]="F","Female","Male") of Syntax error (comma) in query expression. Any ideas?!

[CP611]

This is the SQL for the query (Table 1 is just the table I'm selecting from - as you can see I also have quite a few new fields where I just need the header hence the null).
SELECT Null AS SERIALNUMBER, Null AS CONTACTTYPE, Null AS TITLE, [Table1].[First Name], [Table1].[Last Name], [Table1].Address & "|" & [Address2] AS [=Expr], [Table1].[Suburb/City], [Table1].County, [Table1].[Postal Code], [Table1].Country, IIf([Gender]="F","Female","Male") AS Expr
FROM Table1;