Nesting IIf Statements in Microsoft Access 2000

[CBender]

I need to specify an IIf statement to look at another field and depending on
what that field contains I need it to peovide a number associated with the
contents of the associated field.

The following IIf statement is what I am trying to use. However, there is
OBVIOUSLY an error in it because I cannot save the table when I enter this as
the default value for the LineCallPriorityFee field.

=IIf([LineCallPriority]="",0,0), IIf([LineCallPriority]="Report Only",0,0),
IIf([LineCallPriority]="Low",50,0), IIf([LineCallPriority]="Medium",250,0),
IIf([LineCallPriority]="High",500,0)

The LineCallPriority field is text with the following values: Blank, Report
Only, Low, Medium, High.

The LineCallPriorityFee field values should be: 0, 50, 250, or 500
(depending on what the contents of the LineCallPriority field).

I put in an IT request to have an air bag installed in my monitor but my
they just laughed at me. So any assistance in fixing this problem would be
greatly appreciated.


Thanks,

Chip

 

[Clifford Bass]

Hi Chip,

Your statements are not correctly nested. They should look like:

=IIf(Condition1, ValueWhenCondition1IsTrue, IIf(Condition2,
ValueWhenCondition2IsTrue, IIF(Condition3, ValueWhenCondition3IsTrue,
ValueWhenCondition3IsFalse)))

However, I would suggest for something like this that you switch to
the Switch() function:

=Switch([LineCallPriority] = "", 0, [LineCallPriority] = "Report Only",
0 [LineCallPriority] = "Low", 50, [LineCallPriority] = "Medium", 250,
[LineCallPriority] = "High", 500)

You may need to add "IsNull([LineCallPriority]), 0, " (without the
quotes) to the beginning just after the opening paranthesis if
LineCallPriority can be null. You can add something like ", true, -1" (again
without the quotes) before the closing paranthesis in order to catch all
other conditions. A good idea, just in case something invalid manages to get
into the field.

Clifford Bass

 

[Duane Hookom]

You should not be using a nested IIF() for this. What will happen WHEN the
numeric values change? Make a small lookup table with the values. Consider a
table with a primary key of LineCallPriority with another field for the
numeric values.

Data belongs in your tables, not in code and expressions
http://weblogs.sqlteam.com/jeffs/archive/2006/02/10/9002.aspx.

 

[CBender]

Hello Duane,

I appreciate the information. In fact, I do have a seperate table
containing the LineCallPriority data with the following fields: AutoNumber,
LineCallPriority and LineCallPriorityFee.

What I need is to get is the number value entered as data in the Process
table (and not just displayed in the field on the form) based on the
LineCallPriority field entry. When I create a query to display the
information it is displayed in the form but the data is NOT recorded in the
Process table.

Here is the SQL query I am using to create the lookup:

SELECT DISTINCT LineCallSeverity.LineCallPriorityFee,
LineCallSeverity.LineCallPriority
FROM LineCallSeverity LEFT JOIN Process ON
LineCallSeverity.LineCallPriorityFee = Process.ChargebackPriorityFee
ORDER BY LineCallSeverity.LineCallPriorityFee;

Using the above query the user has to select the data to enter instead of
the field automatically retreiving and recording/updating the data based on
the data recorded in the LineCallPriority field (not merely displaying the
data in the field) so the table actually shows the data instead of a blank
entry.

Uing the same query and changing the bound column to 2 instead of 1 displays
the data I need in the field but does not record the data in the table. Is
there a way around this problem?


Thanks for all your help,

Chip






--
Charles Bender
MTU Detroit Diesel

You should not be using a nested IIF() for this. What will happen WHEN the
numeric values change? Make a small lookup table with the values. Consider a
table with a primary key of LineCallPriority with another field for the
numeric values.

Data belongs in your tables, not in code and expressions
http://weblogs.sqlteam.com/jeffs/archive/2006/02/10/9002.aspx.

