Dealing with a missing value (Access 2003 xp sp3)

Topic

New Reply

My form shows hours booked to a job. In reference to the attached pic
Hours by Permanent staff show in the top white row and are called from a Labour table.
Hours by Contract staff show in the bottom white row and are called from a ContractLabour table.

Labour values are combined to give total hours using code such as: =nz([sbfContLab].[Form]![FAB])+nz([SortedLabour subform].[Form]![FAB])

The lower view of the pic shows what happens when there is no contract labour for the job.

How do I get the correct value to show in this case?

Reply | Quote

Replies

I think you’ll have to create a table or query that lists Permanent and Contract (without knowing the details it’s not possible for me to tell you how exactly), then create a new query based on this with a left join to the table or query that contains the hours, so that even if there is no contract labour, there will still be a (blank) record for it.

Reply | Quote

Thanks Hans,

I built a new query combining both sources as you suggested and it works fine.

Reply | Quote