Iif statement for 3 alternatives ?

[PNGBill]

Hi forum,

I Have 3 options in a string. Weekly, Fortnightly and Monthly and I want to replace these with Week, Fortnight and Month accordingly.

iif([LDPayFre] ="Fortnightly", "Fortnight", "Week") works for two. How to get the third in ??

As I typed this I also realised I could remove the last two chrs from the right hand side of the string [LDPayFre] but if I can be pointed in th eright direction for the iif statement variation please do so.

Here is the query field expression

Line03: "every " & (IIf([LDPayFre]="Fortnightly","Fortnight","Week")) & " for the next " & [LDPayNo] & " pay periods."

 

[Galaxiom]

Nest two IIF statements.
IIf([LDPayFre]="Fortnightly", "Fortnight", IIf([LDPayFre]= "Weekly", "Week", "Month"))

Incidentlly it is usually better to use a number to represent the pay frequency. It is faster to execute and simpler to write.

One trick with this kind of thing is using 1 for weekly, 2 for fortnightly, -1 for monthly, -12 for annually, 0 for casual etc. The positve numbers represent weeks and the negative represent months.

The users select the period using a combobox. They see the name but the number is recorded in the table. Deft use of IIF statements in queries can work with the calculations.

 

[PNGBill]

Thanks GalaxiomAtHome, Appreciate the advice and ideas.

 

[shadow9449]

In general, I use the Switch statement instead of nested IIFs. It's easier to read.

Here's the example from the Access Help file:

Function MatchUp (CityName As String)
    Matchup = Switch(CityName = "London", "English", CityName _
                    = "Rome", "Italian", CityName = "Paris", "French")
End Function

This example should explain it. See the help file if you need more detail.

SHADOW

 

[PNGBill]

Will this work in a query ?

 

[Galaxiom]

Yes

NewName: Switch(fieldname="x","a",fieldname="y","b",fieldname Not In("x","y"),"other", fieldname Is Null, "empty")