DSUM value Return as string

[Shafique]

HI EVERYONE.
in my query i set a field as USED30 where i get last 30 days used weight.
my table name is IMPPRD
[REELWEIGHT] as number
[REELCOLOR] as short text

but the DSUM Function's value Return as string
WHY......

 

[welshgasman]

Your syntax is wrong.
Look at the difference you have between the date -31 and date + 1
Put all that into a string variable in the immediate window then debug.print it to see what you actually have, NOT what you think you have.
AFAIK DSum() cannot return a string? That woul make no sense whatsoever.

 

[welshgasman]

Well I stand corrected.
I just tried this

SELECT tblBoxID.BoxID, tblBoxID.PackedQty, DSum("PackedQty","tblBoxID","ID > 20") AS Expr1, VarType([EXpr1]) AS Expr2
FROM tblBoxID;

and the vartype was 8, which is a string.?

However you can easly add a numeric to it and it does not fall over, so for all intents and purposes it is numeric.?

 

[Micron]

The return value of DSum is a variant, so I suppose the data type of the returned value could be anything, including Null.

 

[Shafique]

The return value of DSum is a variant, so I suppose the data type of the returned value could be anything, including Null.

I added VAL and Nz function to this formula and all was well..
THANKS for your Valuable time

USED30: Val(Nz(DSum("REELWEIGHT","IMPPRD","[REELCOLOR]='" & [RCOLOR] & "' AND [PRDATE]> # " & Date()-31 & "# AND [PRDATE]< # " & Date() & "#+1"),0))

 

[Micron]

I think we established that the result was text because one of the inputs is text (RCOLOR). Based on the limited data you show it looks like it's numeric so why is it text (or did you manually left justify the field)? Also wondering why you use Val function for this because you don't seem to get a mix of numbers and text in the calculated result. If you want the calculation to be number data type, why not use a type conversion function such as CLng or Cint instead? You would be wise to use NZ function inside of that if a calculated result could be null.