DSUM decimal data type in a query.

I have been trying to sum values (quantity) from a related table and preserve or convert the data to decimal. The original field has the decimal data type but when the function retrieves and sums the data the data type is string. Here is the field definition:

QuantityShipped: Nz(DSum("Quantity","qryDeliveryItemWithOrderNumber","MfgItemNumber = '" & [MfgItemNumber] & "' AND OrderNumber = " & [OrderNumber]),0)

I have tried every method I can think of to preserve or convert the data back to decimal without success.
Brian ThorPresidentAsked:
Who is Participating?
 
hnasrConnect With a Mentor Commented:
Force field type to number by adding 0 to expression.

QuantityShipped: Nz(DSum("Quantity","qryDeliveryItemWithOrderNumber","MfgItemNumber = '" & [MfgItemNumber] & "' AND OrderNumber = " & [OrderNumber]),0)+0

QuantityInvoiced: Nz(DSum("Quantity","qryInvoiceItemWithOrderNumber","MfgItemNumber = '" & [MfgItemNumber] & "' AND OrderNumber = " & [OrderNumber]),0)+0
0
 
IrogSintaCommented:
I don't see anything in your expression that would convert Quantity to a string data type.  What makes you think it's a string type anyway?  Perhaps whatever is converting it is in the query qryDeliveryItemWithOrderNumber.
0
 
peter57rCommented:
I agree.
Try this in the immediate window..

?typename(Nz(DSum("Quantity","qryDeliveryItemWithOrderNumber"),0))

If this does not show as Decimal then the problem lies in the query.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
hnasrCommented:
Reproduce the issue using a sample database and upload.
0
 
Brian ThorPresidentAuthor Commented:
Dear Experts,

Please review attached file. I appreciate your time investment.

Thank you.

Brian
Database1.zip
0
 
IrogSintaConnect With a Mentor Commented:
Interesting.  Apparently using Domain Aggregate functions (i.e. DSum, DCount, etc.) in a query returns what seems to be a String data type while using it anywhere else returns a numeric one.  I tested this in both Access 97 and 2007 and got the same results.  

Although it seems to display as a String, it doesn't behave like one when you perform a calculation with it.  If the result of DSum("Quantity","AnyTable") shows 5, DSum("Quantity","AnyTable") +1 results in 6.  If 5 was really a string, the result would have been "51" instead.  

So in essence you can just treat it like a number.  The only problem I can see is how it becomes left justified when using the field in a form or report.  You can always change the Text Align property to fix that.  I guess you can also use the VAL function to convert it, or you can add a 0 to the result, or multiply the result by 1.  

I couldn't find any documentation about this quirk but perhaps another expert may.
0
 
Brian ThorPresidentAuthor Commented:
IrogSinta,

