Help with an IIF statement

  • Thread starter Thread starter ktbull
  • Start date Start date
K

ktbull

I have a report that uses a parameter query to pull a particular city, month
and years data. I need to create a formula field in that report that says if
the city is "Miami" multiply the x field by .025, otherwise if the city is
Dallas, Austin or Houston multiply x field by .05.
 
Start by creating a place in your database (table or additional field) that
stores 0.025 for Miami and 0.05 for Dallas, Austin, and Houston. Then add
this "place" to your report's record source query so you can multiply by the
rate stored in your data.

A complex IIf() statement is not the direction you should be taking. If you
don't want to create a "place", then at least create a small user-defined
function that accepts the City as an argument and returns the rate.
 
I have a report that uses a parameter query to pull a particular city, month
and years data. I need to create a formula field in that report that says if
the city is "Miami" multiply the x field by .025, otherwise if the city is
Dallas, Austin or Houston multiply x field by .05.
Click to expand...

Does the city always have to be on of those above?
Add an unbound control to the Report.
Set it's control source to:
=IIf([City]= "Miami",[XField]* 0.25,[XField] * 0.05)

And what if the City is none of the above?

Set it's control source to:
=IIf([City]= "Miami",[XField]* 0.25,IIf([City] in
("Dallas","Austin","Houston"), [XField] * 0.05,0))
 
I have tried your suggestion and it works beautifully for Miami, but it is
not calculating for Houston, Austin or Dallas...

Thanks for helping!

fredg said:
I have a report that uses a parameter query to pull a particular city, month
and years data. I need to create a formula field in that report that says if
the city is "Miami" multiply the x field by .025, otherwise if the city is
Dallas, Austin or Houston multiply x field by .05.
Click to expand...

Does the city always have to be on of those above?
Add an unbound control to the Report.
Set it's control source to:
=IIf([City]= "Miami",[XField]* 0.25,[XField] * 0.05)

And what if the City is none of the above?

Set it's control source to:
=IIf([City]= "Miami",[XField]* 0.25,IIf([City] in
("Dallas","Austin","Houston"), [XField] * 0.05,0))
Click to expand...
 
I have tried your suggestion and it works beautifully for Miami, but it is
not calculating for Houston, Austin or Dallas...

Thanks for helping!

fredg said:
I have a report that uses a parameter query to pull a particular city, month
and years data. I need to create a formula field in that report that says if
the city is "Miami" multiply the x field by .025, otherwise if the city is
Dallas, Austin or Houston multiply x field by .05.
Click to expand...

Does the city always have to be on of those above?
Add an unbound control to the Report.
Set it's control source to:
=IIf([City]= "Miami",[XField]* 0.25,[XField] * 0.05)

And what if the City is none of the above?

Set it's control source to:
=IIf([City]= "Miami",[XField]* 0.25,IIf([City] in
("Dallas","Austin","Houston"), [XField] * 0.05,0))
Click to expand...
Click to expand...

Then you have done something wrong, or your data is not as you have
indicated.
Regarding >it is not calculating for Houston, Austin or Dallas<
does not tell us much.
If you wish more specific help, then you'll have to post back with
more specific information.
 
Back
Top