Expression?

This is most probably easy but not for me...

I have a table which contains amongst others two specific fields - called ORG and SOBP - dependent on the combination of the data value in these two fields I want to create a third field (which will contain just seven alternatives) which I wish to be able to use as column headings in a crosstab report.

For the life of me I can't work out the most efficient way of doing this - can I write an expression in the manner of an "If" statement in Excel or is there a better way?

Thanks in advance

You can nest six iif() functions like this in a query:-

SELECT ORG, SOBP,
iif(ORG="xx1" AND SOBP="yy1", "Alternative1",
iif(ORG="xx2" AND SOBP="yy2", "Alternative2",
iif(ORG="xx3" AND SOBP="yy3", "Alternative3",
iif(ORG="xx4" AND SOBP="yy4", "Alternative4",
iif(ORG="xx5" AND SOBP="yy5", "Alternative5",
iif(ORG="xx6" AND SOBP="yy6", "Alternative6",
"Alternative7")))))) as Alternatives
FROM TableName


It would be easier to type the SQL Statement in the SQL View of a new query.

If ORG or SOBP is a numeric field, there is no need to put xx1 or yy1 etc. in double quotes. The double quotes are needed only for text.

Thanks

That's great Jon - many thanks for your time

Just what I needed

This is just what I was looking for to solve a problem I had myself.
I'm posting in case it might help others.
I have an AR spreadsheet imported with 6 columns of aging
(i.e. "current", "31to60", "60-90", "91-120", "121-365", "Over 365") with lines of data by customer and amounts in the column it pertains to. I wanted to create a new field that "labels" the line, and couldn't figure out how to do it as I thought iif Statements could only be used with two pieces of data.
I used the following:
-----------------------
SELECT ARDetailReport.Employee, ARDetailReport.BPName, ARDetailReport.BPNum, ARDetailReport.InvNum, ARDetailReport.InvDate, ARDetailReport.InvAmt, ARDetailReport.Bal, ARDetailReport.Current, ARDetailReport.[31to60], ARDetailReport.[61to90], ARDetailReport.[91to120], ARDetailReport.[121to365], ARDetailReport.Over365,
IIf(Val([121to365])>0,"121 to 365",
IIf(Val([Over365])>0,"Over 365",
IIf(Val([Current])>0,"Current",
IIf(Val([31to60])>0,"31 to 60",
IIf(Val([61to90])>0,"61 to 90",
IIf(Val([91to120])>0,"91 to 120")))))) AS Status
FROM ARDetailReport;
------------------------
I now have a field I can use easily in reports, etc. to group these items by the AR status.
Thanks Jon!