Dlookup with Multiple Criteria

[ria4life]

HI All,

Need some assistance with a dlookup code…..I previously had this function which worked out to my needs.....

Combo27 = DLookup("Desc", "[HPEMProblemCodes]", "TaskCode= '" & [Problemcode] & "'")


Now I am trying to add a second criteria to the dlookup where temp1 in my form must also match category in the HPEMProblemCodes table


I came up with this formula but seem to have type mismatch error and I simply can’t figure it out:

Combo27 = DLookup("Desc", "[HPEMProblemCodes]", "TaskCode= '" & [Problemcode] & "" And Temp1 = " & [Category] '")

Taskcode, Description, and Category in the HPEMProblemCodes table are formatted as short text.
Taskcode could be either 3 numbers or 3 letter combinations
Description is a worded description of the problem
Category is a short text

Please assist.

 

[CJ_London]

try

Combo27 = DLookup("Desc", "[HPEMProblemCodes]", "TaskCode= '" & [Problemcode] & "' And Temp1 = '" & [Category] "'")

 

[ria4life]

Thanks CJ,

But i got a compile error on execution

 

[ria4life]

i got this to work with the additional "&" to the end.

However it still does not return the correct record...returns the first record



Combo27 = DLookup("Desc", "[HPEMProblemCodes]", "TaskCode= '" & [Problemcode] & "' And [Temp1] = '" & [category] & "'")

 

[CJ_London]

my bad for the missing &

re not returning the right record - perhaps you have several records with same taskcode and temp1. Dlookup will only return the first one it finds - and records are stored randomly so what is first this time may not be first next time - you need to refine your criteria to ensure only one record can be found.

Alternatively try DFirst tho' I suspect that is not what you require

 

[ria4life]

Th lookup table HPEMProblemCodes consists of 3 columns:

Taskcode
Description
Category

In the three columns, Only the Taskcode are the same... Description and Category are all different.

 

[CJ_London]

in that case I don't understand what you mean by 'returning the first record'

 

[ria4life]

Cj,

Here is the setup of my table:

Taskcode ----- Description ------------Category

12345-----------Repaird Device --------Production
12345 ----------Replaced Device --------HPEM

(Ignore the dashes of course..only used to line up the text here)

When i run the lookup on taskcode=12345 and category=HPEM.....I Get Repaired Device instead of Replaced Device.

Hope this is a better explanation.

 

[CJ_London]

looks like your table does not contain a field called Temp1

 

[ria4life]

Thanks,

Your last comment mad me realize i had it reversed:
This works perfectly

Combo27 = DLookup("Desc", "[HPEMProblemCodes]", "TaskCode= '" & [Problemcode] & "' And [category] = '" & [Temp1] & "'")