Sql Syntax Query

[Matthieu]

So, i'm trying to create a query that will take what the Options Groups in my forms input and change them from 1, 2, 3, or -1 to Yes, No, N/A and 1. I am trying to use an iif statement to do this, this is what I have:


iif([Data].[Customer]=1, [Data].[Customer]= "Yes", (iif([Data].[Customer]=2,[Data].[Customer]= "No",[Data].[Customer]="N/A"))) AS Customer, etc..
Instead of changing each cell from 1,2 or 3 to Yes, No, N/A, it's creating new columns and leaving them blank. I can't figure out what i'm doing wrong. I know sql very roughly, having mostly taught myself. Any thoughts would help!
Notes: Data.Customer is one of the columns in the data- table. I am trying to modify multiple columns. I have also tried something like iif([Data].[Customer]=1, "Yes", (iif([Data].[Customer]=2, "No", "N/A"
-this did not work either.

[RuralGuy]

Do you have more than one table in your query? If not, leave the table scoping off of your reference.
IIF(Customer]=1, "Yes", IIF([Customer]=2, "No", ..etc

[RuralGuy]

You could also make it easier to read and maintain if you created a Global Function in a standard module and used a Select Case structure.

[Matthieu]

Thanks, I got it to work like this:
(iif([Customer]= 1, "Yes", iif([Customer]=2, "No", "N/A"))) AS Customer1
and did that for each column. I would prefer to do that Global Function Case statement that you recommended but I attempted it in Visual Basic and my understanding of VB is too limited, I couldn't get it to work. I gave up and just decided to do it in sql like this. Thanks for the help though and if you have recommendations on how to write something like that or a good link, that would also be appreciated. Writing it in sql like this is messy and difficult to work with.

[RuralGuy]

The code in a standard module will look something like:

Public Function Foo(InNum As Integer) As String
   Select Case InNum
      Case 1
         Foo = "Yes"
      Case 2
         Foo = "No"
      Case 3
         Foo = "N/A"
      Case -1
         Foo = "1"
      Case Else
         '-- Error or return what ever you want
   End Select
End Function