Dlookup via query in vba

[hughess7]

Hi all, I have the following line of code to lookup a value from a range between two fields:

NewRate = DLookup("Petrol", "qry LookupPetrol", "[Minimum] <=" & Engine & " And [Maximum] >= " & Engine)

It runs and returns a value but it is incorrect. It returns the first value of Petrol it finds in the query. Data example below:

Min Max Petrol
0 1.4 0.14
1.5 2.0 0.16

Engine is 1.6 therefore 0.16 should be returned but it returns 0.14 ????

 

[boblarson]

Are your fields named MIN and MAX or Minimum and Maximum? You are using both in your example and neither matches up with the table example or the code example.

 

[hughess7]

Sorry they are Minimum and Maximum, my example wasn't very clear! But it was working correctly, someone had changed his engine size and I hadn't spotted it! Thanks anyway for the reply...