problem with IIf Statement

[Guest]

I am having a problem with an IIf statement on my report. Here is the
background. I have a field that is based on the concat of a union query.
This field is informational in nature. I need to include a specific
compensation rate based on different parts of this field.

I have gotten my IIf statement to work somewhat. controlsource follows:

=IIf([TestType] Like "BAT",(1*100),0)

The issue that I am having here is if I replace the number 100 with the rate
that I need to calculate with the calculation goes null I guess. On the
records that don't include BAT the field shows $ 0.00 on those that do
include BAT it is blank...but with the 1*100 in there it works like a charm.

Any guidance would be appreciated.

Thanks,
ea

 

[Guest]

Where is the "rate" (that you are putting in place of the 1*100) coming from?
I'm guessing that it is not being retrieved/calculated correctly.

 

[Duane Hookom]

You shouldn't use "Like" without using a wildcard like "*".
Try use this expression:

=Abs([TestType] = "BAT") * [Rate]

 

[Guest]

Duane-

I tried this, and it didn't work. The records that don't include BAT showed
$ 0.00 and those that do include BAT were blank. Same as the results with
using the IIF statement. I have been using "*BAT*". I must have taken out
the wildcards in an attempt to get it to work.

EA

 

[Guest]

Bruce-

The rate is coming from one of my tables. I have it included in the source
for the report, and have it in a non visible field on the report. I tried
that thinking that it couldn't calculate on the rate because it couldn't
"see" it, but to no avail.

Any suggestions?

EA

 

[Duane Hookom]

Try:
=Abs([TestType] Like "*BAT*") * [Rate]
Make sure the Rate field is numeric and your text box is large enough to
display the value. Also clear the format property and check any conditional
formatting or code that might affect the control.

 

[Guest]

Duane-

I tried the change that you suggested, and it still doesn't work with the
rate. If I replace the rate with a number, it works just like it should. I
have the rate in the source for the report, so it shouldn't be having a
problem locating that.

I just realized something...the rate is sort of record specific. I need to
include a where clause in there. Basically, the rate that needs to be used
is dependent upon the employee that did the task. I guess what I need to
figure out how to do is...

if the test type is like BAT, multiple by the rate where the employee id in
the report is the same employee id in the table that holds the rate.

Is that clear as mud? Am I going to right direction, and can I put a where
clause in the control source of the field on the report?

thanks...ea

 

[Guest]

Well...I had a blond moment there. Duane, your fix with the Abs Like
statement works like a charm. The record that I was testing with didn't have
the rate entered...which would be why nothing was showing on the report.
Once I put a rate in the underlying table...it works perfectly.

Thanks for your help...again!

Emily