In a table, Field2 can contain 1 of 2 values. Depending on what value is in Field2, the value in Field3 needs to be placed in a different window in my program. So, using a SQL statement, I would like to add two "virtual" fields to the recordset that is returned from the database. The two new fields would contain the value from Field3 depending on what is in Field2.

For example, if the database looks like this:

Field1 Field2 Field3
test A 65
test2 B 73
test3 A 45
test4 A 87
test5 B 34

I want the recordset returned to look like the following, with FieldA and FieldB representing the "virtual" fields that don't actually exist in database but their contents depend on what is in Field2 and Field3:

Field1 Field2 Field3 FieldA FieldB
test A 65 65
test2 B 73 73
test3 A 45 45
test4 A 87 87
test5 B 34 34

How would I structure a SQL statement to produce this recordset? Thanks!

Use the IIf («expr», «truepart», «falsepart») function.

SELECT Table.Field1,
Table.Field2,
Table.Field3,
IIf([Field2]="A",[Field3],"") AS FieldA,
IIf([Field2]="B",[Field3],"") AS FieldB
FROM [Table];

Assuming that your table name is Table. Use the IIF statement. This statement evaluates an expression on the first argument. After evaluating it you use the second argument to tell it what to do if it comes back as true, if it comes back as false you tell it what you want it to do on the third argument.

Hope it helps.


Sal

That worked! Thanks!