Thank you for your work on this. My solution so far has been to use a custom function (http://support.microsoft.com/kb/225931) to correct the information on forms and the built-in CDec() function in VBA code. The whole reason for researching this problem is that I have code that compares the Quantity Ordered (Decimal) with the Quantity Shipped (String). Since the two are different data types they aren't considered equal even when they appear the same (except for the justification) on screen. Perhaps there is a better way to deal with this problem but it just seemed right to me to make sure the data type was correct.

Brian
0
 
IrogSintaCommented:
How are you comparing the two fields?  Are you using something like If [Quantity Ordered] = [Quqntity Shipped] Then?  I tried a quick test and they compare just fine.
0
 
Brian ThorPresidentAuthor Commented:
I never tried a datacomparison in the query. Using IIF in the query does seem to indicate a correct comparison. Here is a modification of my code (modified slightly because the actual code walks the RecordsetClone and then takes a different action and to remove error checking):

Sub DataTest()

   Dim db As DAO.Database
    Dim rsOrderInventory As DAO.Recordset
    Set db = CurrentDb
    Set rsOrderInventory = db.OpenRecordset("Query1")
    rsOrderInventory.MoveFirst
    Do Until rsOrderInventory.EOF
        If rsOrderInventory.Fields("Quantity") <> CDec(rsOrderInventory.Fields("QuantityInvoiced")) Then
            Debug.Print "Not Equal"
        Else
            Debug.Print "Equal"
        End If
        rsOrderInventory.MoveNext
    Loop
    rsOrderInventory.Close
    Set rsOrderInventory = Nothing

End Sub

This code produces the desired answers but if you remove the CDec function it does not.
0
 
IrogSintaCommented:
You're right.  A recordset sees this field as a string as well.  Perhaps you can add another calculated field to your query such as OrderComplete: Quantity = QuantityInvoiced and use that in your code.  

On another note, here's another way to refer to your recordset fields:
If rsOrderInventory!Quantity <> rsOrderInventory!QuantityInvoiced Then
0
 
IrogSintaCommented:
Let's see if other experts can chime in about this.
0
 
Brian ThorPresidentAuthor Commented:
Thank you to all who took the time to investigate this issue with me.
0
 
IrogSintaCommented:
hnasr's solution is one I already gave you in my earlier post here when I said that you could add a 0 to the result or multiply it by 1.  

What I meant about waiting for the others input was to see if anyone can give a better explanation on why this was the case or if they knew  any other way other than the few suggestions I already gave you.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Outside of the issue at hand, you should not be using a domain function inside a query anyway as your setting yourself up for poor performance.

The query parser can't optimize the SQL statement encapsulated by the domain function.  

Your far better off to write a nested query or use a sub select.

Jim.
0
 
Brian ThorPresidentAuthor Commented:
Jim,

You are correct about the poor performance. That is likely to be a problem to solve another day. I have tried a nested query but then loose the ability to update the recordset. I have considered the idea of one method for the form and another for my VBA code.

I am not sure what you mean by a sub select.

Brian
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Brian,

<<I am not sure what you mean by a sub select.>>

 A subselect is a SQL Select that appears inside another SQL Select, so something like this:

SELECT AlbumInfo.album_name, album_tracks,
(SELECT COUNT(*) FROM Album
WHERE Album.album_name = AlbumInfo.album_name)
FROM  AlbumInfo
WHERE AlbumInfo.band_name = ‘Metallica’;

 Sub-selects though are not great for performance either (your running a seperate query for each row), but it's better then the DSum().

  best is a another table/view and joining to it.

 Jim.
0
 
Brian ThorPresidentAuthor Commented:
Jim,

Thanks. Can you tell me the difference between a sub select and a nested query? I expect that by a nested query you just mean one that is saved as a separate object. When I replied to you before I was thinking of them as the same thing since they have the same result.

Can you give an example of how you would change my example to use a separate table or view?

Thanks

Brian
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Thanks. Can you tell me the difference between a sub select and a nested query?>>

A subselect is a select within a select, a nested query (at least what I call it) is one where you use it , but with a join as if you were joining to another table.  However it's a view (or a query, which is a SQL statement), so it's "nested".

 You'll notice in the example I gave above, there is no join statement.

 Not sure what's in qryDeliveryItemWithOrderNumber, but in a new query:

1. Add qryDeliveryItemWithOrderNumber as a table.
2. Define a column as Sum([<fieldname>])
3. Bring down other columns you will need to perform the join in the final query.
4. Save it.

5 In the final query, add the query saved in step 4 as a "table" and join to it on the appropriate fields.

Jim.
0
 
hnasrCommented:
No intention of objecting, but to clarify the issue. The previous comment was explaining how to manage the query field in a report.

"So in essence you can just treat it like a number.  The only problem I can see is how it becomes left justified when using the field in a form or report.  You can always change the Text Align property to fix that.  I guess you can also use the VAL function to convert it, or you can add a 0 to the result, or multiply the result by 1."

Mentioning 0 or 1 above, does not imply changing the field type to numeric in the query and hence as a solution to the problem.

In general, an expression in query makes access to guess the nearest data type to assign for that column. Adding 0 to the expression, helps access to decide it as numeric.

Example: table a(f1 as integer)

f1=1
Select f1, Nz(f1,0) from a; ====> 1, 1 (String)

Select f1, f1 +0 from a; ===>1,  1 (Numeric)

Select f1, f1 & False from a; ===>1, 10 (string)

Select f1, f1 OR True from a; ===> 1,-1 (Boolean)
0
 
Brian ThorPresidentAuthor Commented:
Jim,

I missed the lack of a JOIN in your example. I tried your suggestion. It performs very well except that the recordset is not updatable. This presents a problem for the form where this is used. I suppose I could setup the form with domain aggregate calculated controls removing the work from the query. I could then setup the read-only query in VBA where the updatability is not a concern.

Thank you.

Brian
0
 
IrogSintaCommented:
@hnasr, I see where you're coming from.

When I mentioned the Val function or adding a zero, I was actually referring to the query itself and not within a form.  I guess I should have separated that comment into its own paragraph so that it would have been clearer (or at least added the phrase "in the query").

This though didn't seem to be the best solution but rather a work-around.  I wanted to find the reason why it behaved this way in a query and not any where else in Access.  It behaves as it should when used within VBA code or inside a textbox.  Why the need to force it when inside a query?

@Brian, if you didn't understand my comment as being the same as hnasr's, then he should get the points.  Sorry for making you do double work.
0
 
hnasrCommented:
IrogSinta,

" I wanted to find the reason why it behaved this way in a query and not any where else in Access."

An expression in a query, will not be clear what type to use.
With intrinsic type conversion, one tells access his intention of the data type required.

Try this example: used table name a. Created table name: _temp01

Private Sub Command7_Click()
    DoCmd.RunSQL "Select  1 as one, 1.1 as onePointOne, '1' as stringOne, '1' + 1.2 as stringtoDecimal into _temp01 from a"
Exit Sub
1 creates Long
1.1 creates decimal
'1' creates Text
'1'+1.2 creates Decimal

Good luck.
0
 
IrogSintaCommented:
@hnasr, thanks for that explanation.
0
 
hnasrCommented:
Welcome IrogSinta!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.