Using DLOOKUP in a Query 1

[JOEYB99]

I'm using Access 2007 and struggling with DLOOKUP in a query.

There are two tables - PayrollTransactions and RateMaster. They are joined properly. I have tested for this.

My formula is =DLOOKUP("[RateMaster]![Rate]","[RateMaster]","[PayrollTransactions]![TransDate] between [RateMaster]![StartDate] and [RateMaster]![EndDate]")

I am getting the following error message

"The expression you entered as a query parameter produced this error: 'Microsoft Office Access can't find the name 'PayrollTransactions!TransDate' you entered in the expression'."

What am I doing wrong? Can someone please help? Any input would be greatly appreciated - even an alternative approach.

 

[PHV]

What am I doing wrong?
You ask DLookUp to query the RateMaster table with a criteria on the PayrollTransactions table ...

Anyway, why using a DLookUp instead of a JOIN ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JOEYB99]

My output for the query is the payroll transactions along with their appropriate rates - there is more than one rate.

The rate master has rates set up based on a date range. So the Rate Master has a start and end date defined for each rate.

How would I incorporate the JOIN instead of using the Dlookup function? Can you please explain, PHV?

 

[PHV]

What is your actual SQL code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JOEYB99]

Actual SQL code?

I have greatly simplified my situation with table/field names. Anyway, here is the actual SQL code for my query, so far. I intentionally added spacing for ease of readability.


SELECT tblTimeMaterialPayrollWorkFile.[Job Number], tblTimeMaterialPayrollWorkFile.[Sub Job Number], tblTimeMaterialPayrollWorkFile.[Sub Job Description], tblTimeMaterialPayrollWorkFile.[Formatted Cost Code], tblTimeMaterialPayrollWorkFile.[Employee Number], tblTimeMaterialPayrollWorkFile.[Employee Name], tblTimeMaterialPayrollWorkFile.[Employee Class], tblTimeMaterialPayrollWorkFile.[Employee Type], tblTimeMaterialPayrollWorkFile.[Shift Number], tblTimeMaterialPayrollWorkFile.[Regular Hours], tblTimeMaterialPayrollWorkFile.[Overtime Hours], tblTimeMaterialPayrollWorkFile.[Other Hours], tblTimeMaterialPayrollWorkFile.[Ticket Date (YYYY-MM-DD)], tblTimeMaterialPayrollWorkFile.[Sequence Number], DLookUp("[Regular Rate of Pay]","[tblTimeMaterialRateMaster]","[tblTimeMaterialPayrollWorkFile]![Ticket Date (YYYY-MM-DD)] between [tblTimeMaterialRateMaster]![Start Date] and [tblTimeMaterialRateMaster]![End Date]") AS Expr1

FROM tblTimeMaterialPayrollWorkFile INNER JOIN tblTimeMaterialRateMaster ON (tblTimeMaterialPayrollWorkFile.[Job Number] = tblTimeMaterialRateMaster.[Job Number]) AND (tblTimeMaterialPayrollWorkFile.[Employee Class] = tblTimeMaterialRateMaster.[Employee Class]) AND (tblTimeMaterialPayrollWorkFile.[Employee Type] = tblTimeMaterialRateMaster.[Employee Type])

GROUP BY tblTimeMaterialPayrollWorkFile.[Job Number], tblTimeMaterialPayrollWorkFile.[Sub Job Number], tblTimeMaterialPayrollWorkFile.[Sub Job Description], tblTimeMaterialPayrollWorkFile.[Formatted Cost Code], tblTimeMaterialPayrollWorkFile.[Employee Number], tblTimeMaterialPayrollWorkFile.[Employee Name], tblTimeMaterialPayrollWorkFile.[Employee Class], tblTimeMaterialPayrollWorkFile.[Employee Type], tblTimeMaterialPayrollWorkFile.[Shift Number], tblTimeMaterialPayrollWorkFile.[Regular Hours], tblTimeMaterialPayrollWorkFile.[Overtime Hours], tblTimeMaterialPayrollWorkFile.[Other Hours], tblTimeMaterialPayrollWorkFile.[Ticket Date (YYYY-MM-DD)], tblTimeMaterialPayrollWorkFile.[Sequence Number];

 

[PHV]

Why using a GROUP BY clause without any aggregate function in the SELECT list ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

What about this ?
SELECT P.[Job Number], P.[Sub Job Number], P.[Sub Job Description], P.[Formatted Cost Code], P.[Employee Number]
, P.[Employee Name], P.[Employee Class], P.[Employee Type], P.[Shift Number], P.[Regular Hours], P.[Overtime Hours]
, P.[Other Hours], P.[Ticket Date (YYYY-MM-DD)], P.[Sequence Number], R.[Regular Rate of Pay]
FROM tblTimeMaterialPayrollWorkFile P
INNER JOIN tblTimeMaterialRateMaster R ON P.[Job Number]=R.[Job Number] AND P.[Employee Class]=R.[Employee Class] AND P.[Employee Type]=R.[Employee Type]
WHERE P.[Ticket Date (YYYY-MM-DD)] Between R.[Start Date] And R.[End Date]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JOEYB99]

Okay PHV, thanks. I think I know what you are doing here.


I removed my DLOOKUP function completely and added your WHERE statement into my SQL code.

My query runs but there are no results! Why is that? Do I need to do something else?


Should I re-enter the DLOOKUP function but without criteria?

 

[PHV]

Try LEFT JOIN instead of INNER JOIN

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JOEYB99]

Thank you for your continued efforts to help me PHV.

I tried that (modified the SQL code) but still no results.

Can you think of anything else, PHV?

 

[PHV]

Perhaps this:
SELECT ...
FROM tblTimeMaterialPayrollWorkFile P
LEFT JOIN tblTimeMaterialRateMaster R ON P.[Job Number]=R.[Job Number] AND P.[Employee Class]=R.[Employee Class] AND P.[Employee Type]=R.[Employee Type]
WHERE P.[Ticket Date (YYYY-MM-DD)] Between R.[Start Date] And R.[End Date]
OR R.[Job Number] IS NULL

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JOEYB99]

Thanks PHV, but I'll have to think about that one.

The Job Number field is never empty or null. However, I will test it.

I will likely have to examine the raw data to see if there is something funny going on.

 

[PHV]

My guess is that you don't have rows in tblTimeMaterialRateMaster corresponding to tblTimeMaterialPayrollWorkFile with the JOIN criterias ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JOEYB99]

Eureka...success! It worked PHV, thank you very much for all your help.

I did some investigating on my linked Excel spreadsheet RateMaster table. All the date fields had #NUM in them! so I re-linked it and double-checked by re-opening the linked table within Access. No errors.

Now the query we have been working on works great. It is fast and the total number of records agrees with the Payroll Transactions table.

Thank you PHV, thank you.

But now I have a new problem and would really apprecciate your help again. I want to create a Split Form from this query and allow the user to make changes....please visit the Forms forum......