MS Access DSUM Domain Function Returns Value as TEXT Type Value?? Why??

Question

    Given the following Expression used in an Aggregate Query:

         TFISH:DSum ("[Haddock]","[TBLReservations]")             Note:  All  fields in table  are NUMBER Types.

    It appears that the TFISH is assigned a TEXT type by Access rather than an Number Type??

  If I then attempt to add TFISH to another value also determined by the DSum Domain Aggregate Function via the following
expression, Access appears to concatenate the values of the two computed variables, rather than adding them.

        TotRsv: ([TFISH] + [TBEEF])

   Assume  TFISH has a value of 3 returned by DSum.    It appears in Query Dataset View Left Justified
   Assume  TBEEF has a value of 1 returned by DSum.    It also appears in Query Dataset  View as Left Justified

    TotRsv Result displayed  in Query Dataset View is: 31 left Justified. All numeric Sum and other values are right justified.

It therefore appears to me that:

   The variables used to capture the results of the DSum Domain Sum Function - which is computing correctly - are being assigned Text Types rather than Number Types.
    Hence, when attempting to Add them,  ACCESS concatenates them instead of adding them.

Can I change the TFISH and TBEEF variable type to Number?? If so, how??  

What am I missing or doing wrong???

Thanks!!

 Thanks to all for your Responses!! I have noted the different solution options.

I solved my problem by doing the addition as part of the DSum Function itself as follows:

 TOTRsv:DSum("([Haddock] + [Beef])","[TBLReservations]) 

Both [Haddock] and [Beef] are Number Type Fields.

I also made judicious use of the Property Sheets to make all results display the same.

However, I am curious as to why the "+" is used as both the popular Addition Operator as well as the less used Concatenation Operator when there is another Concatenation Operator "&"???? 

Thanks Again

 

Answer 1

Hi,

The Help Files says the DSum() function returns a Variant data type because if no records match the criteria provided, the function returns a Null.

Access is probably treating it as Text since it's a Variant data type. To force the value as Numeric, you can use any of the conversion functions like Int(), CInt(), CLng(), or Val().

Hope it helps...

Answer 2

> It appears that the TFISH is assigned a TEXT type by Access rather than an Number Type??

You don't have to be in the dark about what it really does, just do this in the Immediate window:

?VarType(DSum ("[Haddock]","[TBLReservations]"))

Then look up in the help file what the returning value means.

If Haddock is a number, I would expect DSum to return a number as well.

---

-Tom. Microsoft Access MVP

Answer 3

I suspect that the problem results from your referring to the two computed columns by name when adding their values.  Try repeating the original expressions in the addition operation.

Are you calling the DSum functions here so that the query's result table is updatable?  If not, this is far less efficient than using the SQL SUM operator.

---

Ken Sheridan, Stafford, England

Answer 4

I solved my problem by doing the addition as part of the DSum Function itself as follows:

 TOTRsv:DSum("([Haddock] + [Beef])","[TBLReservations]) 

Both [Haddock] and [Beef] are Number Type Fields

Hello,

I suggest you post your solution here and mark it as answer to close this thread.

+ Operator: Adds two numbers or returns the positive value of a numeric expression. Can also be used to concatenate two string expressions
& Operator: Generates a string concatenation of two expressions.

You could test a simple macro below then you could find the difference.

Sub test()
Dim a, b As Integer
a = 1
b = 2
Dim c, d As String
c = "a"
d = "b"
Debug.Print a + b; a & b
Debug.Print c + d; c & d
End Sub

 

Regards,

Celeste

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].