Using Dlookup (1 Viewer)

[MarionD]

Hi there,
Is anyone willing to help me a bit on this:

I have a table tbl_Rates which holds the amount an employee would be payed within a certain time limit. eg.
01.01.2016 till 31.12.2017 - 50 €
01.01.2018 till 31.12.2018 - 55 €
01.01.2019 till (is null) -60 €

Now for reporting purposes I need to:

Work done on 25.10.2016 - Rate 50€
Work done 02.02.2019 - rate 60€

I have this: rate = Nz(DLookup("[RateofPay]", "[tbl_Rates]", "[tblxx_Personal_Id]=" & Stunden!tblxx_Personal_ID & " and " & "[RateStart]<= " & sqlWorkdate & " and " & "[RateEnd]>= " & Workdate), 0)

which work fine but not if the period is in the current period(without an end date... so I need something like this:

rate= Nz(DLookup("[RateofPay]", "[tbl_Rates]", "[tblxx_Personal_Id]=" & Stunden!tblxx_Personal_ID And "[RateStart]<= " & Workdate And "[RateEnd]>= " & sWorkdate Or IsNull([RateEnd])), 0)

Whew! I hope this makes sense to someone....
Basically I have the date on which the work was done and must find the applicable rate....

 

[bdra2778]

The best thing you should do is put a date to infinity (31.12.9999) in End Date.

 

[plog]

OVerall, we need more context. That Dlookup is chock full of weird references:

 ..."[tblxx_Personal_Id]=" & Stunden!tblxx_Personal_ID &...

1. Is tblxx_Personal_Id in tbl_Rates? If not, this is a no go from the start. You can only use fields in the criteria argment that exist in the table argument.

2. What does Stunden!tblxx_Personal_ID references? Is that a control on the report? Doesn't seem like the right way to reference it. I bet your code has no idea what you are talking about.

[RateStart]<= " & sqlWorkdate & " and " & "[RateEnd]>= " & Workdate

3. Why are you using 2 different date variables? If you want to know if a date falls between 2 other dates you would compare that date to those other 2. You are comparing one date value to the Start and then another to the End. That doesn't make sense.

---

In general, my advice is to write out a logical sentence saying what you want to do and then convert that from English to code:

I want the rate of pay that occurs before the Start, and then is either before the end date or of the record with a null end date.

Converting that to code gives us this:


DLookup("[RateofPay]", "tbl_Rates", "[RateStart]<=" & Workdate & " AND (IsNull([RateEnd]) OR [RateEnd>=" & Workdate & ")")

No Nz needed, just parenthesis and an OR to capture the logic of that second criteria for the RateEnd.

 

[MarionD]

Hi there and thanks for the help!

I'm sorry if seems abit mixed up, I try and change the german terms to English when asking a queation as I think it might make more sense then.

1. Is tblxx_Personal_Id in tbl_Rates?.. yes it is

Your last dlookup seems exactly what I want, but I also have to look for the record for a certain employee - can one add an employee ID to the criteria?
I have a local table that I clear and fill every time the report is printed.

I loop through the recordset "workhours" which contains the Employee_ID ,and Nr.of hours worked, and work date.
In the local table I have a field "rate" which I fill by looking in the Tbl_Rates to find the correct amount for the work-date and Employee_ID.

The tbl_Rates holds multiple records for one employee ID with from and till dates for a certain rate, the last being the current rate (with no end date)

Thanks again.

 

[MarionD]

Thanks Plog, Have got this working now.
I used a recordset, which I filtered by Employee_ID, then used a findfirst with the dates as criteria. Works well.