Conditional calculation in report field

[gh444]

Hi,



I have the following as the Control Source for a text field on a Report

=IIf([Time type]="Overtime",1.5*([Finish]-[Start]),1*([Finish]-[Start]))

The idea is that the working hours are totalled each day for each type of work, i.e. normal time and overtime. Overtime is paid at time and a half. Finish and Start fields are short time format.

The values "Overtime" or "Normal Time" appear in another field on the same report.

It appears that the [Time type]="Overtime" part of the expression is not being recognised as the answer is always [Finish]-[Start].

Can anyone help?

Thanks,
Graham

[pbaldy]

What does the type field actually store? Is it perhaps an ID value and you're seeing a lookup field displaying the text? Or is there perhaps a space?

Why bother with the "1*" in the False argument? You're not changing the answer, but you're making Access work harder to get it.

[gh444]

Thanks for the reply Paul.
The type field stores one of two text values "Overtime" or "Plain time".
There are records in the underlying table populated with one or the other.
The field in the table is populated from a single column combo box on a form.

[pbaldy]

I don't see why that wouldn't work then. Can you post the db, or a representative sample?

[gh444]

Hi Paul,
the database contains names, payroll nos. etc so have created this small database to illustrate the problem. (See attached).

I don't understand why the calculated field called 'Rate' in the 'Weekly_times_sheet' query always returns 1 from the following expression.
Rate: IIf([Time_type]="Overtime",1.5,1)

Thanks,
Graham

[pbaldy]

It's because of the lookup field in the table. This works:

Rate: IIf([Time_type]="0",1.5,1)

More on lookup fields and why many of us don't like them:

http://access.mvps.org/access/lookupfields.htm

[gh444]

Thanks for your quick reply Paul. Much appreciated!

Having read the link I can see how Lookups can be problematic.

Just so that I fully understand, you are saying that the reference in the IIf statement is to the record number in the Lookup table where the first record is numbered as zero?

Regards,
Graham

[pbaldy]

Well, it's odd, and since I never use lookup fields I didn't really understand it. What I expected and would normally have seen is a lookup table with say an ID field and the text field. Your formula would have been looking for the ID field, not the text field. Your table only contains 1 field, so at first I didn't see the problem. I poked around a bit and apparently it's looking at an index rather than the value, because of the bound column property. See attached.

[gh444]

Thanks Paul, brilliant spot!

Will be giving some thought to Lookup alternatives.

Much appreciated. Problem solved!

Graham

[pbaldy]

Happy to help Graham! To clarify, lookup tables are fine, it's that table-level lookup field that causes problems.

[gh444]

For the benefit of others that may read this.
This link also discusses the problems of using Table level Lookups:
http://bytes.com/topic/access/answer...-lookup-fields

[pbaldy]

I've met Albert a couple of times; he is very, very good with Access. He can be very wordy, as he is there, but the info is always good.