DLookUp with criteria isn't working (1 Viewer)

[chuckcoleman]

I have a query that has a DLookUp function in it with criteria but it's not producing the results I need.

The DLookUp is-

TESTLookUp("LaborRate","[Locations]","[Location Code]='" & [RepairLocation] & "' AND '" & [VendorReferenceNo] & "' Like [InvPrefix] & '*'")

[RepairLocation] and [VendorReferenceNo] are in the underlying table to the query. [LaborRate] and InvPrefix are in the [Locations] table, where the DLookUp, looks.

Everything works in the criteria except the 'Like' statement. The [Location] table looks like:

LocationName----------LocationCode-------LocPrefix-------InvPrefix----LaborRte
Laredo...........................AX482................AX482.............................$36.00
Laredo-LEM----------------AX482------------AX482-LAR........LAR............$0.00
San Antonio...................SO232...............SO232...............................$40.00
Council Auto..................NX001................NX001-UPA........UPA...........$80.00
Council M&R..................NX001................NX001-CBM........CBM..........$30.33

The InvPrefix values for Laredo and San Antonio are blank; no entry.

When I have records that the query uses and those records include only Laredo-LEM, I see a LaborRte of $36.00 instead of $0.00

What am I doing wrong?

Thanks,

Chuck

 

[Mark_]

What are your VendorReferenceNo's? I don't see them in your sample data.
You are using "VendorReferenceNo LIKE" in your lookup. Is this correct?

 

[chuckcoleman]

[VendorReferenceNo] is a field in the underlying table for the query. In that underlying table, a value for [VendorReferenceNo] might be something like, "LAR0910" In the [Locations] table, the value of [InvPrefix] will either have a value in it or it will be empty/blank. In my example, I'm trying to match the [Location Code] to [RepairLocation], which it does, and I am also trying to use LIKE to match out of the [VendorReferenceNo] where it matches the value of [InvPrefix], in this case 'LAR'. So, it appears to ignore/not work in matching the [InvPrefix] to a part of [VendorReferenceNo] using LIKE.

Make sense?

 

[The_Doc_Man]

OK, here is something to consider. DLookup will return only one value. You are talking about a query but the problem is that from post #3, [VendorReferenceNo] isn't a member of the [Locations] table, it is a member of another table that is in the same query that uses the DLookup.

Well, if [VendorReferenceNo] isn't in the Locations table, then NO records exist IN THAT TABLE to match the DLookup criteria. Which doesn't bother domain aggregate functions because they are designed to not hiccup when faced with a NULL result.

Look carefully at your criteria and understand this: EVERYTHING in the criteria clause MUST be in the domain you are querying, which in this case is the Locations table. And from your description, that is not the case.

FURTHER consider that if more than one record in this other table you mentioned could possibly have matched your selection criteria, you will only get the FIRST one. If there was another one and it was different, you would never know it.

 

[chuckcoleman]

Well, Sugar-Jets!

 

[Mark_]

Sugar-Jets?

SUGAR-JETS?

Not ever a "Dag-Nabbit"???

 

[chuckcoleman]

DBG, please check your email.

 

[Bullschmidt]

Another thing to consider that MIGHT be somewhat helpful is that DLookup can be based on a table as was discussed above OR it could be based on a query (but not the same query that it is being used in as that would be circular).