IIf Function in Access 2

[alexlc]

I have a query in Access, that is going to a report, that is then exporting to Excel.

One of my fields, in the Exported File needs to return text: "Day", Night", "Sat", "Sun" etc based on character values in one field in another table.

I would like to write an Iif statment that allows me to do Iff - Then - Else & start a new If statement, or however it works.

Below is the current formula - how do I expand this?

Shift Type: IIf(dbo_Invoice_Detail!Sub_Pay_Type="A","Day"," ")

I normally use Crystal & it's so much easier with words!!

Cheers Alex

 

[BobStubbs]

You need to be careful with field names etc in expressions such as Iif ... I think you need square brackets round the field name. One of these should work (I am not sure which!)...

[dbo_Invoice_Detail!Sub_Pay_Type]
  or
dbo_Invoice_Detail![Sub_Pay_Type]

However, nesting IIf statements will get very messy - from your question it seems that there might be many character values to translate? Instead, why not create a small look-up table, with just two columns:

Sub_Pay_Type (text, 1 char, primary key)
Sub_Pay_Description (text, to hold your values such as Day, Night etc)

Relate Sub_Pay_Type in the two tables
Include this table in the query which drives your report, and list Sub_Pay_Description in the report.

I think this has several advantages:

-- No messy nested IIf statements
-- You can use the same lookup table in several forms / queries / reports without having to re-write code each time
-- You can add values, change descriptions etc. via a table change, instead of having to change VBA code.

As another example, here's how to look up a description into a form, based on a numeric value in an other field. You could use the same method to display your descriptions in a form, based on your alphabetic codes. This code is placed in the Control Source of an unbound text box on the form:

=DLookUp "[RclDescription]", "tblRclDescriptions", "[RclStageNumber]=forms!frmMyFormName![RclStageNumber]")

Also set these properties on the [Data] tab of the text box:

Enabled = No
Locked = Yes

I hope that this helps.


Bob Stubbs

 

[lespaul]

And the structure for the nested statement would be:

IIF(Comparison, Value if True, If False do this)

Shift Type: IIf(dbo_Invoice_Detail!Sub_Pay_Type="A","Day",iif(dbo_Invoice_Detail![Sub_Pay_Type] = "B", "Night", iif(dbo_Invoice_Detail![Sub_Pay_Type] = "C", "Graveyard", "Unknown")))

Leslie

 

[alexlc]

Wahey!!!
Quality - look-up table was just the thing, I'm sure all the rest will come in useful at a later date
Cheers Alex