DSUM Result comes as String

[Shafique]

MTIN: DSum('QTY',"INKIN","[IDATE] <#" & Date() & "#+1 AND [IDATE] >#" & DateSerial(Year(Date()),Month(Date()),1) & "#-1 AND [INKNAME]='" & [CHAMICALBAL]![INKNAME] & "' AND [MADE]='" & [CHAMICALBAL]![MADE] & "'")
this is formula for current month's Total.
Why it Comes as String in query Builder

 

[Micron]

Just a guess, but I'll say it's because QTY field is text. Either that, or for some records the return is Null thus the value type is a variant. When variants are encountered, Access determines what the value type should be. Perhaps this causes records with values to default to string, since one field record in the query cannot be a number, the next a variant and so on. It's noteworthy that the first few records are null, which is probably what makes the decision about data type right away. You could experiment with table copies and make those nulls go away and see what happens.

You might try wrapping the whole thing in a type conversion function, such as CLng.

 

[welshgasman]

I get type mismatch if I try your syntax where the +1 is outside the # marks.

? "#" & date() +1 & "#"
#23/04/2024#

 

[Shafique]

I get type mismatch if I try your syntax where the +1 is outside the # marks.

? "#" & date() +1 & "#"
#23/04/2024#

MTIN: DSum('QTY',"INKIN","[IDATE] <#" & Date()+1 & "# AND [IDATE] >#" & DateSerial(Year(Date()),Month(Date()),1)-1 & "# AND [INKNAME]='" & [CHAMICALBAL]![INKNAME] & "' AND [MADE]='" & [CHAMICALBAL]![MADE] & "'")

same results

 

[welshgasman]

Surround it with a VAL() then