DLOOKUP returns wrong data

[Kiwiman]

Good morning

An after update procedure of my Quotes form looks up the chargeout rate of the Employee based on their Employeeid, but returns an incorrect value - where the EmployeeID has more than one rate. The employee will have more than one rate as the rate will be effective from a point in time.

The rates are stored in a table "tblrates". I have tried sorting the table by effective date (descending) - but the dlookup still picks up the first value.

I can get around this by replacing the "tblrates" with the query "qryLabourlookup" that has the employeeid and the chargeout rate (ordered by effective date - desc). I cannot work out why referencing the table itself, the Dlookup gives the wrong value.

Help as always is appreciated.

Cheers

Private Sub EmployeeId_AfterUpdate()

Dim strName As String
Dim strDocName As String, strDocName1 As String
Dim strFilter As String


On Error GoTo Err_EmployeeId_AfterUpdate

strName = "ChargeOutRate"
strDocName = "qryLabourlookup"
strDocName1 = "tblrates"

    'Evaluate filter before it's passed to DLookup function
    strFilter = "EmployeeId = " & Me!Employeeid
    
    'Look up product's unit price and assign it to UnitPrice ontrol.
    Me!UnitPrice = DLookup(strName, strDocName1, strFilter)
    
Exit_EmployeeId_AfterUpdate:
    Exit Sub

Err_EmployeeId_AfterUpdate:
    MsgBox Err.Description
    Resume Exit_EmployeeId_AfterUpdate
    
End Sub

 

[randy700]

The code you posted will ALWAYS return the first value found. To find a different rate, you will need to provide more information in the WHERE clause (strFilter). Something like....

strFilter = "EmployeeID = " & Me.Employeeid & " AND PointInTime = '" & Me.Quarter& "'"



Randy

 

[KenReay]

Hi

You are looking up on the EmployeeId only, as you say this can return "n" records, you need to refine your query to include the date so that you can return a single correct record,

you need to change the line:

strFilter = "EmployeeId = " & Me!Employeeid

to include the effective date

not sure exactly how your system works, but something like:

strFilter = "EmployeeId = " & Me!Employeeid & " AND EffectiveDate = #" & Format(Date(),"yyyy/mm/dd") & "#"

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Kiwiman]

Thanks Kenreay / randy700 for your input

I have tried the following piece of code, now the dlookup is not returning anything - it does not give an error jut returns null. I want the filter to return the right rate that is in effect when the work goes ahead. The startdate is in the Main form frmQuoteHdr.


I've tried the code with the < sign and it returns a value - but always the first value assigned to the employee
I've also tried it with the > sign, but again get null returns.

Any ideas

    strFilter = "EmployeeId = " & Me!Employeeid & " AND EffectiveDate = #" & Format([Forms]![frmQuoteHdr]![StartDate], strFormat) & "#"

 

[randy700]

What value is in strFormat?

Randy

 

[Kiwiman]

strFormat = "DD/MM/YY"

KM

 

[KenReay]

Hi

Try

Format(Date(),"yyyy/mm/dd")

as I suggested, Jet SQL uses USA style dates (mm/dd/yyyy), or the ANSI style I suggested, but does not like the UK style dd/mm/yy in SQL strings

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[PHV]

Any chance you could post the schema of tblRates ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Kiwiman]

Hi Guys

Thanks for the input. The change (date format - great to know) as suggested by KenReay works to a point - where the startdate of the quote agrees to the effective date.

I have change the filter to look for the effective date that is less than the startdate (see code below).

This again returns the first value in the table relating to the employee. e.g

Emp1 rates
Rate1 effectivedate (ED) 01/04/05 £20
Rate2 ED 01/06/05 £25
Rate3 ED 01/09/05 £30

A quote with a start date of 21/07/05 picks up £20, not the expected £25.

I have tried sorting the table by ED, but to no avail. As I said before, I can get around this by using a query, but thought there would be a better way.

the tblrates schema is as follows - not sure if there is an easier way to get the info - so just typed it:

RateID autonumber
CreationDate Date/Time
EmployeeID Number (long integer)
EffectiveDate Date/Time
Trade Text
HourlyRate Currency
ChargeOutRate Currency

    strFilter = "EmployeeId = " & Me!Employeeid & " AND EffectiveDate < #" & Format(Me.Parent!StartDate, strFormat) & "#"

Thanks for the help people - really appreciated.

 

[PHV]

And this ?
strName = "ChargeOutRate"
strDocName = "qryLabourlookup"
strFilter = "EmployeeId=" & Me!Employeeid & " AND EffectiveDate<=#" & Format(Me.Parent!StartDate, "yyyy-mm-dd") & "#"
Me!UnitPrice = DLookup(strName, strDocName, strFilter)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[earthandfire]

If PHV's last suggestion doesn't work, try changing it by setting the EffectiveDate part of the criteria to:
DMax("EffectiveDate","tblRates","EmployeeID=" & Me!EmployeeID)

 

[Kiwiman]

Yes - that is the way I have got it to work. Just trying to avoid using a query, that to me returns the data in just the same way as if I sorted the table. Or is there something that I am missing in regards to how Access stores the records in the table?

Cheers

 

[PHV]

Did you think that Access physically reorganize the records each time you change the sort order ?
Luckily he don't.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886