--
Duane Hookom
Microsoft Access MVP

I need to specify an IIf statement to look at another field and depending on
what that field contains I need it to peovide a number associated with the
contents of the associated field.

The following IIf statement is what I am trying to use. However, there is
OBVIOUSLY an error in it because I cannot save the table when I enter this as
the default value for the LineCallPriorityFee field.

=IIf([LineCallPriority]="",0,0), IIf([LineCallPriority]="Report Only",0,0),
IIf([LineCallPriority]="Low",50,0), IIf([LineCallPriority]="Medium",250,0),
IIf([LineCallPriority]="High",500,0)

The LineCallPriority field is text with the following values: Blank, Report
Only, Low, Medium, High.

The LineCallPriorityFee field values should be: 0, 50, 250, or 500
(depending on what the contents of the LineCallPriority field).

I put in an IT request to have an air bag installed in my monitor but my
they just laughed at me. So any assistance in fixing this problem would be
greatly appreciated.


Thanks,

Chip

 

[Duane Hookom]

If you feel you need to store both values, you can place them both in the
combo box. Bind one column to field in the form's record source. Use a little
code in the After Update event of the combo box to set the value of the other
field to the other column of the combo box:

Me.txtPriorityFee = Me.cboLineCallSeverity.Column(1)

Remember the column numbering is zero-based so the above code references the
2nd column of the combo box.

--
Duane Hookom
Microsoft Access MVP

Hello Duane,

I appreciate the information. In fact, I do have a seperate table
containing the LineCallPriority data with the following fields: AutoNumber,
LineCallPriority and LineCallPriorityFee.

What I need is to get is the number value entered as data in the Process
table (and not just displayed in the field on the form) based on the
LineCallPriority field entry. When I create a query to display the
information it is displayed in the form but the data is NOT recorded in the
Process table.

Here is the SQL query I am using to create the lookup:

SELECT DISTINCT LineCallSeverity.LineCallPriorityFee,
LineCallSeverity.LineCallPriority
FROM LineCallSeverity LEFT JOIN Process ON
LineCallSeverity.LineCallPriorityFee = Process.ChargebackPriorityFee
ORDER BY LineCallSeverity.LineCallPriorityFee;

Using the above query the user has to select the data to enter instead of
the field automatically retreiving and recording/updating the data based on
the data recorded in the LineCallPriority field (not merely displaying the
data in the field) so the table actually shows the data instead of a blank
entry.

Uing the same query and changing the bound column to 2 instead of 1 displays
the data I need in the field but does not record the data in the table. Is
there a way around this problem?


Thanks for all your help,

Chip






--
Charles Bender
MTU Detroit Diesel

You should not be using a nested IIF() for this. What will happen WHEN the
numeric values change? Make a small lookup table with the values. Consider a
table with a primary key of LineCallPriority with another field for the
numeric values.

Data belongs in your tables, not in code and expressions
http://weblogs.sqlteam.com/jeffs/archive/2006/02/10/9002.aspx.

--
Duane Hookom
Microsoft Access MVP

I need to specify an IIf statement to look at another field and depending on
what that field contains I need it to peovide a number associated with the
contents of the associated field.

The following IIf statement is what I am trying to use. However, there is
OBVIOUSLY an error in it because I cannot save the table when I enter this as
the default value for the LineCallPriorityFee field.

=IIf([LineCallPriority]="",0,0), IIf([LineCallPriority]="Report Only",0,0),
IIf([LineCallPriority]="Low",50,0), IIf([LineCallPriority]="Medium",250,0),
IIf([LineCallPriority]="High",500,0)

The LineCallPriority field is text with the following values: Blank, Report
Only, Low, Medium, High.

The LineCallPriorityFee field values should be: 0, 50, 250, or 500
(depending on what the contents of the LineCallPriority field).

I put in an IT request to have an air bag installed in my monitor but my
they just laughed at me. So any assistance in fixing this problem would be
greatly appreciated.


Thanks,

Chip