Expected number calcualted field is left aligned 2

[knuckelhead]

I am thinking that my calculated field is having trouble being a calculated number field. The underlying fields are all number fields. So why would the Qry answer be aligned left. I am worried that the calculated field converted itself to Text. Do you see something in this calc to make the answer to become RIGHT aligned in the Qry?

SetupCleaningMH2: IIf(Val([SellUOM] & "")<>"CS",Nz(DLookUp("SetupCleaningMH","tblPack","PackID=" & [PackID] & " AND " & [StdconvFactor] & " Between LowerConvFactor And UpperConvFactor")),Nz(DLookUp("SetUpCleaningMH","tblPack","PackID=" & [PackID] & " AND " & [CSStdConvFactor] & " Between LowerConvFactor And UpperConvFactor"),0))

FYI: CS means case quantity whereas everything else is pounds or something else. the answer is fine. i just worry that a left align might bite me later.

Knucklehead Smith

 

[Golom]

SetupCleaningMH2: 
IIf( Val([SellUOM] & "")<>"CS",

  Nz(DLookUp("SetupCleaningMH","tblPack","PackID=" & [PackID] & " AND " & [StdconvFactor] & " Between LowerConvFactor And UpperConvFactor")[b][COLOR=red],0[/color][/b]),

  Nz(DLookUp("SetUpCleaningMH","tblPack","PackID=" & [PackID] & " AND " & [CSStdConvFactor] & " Between LowerConvFactor And UpperConvFactor"),0))

Don't quite understand how this is supposed to work.

Val([SellUOM] & "") will return a numeric value so it will never be equal to the text string "CS"

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[knuckelhead]

Sorry for giving you wrong info.

SellUom is text like you said.

the first dlookup is for the sellUom's that are not in "CS" or cases. it will use the PackID and StdConvFactor to dlookup a value from a range in a tblPack.

the second dlookup is for SellUOM's that are in cases or CS. instead of using the StdConvFactor it needs to use the CSstdConvFactor field with the PackID. and then it looks in the range.

i am looking for an answer in decimals and RIGHT aligned. I only say Right aligned because i worry. i am now getting Left aligned. the qry works fine so far. but i want to make sure that i am doing the best calc.

 

[PHV]

and what about this ?
SetupCleaningMH2: Val(DLookUp("SetupCleaningMH","tblPack","PackID=" & [PackID] & " AND " & IIf(Trim([SellUOM] & "")<>"CS",[StdconvFactor],[CSStdConvFactor]) & " Between LowerConvFactor And UpperConvFactor") & "")

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

 

[Golom]

The point was that the Val function returns a number. For example

  Val("CS" & "")

would evaluate to "0" (zero) and not to "CS".

Thus Val([SellUOM] & "") <> "CS" will always be TRUE because no number is equal to "CS" and only the first if the IIF clauses will ever be executed. You probably want

IIf( [SellUOM] & "" <> "CS",

without the Val function.

And you need to specify the zero in the first "NZ" function ...

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[knuckelhead]

Golum. I took out the Val stuff. i still get left aligned for this calculated field in the Qry. i would feel more comfortable if the answer were Right aligned. any more ideas? thanks

here is the new status.

SetupCleaningMH2: IIf([SellUOM] & ""<>"CS",Nz(DLookUp("SetupCleaningMH","tblPack","PackID=" & [PackID] & " AND " & [StdconvFactor] & " Between LowerConvFactor And UpperConvFactor")),Nz(DLookUp("SetUpCleaningMH","tblPack","PackID=" & [PackID] & " AND " & [CSStdConvFactor] & " Between LowerConvFactor And UpperConvFactor"),0))

 

[PHV]

Have you tried my suggestion ?

 

[knuckelhead]

i thought i did use your suggestion. so i guess i do not know what you want me to do. what else am i suppose to do in the calc? thanks alot.

 

[Golom]

I would use PHV's suggestion that puts the IIF where it belongs to just change one parameter in the DLookUp call. He has also wrapped the whole thing in a "Val" call to force conversion to a numeric value.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[knuckelhead]

got ya. i didn't realize that PHV changed my calc already. His method works fine.

thank you guys. i will send money to the club today.

 

[knuckelhead]

This question is similar to my earlier one but it should be simpler. so i thought.

PackUnitHr, PackId and PackConvFactor are definitely Number fields.

The query answer is left aligned. i expected it to be right aligned and therefore needing some kind of better code. Can you help?

PackUnitsHR2: DLookUp("PackUnitHr","tblPack","PackID=" & [PackID] & " AND " & [PackConvFactor] & " Between LowerConvFactor And UpperConvFactor")

Knuckelhead

 

[PHV]

As already showed above:
PackUnitsHR2: [!]Val([/!]DLookUp("PackUnitHr","tblPack","PackID=" & [PackID] & " AND " & [PackConvFactor] & " Between LowerConvFactor And UpperConvFactor")[!] & "")[/!]

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

 

[knuckelhead]

Thanks alot. it works fine.