Dlookup using 2 fields as basis in "Between" range 1

[knuckelhead]

I need an extra statement to make this work. I have a table with 2 ranges of data. The qry needs 2 things to seek the proper records range. I only have 1 thing programmed so far. that's my problem.

I presently have the Qry use PACKUNITS to look at the 2 ranges. you can see PackUnits in the middle of the below Qry. I need a second thing to make this work: PACKid.


PackUnitsHR2: IIf(Nz([Batchsize])=" ",0,Val(DLookUp("Nz([PackUnitHr])","[tblPack]",[PackUnits] & "Between [LowerPackWeight] and [UpperPackWeight]")))

Here is a sample from tblPack:
------------------------------------------------
PackIDHr PackID LowerPackWeight UpperPackWeight PackUnitHr
4110 201 0.00 0.19 1800.00
4112 201 0.19 0.75 900.00
4114 201 0.75 2.00 600.00
4116 201 2.00 999999.00 500.00

4130 205 0.00 5.00 400.00
4133 205 5.00 999999.00 300.00

4140 209 0.00 999999.00 300.00
4211 221 0.00 999999.00 75.00
------------------------------------------------

So, if my PackID is 201 and my PackUnits are 2.34, then i want the answer to be 500.

In the Qry, I need to get the combination of PackID and PACKUNITS to be the basis to look for PackUnitHr from the table. I currently only figured out Packunits, which is not enough.
thanks

 

[PHV]

if my PackID is 201 and my PackUnits are 2.34, then i want the answer to be 500
Something like this ?
Nz(DLookUp("PackUnitHr","tblPack","PackID=" & [PackID] & " AND " & [PackUnits] & " Between LowerPackWeight And UpperPackWeight"), 0)

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

 

[knuckelhead]

This woiks perfectly! For anyone else reading this, I am will use:

PackUnitsHR2: IIf(nz([Batchsize])=" ",0,Nz(DLookUp("PackUnitHr","tblPack","PackID=" & [PackID] & " AND " & [PackUnits] & " Between LowerPackWeight And UpperPackWeight"),0))

PS - i am not sure of i should use " " or 0 for batchsize in the IIF statement. what do you think?

I will donate $$$ as usual to the club for this terrific service.
Knucklehead

 

[PHV]

You may try this:
PackUnitsHR2: IIf(Val([Batchsize] & "")=0,0,Nz(DLookUp("PackUnitHr","tblPack","PackID=" & [PackID] & " AND " & [PackUnits] & " Between LowerPackWeight And UpperPackWeight"),0))

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

 

[knuckelhead]

yes, that works nicely. thanks for all your help. I will donate more.
knucklehead

 

[knuckelhead]

PHV: i notice in the above, you have a "0" at the end of the qry PackUnitsHR2 statement. it works so i left it in.

I tried your wonderful method in a different Qry field but i had to take the "0" out of the below different statement. it was bombing out. So my question is, did you really mean for me to use that ending "0" part in PackUnitsHR2??

the new different field that i am mimicking is:

BatchQty: IIf(Val([Batchsize] & "")=0,0,Round(Nz([SumOfStdQty])/Nz([BatchSize]),2))

Do you like this BatchQty calc? can it be improved? I am trying to mimick your technique.
Knucklehead

 

[PHV]

The "0" you mention is the 2nd argument of the Nz function, i.e. if the DLookUp returns null then the expression evaluates to 0.

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