?
Solved

DSUM decimal data type in a query.

Posted on 2012-08-23
25
Medium Priority
?
1,151 Views
Last Modified: 2012-08-24
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.
0
Comment
Question by:Brian Thor
  • 8
  • 7
  • 5
  • +2
24 Comments
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38327885
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
 
LVL 77

Expert Comment

by:peter57r
ID: 38328267
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
 
LVL 31

Expert Comment

by:hnasr
ID: 38328747
Reproduce the issue using a sample database and upload.
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 

Author Comment

by:Brian Thor
ID: 38329014
Dear Experts,

Please review attached file. I appreciate your time investment.

Thank you.

Brian
Database1.zip
0
 
LVL 29

Assisted Solution

by:IrogSinta
IrogSinta earned 1000 total points
ID: 38329315
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
 

Author Comment

by:Brian Thor
ID: 38329341
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
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38329389
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
 

Author Comment

by:Brian Thor
ID: 38329515
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
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38329611
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
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38329676
Let's see if other experts can chime in about this.
0
 
LVL 31

Accepted Solution

by:
hnasr earned 1000 total points
ID: 38329790
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
 

Author Comment

by:Brian Thor
ID: 38329809
Thank you to all who took the time to investigate this issue with me.
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38329903
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
 
LVL 58
ID: 38330138
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
 

Author Comment

by:Brian Thor
ID: 38330159
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
 
LVL 58
ID: 38330237
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
 

Author Comment

by:Brian Thor
ID: 38330522
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
 
LVL 58
ID: 38330667
<<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
 
LVL 31

Expert Comment

by:hnasr
ID: 38330761
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
 

Author Comment

by:Brian Thor
ID: 38330826
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
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38331088
@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
 
LVL 31

Expert Comment

by:hnasr
ID: 38331423
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
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38331477
@hnasr, thanks for that explanation.
0
 
LVL 31

Expert Comment

by:hnasr
ID: 38331501
Welcome IrogSinta!
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question