Dlookup to Track Employee Loans (1 Viewer)

raziel3

Registered User.
Local time
Today, 06:03
Joined
Oct 5, 2017
Messages
273
Good day everyone, I am searching for a method to track employee loans. I have an Employee Loans Table with the fields and data:

EmpName - John Doe
LoanStart - 5/1/19
LoanEnd - 6/30/19
LoanAmt - 800.00
RePmtAmt - 200.00
RePmtTerms - Fortnightly

On my Payroll query I am trying use a Dlookup to return the RePmtAmt for John Doe for Payroll Period Ending 5/31/19.

Using This:
Code:
DLookup("RePmtAmt","
tblEmpLoans","[PeriodEnd] <= " & [LoanStart] & " And [PeriodEnd] >= " & [LoanEnd])

but PeriodEnd is a field in the Payroll Query not the Employee Loans Table so how I 'switch around' the criteria, is it possible or am I approaching this wrong?
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 06:03
Joined
Apr 27, 2015
Messages
6,286
Change you Domain in the DLookup syntax from the table to the query.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 03:03
Joined
Aug 30, 2003
Messages
36,118
I would use a non-equi join, as the DLookup() will be a performance hit if the table is large. You'd also need date delimiters in the DLookup(). That said, I suspect this is what you're looking for:

"LoanStart <= #" & PeriodEnd & "# AND LoanEnd >= # & PeriodEnd & "#"
 

raziel3

Registered User.
Local time
Today, 06:03
Joined
Oct 5, 2017
Messages
273
Change you Domain in the DLookup syntax from the table to the query.

I tried that but it's not working. The Dlookup is blank.

I used this:
Code:
DLookUp("RePmtAmt","tblEmpLoans","[LoanStart] >= #[PeriodEnd]# And [LoanEnd] <=#[PeriodEnd]#" And "[EmpName]= '[EmployeeName]'")

but I have 2 emplyoyees with loans,

Emp1 Loan Repayment Amount = 200
Emp2 Loan Repayment Amount = 1000

The query returns 200 for both employees.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:03
Joined
May 7, 2009
Messages
19,175
Code:
DLookup("RePmtAmt","
tblEmpLoans","[EmpName]='John Doe' And #" & Format([PeriodEnd],"mm/dd/yyyy") & "# Between [LoanStart] And  [LoanEnd]")
 

raziel3

Registered User.
Local time
Today, 06:03
Joined
Oct 5, 2017
Messages
273
I think I've got it. I created a sub query to get the loans that is within the ending Pay Period and then joined it to my Pay Summary Query. I did not use the Dlookups. Will test with more data to see how it runs.
 

Users who are viewing this thread

Top Bottom