IIF in Query is there an ELSE

[mattc66 via AccessMonster.com]

I have a really long IIF statement in a Query. I need an ELSE at the end,
because if its none of the options then I need it to be Z. Otherwise my sub
query whiches uses these codes has a blank fld.

Does anyone have any suggestions on how I could handle this?

This is all in the Query
BILLOPT: IIf(Mid([SHIP_VIA],4,2)="","Z",IIf(Mid([SHIP_VIA],4,2)="PB","PB", _
IIf(Mid([SHIP_VIA],4,2)="PP","PP",IIf(Mid([SHIP_VIA],4,2)="FC","FC", _
IIf(Mid([SHIP_VIA],4,2)="TP","TP",IIf(Mid([SHIP_VIA],4,2)="CO","CO", _
IIf(Mid([SHIP_VIA],4,2)="CB","CB")))))))

 

[KARL DEWEY]

You can have only 4 IIF's where there are 5 possibilities and the results of
4th true give #4 and #5 on false.
Alternatively have 4 IIF's and in the 5th have false result in "Error" so
you know something went wrong.

If you have a whole bunch then consider a cross reference table. In your
case the first query will output all your fields plus Expr1:
Mid([SHIP_VIA],4,2)
Then in the second query that uses the first and the reference table left
join Expr1 on corresponding field in the reference table.

 

[Kaj Julius]

I have a really long IIF statement in a Query. I need an ELSE at the end,
because if its none of the options then I need it to be Z. Otherwise my
sub
query whiches uses these codes has a blank fld.

Does anyone have any suggestions on how I could handle this?

This is all in the Query
BILLOPT: IIf(Mid([SHIP_VIA],4,2)="","Z",IIf(Mid([SHIP_VIA],4,2)="PB","PB",
_
IIf(Mid([SHIP_VIA],4,2)="PP","PP",IIf(Mid([SHIP_VIA],4,2)="FC","FC", _
IIf(Mid([SHIP_VIA],4,2)="TP","TP",IIf(Mid([SHIP_VIA],4,2)="CO","CO", _
IIf(Mid([SHIP_VIA],4,2)="CB","CB")))))))

It seems to me that you should be able to make it a bit more straightforward
by simply change it to:

BILLOPT:IIf(Mid([SHIP_VIA],4,2)="PB" Or Mid([SHIP_VIA],4,2)="PP" Or
Mid([SHIP_VIA],4,2)="FC" Or Mid([SHIP_VIA],4,2)="TP" Or
Mid([SHIP_VIA],4,2)="CO" Or Mid([SHIP_VIA],4,2)="CB" , Mid([SHIP_VIA],4,2),
"Z")

or maybe even:

BILLOPT: IIf([SHIP_VIA] Is Null Or Instr(1, "PB, PP, FC, TP, CO, CB",
Mid([SHIP_VIA],4,2)) = 0, "Z", Mid([SHIP_VIA],4,2))

where the list of acceptable values are put into a list.

 

[Ken Sheridan]

Matt:

Using a table as Karl suggests would be the best option as otherwise you are
hard-coding data in the expression; data should really only be stored as
values at column positions in tables.

However, you could do it with a simple function. Paste this into a standard
module:

Public Function GetBillOpt(varShipVia As Variant) As String

Dim strSub As String

strSub = Mid(Nz([varShipVia],""),4,2)

If Instr(" PB,PP,FC,TP,CO,CB",strSub) > 1 Then
GetBillOpt = strSub
Else
GetBillOpt = "Z"
End If

End Function

Call the function in the query, passing the Ship_Via value into it:

BillOpt:GetBillOpt([Ship_Via])

I've assumed that you'd also want Z returned if Ship_Via is Null, hence the
use of the Nz function to return a zero-length string in that case.

Ken Sheridan
Stafford, England