MULTIPLE IF ELSE IN MS ACCESS

[Ashley]

I have a case where
I need to have a status column
and there will be 4 types of status so
i need multiple if then statements i don't know if its possible

If i used this

iif(paymentdate <= date() and isnull(shippeddate), "ORDER PLACED",
"PAYMENT PENDING") as STATUS

I can have only 2 cases but I need to look at shipped date as well and
have status

case where shippeddate is not null i can have

OrderShipped.

and where deliverydate is not null i can have Order Delivered

so how do i get all this in same column

Is it possible to do this in access.

 

[fredg]

I have a case where
I need to have a status column
and there will be 4 types of status so
i need multiple if then statements i don't know if its possible

If i used this

iif(paymentdate <= date() and isnull(shippeddate), "ORDER PLACED",
"PAYMENT PENDING") as STATUS

I can have only 2 cases but I need to look at shipped date as well and
have status

case where shippeddate is not null i can have

OrderShipped.

and where deliverydate is not null i can have Order Delivered

so how do i get all this in same column

Is it possible to do this in access.

You can nest IIf statements:

iif([paymentdate] <= date() and isnull([shippeddate]), "ORDER
PLACED",IIf([paymentdate] <= date() and [ShippedDate] < date(),"Order
Shipped",IIf([OtherField] > Criteria,"More Text", DefaultText")))

 

[Tom Lake]

I have a case where
I need to have a status column
and there will be 4 types of status so
i need multiple if then statements i don't know if its possible

If i used this

iif(paymentdate <= date() and isnull(shippeddate), "ORDER PLACED",
"PAYMENT PENDING") as STATUS

I can have only 2 cases but I need to look at shipped date as well and
have status

case where shippeddate is not null i can have

OrderShipped.

and where deliverydate is not null i can have Order Delivered

so how do i get all this in same column

Is it possible to do this in access.

Suppose The Order was shipped and delivered? Does DELIVERED have
priority over SHIPPED? Do they both have priority over ORDER PLACED?

The following assumes that.

I'd use a VBA function rather than nested IIf functions.

Function OrderStatus(PymtDate, ShippedDate, DeliveryDate) As String
If IsNull(PymtDate) Then OrderStatus = "PAYMENT PENDING"
If PymtDate < Date() Then OrderStatus = "ORDER PLACED"
If Not IsNull(ShippedDate) Then OrderStatus = "ORDER SHIPPED"
IF Not IsNull(DeliveryDate) Then Order Status = "ORDER DELIVERED"
End Function

Tom Lake

 

[John Spencer]

You can use nested IIF statements

IIF( Criteria, TrueResult, IIF(Criteria, TrueResult, IIF(Criteria,
TrueResult, FalseResult)))

Easier to use is the SWITCH function. Look that up in the VBA HELP.
SWITCH (Criteria, TrueResult, Criteria2, TrueResult2, Criteria3,
TrueResult3, Criteria4, TrueResult4)

 

[Marshall Barton]

I have a case where
I need to have a status column
and there will be 4 types of status so
i need multiple if then statements i don't know if its possible

If i used this

iif(paymentdate <= date() and isnull(shippeddate), "ORDER PLACED",
"PAYMENT PENDING") as STATUS

I can have only 2 cases but I need to look at shipped date as well and
have status

case where shippeddate is not null i can have

OrderShipped.

and where deliverydate is not null i can have Order Delivered

so how do i get all this in same column

You can nest IIf function calls:

IIf(a=b, bvalue, IIf(a=c, cvalue, IIf(. . .) . . .))

but that can get more than a little messy.

Also check VBA Help for the Switch function.