conditional lookups?

[Pam]

I need to know how to do a conditional lookup for a value
in Access. I have a table which has employee info, linked
to a table which shows different hourly rates for
different jobs for each employee. (all employees may have
different rates of pay for these jobs). Also, there is a
pay rate for piece rate jobs. From my input form, I need
to lookup the value of the piece rate if the job number is
under 300. If the job number is over 300 it needs to get
the rate from the hourly pay rate table by looking up the
employee number and job number. How can I get this pay
rate based on the conditions? I know I can start with a
basic if statment to see if job number is < 300, but can't
get how to lookup the hourly rate.
I'm stuck & really need to get this finished!
Thanks for any assistance-

 

[Wayne Morgan]

Try a DLookup statement to get the value you need.

 

[Pam]

I tried Dlookup first, and could not get it to read the
number amount - it would read the job name field, but not
the rate field. How do I get dlookup to return the rate
(which is a currency field, as is the field I'm reading it
into?
Thank you-

 

[Wayne Morgan]

Try something similar to this.

=IIf([JobNumber]<300, DLookup("[PieceRate]", "PieceRateTable",
"[JobNumber]=" & [JobNumber]), DLookup("[Rate]", "HourlyPayRateTable",
"[EmployeeNumber]=" & [EmployeeNumber] & " And [JobNumber]=" & [JobNumber]))