Access Report - Conditional Field Visibility

[smuir]

I am working with a basic access form for a payroll system and need to modify it such that if an employee worked overtime, then their overtime payrate and hours show up on the report below their regular payrate and hours.

Ex:
40.00 hours @ $77.00/hr
5.00 hours @ $102.00/hr

If an employee's overtime hours = 0, then the overtime payrate and hours fields should not be visible on the report.

Ex: 40.00 hours @ $77.00/hr

NOT:
40.00 hours @ $77.00/hr
0.00 hours @ $102.00/hr

Secondly, if the employee's overtime payrate is the same as their regular payrate, then their total hours and the single payrate should be visible.

Ex: 45.00 hours @ $77.00/hr

I have come close to this outcome using conditional formatting on the fields, telling the text to print as white if the field should not be visible. However, the formatting of the report is very important and I have two fields that need to be in the same position, only one being visible depending on if the overtime rate regular payrate are equal.

Ex: I either need to see...

40.00 hours @ $77.00/hr OR 45.00 hours @ $77.00/hr
5.00 hours @ $102.00/hr

...in the same starting position on the report. If one field prints in black and the other prints on top of it in white the result is a very light gray. (Not good for invoicing clients!)

Is there anyway to have one field simply not print (in white or any other color) if it does not belong on the report? Can I use an if/else statement somewhere in the field properties to accomplish this?

[nico5038]

You can change your report query to deliver the hours in separate records like:

select "Regular"as Type, IIF(hours>40,40,hours) as RegularHours from tblYours
UNION
select "Overtime" as Type, hours-40 as Overtime from tblYours where hours > 40;

This UNION query will give one record for 40 hours or less and two for > 40 hours.

You'll have to add ofcourse the additional fields (like the rate), but i guess you get the idea.

Nic;o)

[smuir]

Thank you for your reply - the query already does all the hours seperation and calculations. I'm concerned with the report formatting only. Can I use the IFF function on a report field? I can't seem to get it to work. I need something like this in one field so that the report will print in an acceptable format to present to clients:

iff(RegPay <> OTPay, RegHrs, RegHrs+OTHrs)

After that I simply have the overtime hours set not to show up if the payrates are the same (RegPay = OTPay) or if OTHrs = 0.

You can change your report query to deliver the hours in separate records like:

select "Regular"as Type, IIF(hours>40,40,hours) as RegularHours from tblYours
UNION
select "Overtime" as Type, hours-40 as Overtime from tblYours where hours > 40;

This UNION query will give one record for 40 hours or less and two for > 40 hours.

You'll have to add ofcourse the additional fields (like the rate), but i guess you get the idea.

Nic;o)

[nico5038]

You can use in a textbox on the report:

=IIF(condition, True, False)

But you can also add the condition in the report query to create a new field like:

select IIF(condition, True, False) as TotalHours, ....

Now TotalHours can be placed on the report as a "normal" field.

Nic;o)