converting number to text for dlookup & can't sum

[doran_doran]

PW: CLng(DLookUp("[PlanWeight]","tblPlanMatrix","[Ga_Record_ID_2] = '" & [Ga_Record_ID_2] & "'"))

Ok, I am using above query with some other fields. I am trying to bring a numeric value. withouth cLng it's converting to text. Then I put CLng, but it's rounding up even after i change the format to 2 decimal after. Also, when I run sum, its giving me error, b/c some of the record gets error on the dlookup.

Here is what i am trying to do.

1. Convert it to (####.##) format. that way 2 digits after the decimal comes.
2. Then do a sum regardless of the error message. By the way, i am not runing a total query, I will be doing the sum on a report.

Prompt respond will be appreciated.

Regards
Dianna

 

[Mile-O]

If you want to have two decimal places then don't convert it to a Long data type; they are integers and don't have decimal places. Try CSng or CDbl

 

[doran_doran]

thanks for the prompt respond

PW2: IIf(IsNull(DLookUp("[PlanWeight]","tblPlanMatrix","[Ga_Record_ID_2] = '" & [Ga_Record_ID_2] & "'")),0,CDbl(DLookUp("[PlanWeight]","tblPlanMatrix","[Ga_Record_ID_2] = '" & [Ga_Record_ID_2] & "'")))

It's now working.

 

[Mile-O]

That's too much:

PW2: Nz(CDbl(DLookup("[PlanWeight]", "tblPlanMatrix", "[Ga_Record_ID_2] = """ & [Ga_Record_ID_2] & """")), 0)

 

[Jon K]

Nz() should be put insdie CDbl() as CDbl(Null) will return an error.

PW2: CDbl(NZ(DSum("[PlanWeight]","tblPlanMatrix","[Ga_Record_ID_2] = '" & [Ga_Record_ID_2] & "'")))

 

[Mile-O]

Good point.