how to represent an unmatching field in Left Join

[Guest]

Hi Guys,

Can someone refresh my memory? I have a left join query that displays
rates(double) from another table .
LEFT JOIN qryEmployeeToEarningsDetail ON
(TimeTracking.PayrollItemWageRef_FullName =
qryEmployeeToEarningsDetail.PayrollItemWageRef_FullName)

if payrollwage items do not exist in another table they are blank of
course. However, how to do i account for these nulls. I have tried
nz(rate,0) and
IIf([TimeTracking].[payrollItemWageRef_FullName]<>[qryEmployeeToEarningsDetail].[payrollItemWageRef_FullName],0,[rate])

Neither work. Any suggestions?

cheers, David

 

[Douglas J. Steele]

What's your definition of "not working"?

Assuming that the field rate is in qryEmployeeToEarningsDetail, then
nz(rate,0) should return a 0 as the rate for each employee in TimeTracking
that's not in qryEmployeeToEarningsDetail.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Guys,

Can someone refresh my memory? I have a left join query that displays
rates(double) from another table .
LEFT JOIN qryEmployeeToEarningsDetail ON
(TimeTracking.PayrollItemWageRef_FullName =
qryEmployeeToEarningsDetail.PayrollItemWageRef_FullName)

if payrollwage items do not exist in another table they are blank of
course. However, how to do i account for these nulls. I have tried
nz(rate,0) and

 

[Guest]

Thanks Doug,
I would like the null values for the rate field to be 0 for calculation
purposes.
I agree that nz(rate,0) should do this but it is not. Here is the whole
query:

"SELECT TimeTracking.PayrollItemWageRef_FullName, CInt([CustomField2]) AS
InvID, nz([rptRate],0) AS rate
FROM TimeTracking LEFT JOIN qryEmployeeToEarningsDetail ON
(TimeTracking.PayrollItemWageRef_FullName =
qryEmployeeToEarningsDetail.PayrollItemWageRef_FullName) AND
(TimeTracking.EntityRef_FullName = qryEmployeeToEarningsDetail.Name);"

Thanks so much,
David

 

[Douglas J. Steele]

So what does it give instead? (and, just to double check, rptRate is a field
in qryEmployeeToEarningsDetail, not in TimeTracking)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Doug,
I would like the null values for the rate field to be 0 for calculation
purposes.
I agree that nz(rate,0) should do this but it is not. Here is the whole
query:

"SELECT TimeTracking.PayrollItemWageRef_FullName, CInt([CustomField2]) AS
InvID, nz([rptRate],0) AS rate
FROM TimeTracking LEFT JOIN qryEmployeeToEarningsDetail ON
(TimeTracking.PayrollItemWageRef_FullName =
qryEmployeeToEarningsDetail.PayrollItemWageRef_FullName) AND
(TimeTracking.EntityRef_FullName = qryEmployeeToEarningsDetail.Name);"

Thanks so much,
David

What's your definition of "not working"?

Assuming that the field rate is in qryEmployeeToEarningsDetail, then
nz(rate,0) should return a 0 as the rate for each employee in TimeTracking
that's not in qryEmployeeToEarningsDetail.

il].[payrollItemWageRef_FullName],0,[rate])

Neither work. Any suggestions?

cheers, David

 

[PC Datasheet]

The NZ function returns a string when in an SQL statement. Change to CInt(
nz(rate,0) ) or IIF(IsNull([Rate]),0,[Rate])

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

Hi Guys,

Can someone refresh my memory? I have a left join query that displays
rates(double) from another table .
LEFT JOIN qryEmployeeToEarningsDetail ON
(TimeTracking.PayrollItemWageRef_FullName =
qryEmployeeToEarningsDetail.PayrollItemWageRef_FullName)

if payrollwage items do not exist in another table they are blank of
course. However, how to do i account for these nulls. I have tried
nz(rate,0) and