Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

DSum syntax in query designer

Posted on 2014-11-29
6
Medium Priority
?
173 Views
Last Modified: 2014-11-30
What is wrong with this syntax?

On Hand Qty: =DSum("[QTY_ORDERED]","tblInventory",[TRNX_TYPE] in ('I','O')")+(DSum("[QTY_ORDERED]","tblInventory",[TRNX_TYPE] ='ORD'")-DSum("[QTY_ORDERED]","tblInventory",[DATE_RECD] Is Not Null"))
0
Comment
Question by:SteveL13
  • 2
  • 2
  • 2
6 Comments
 
LVL 24

Assisted Solution

by:Phillip Burton
Phillip Burton earned 1000 total points
ID: 40471627
The criteria need to be in quotation marks - see http://www.techonthenet.com/access/functions/domain/dsum.php for an example.

In your version, the criteria are outside of the marks.
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40471628
If you were going to test it, just take each DSum at a time, and evaluate it separately to see where the problem lies.
0
 
LVL 18

Accepted Solution

by:
Simon earned 1000 total points
ID: 40471629
Hi Steve, I haven't got access to Access at this device, but think these are the issues...

Square brackets around aliased column name if it includes spaces.
[On Hand Qty]:

The criteria string (3rd parameter) for the DSUM function should be in double quotes. You had no quotes at the start of the parameter though you did have quotes at the end of it.
=DSum("[QTY_ORDERED]","tblInventory","[TRNX_TYPE] in ('I','O')")+(DSum("[QTY_ORDERED]","tblInventory","[TRNX_TYPE] ='ORD'")-DSum("[QTY_ORDERED]","tblInventory","[DATE_RECD] Is Not Null"))

The edited result, ready for copy'n'paste to your query designer"
[On Hand Qty]:=DSum("[QTY_ORDERED]","tblInventory","[TRNX_TYPE] in ('I','O')")+(DSum("[QTY_ORDERED]","tblInventory","[TRNX_TYPE] ='ORD'")-DSum("[QTY_ORDERED]","tblInventory","[DATE_RECD] Is Not Null")) 

Open in new window

0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 58
ID: 40471790
The real answer; your using dsum() in a query<g>.  Dsum and the other domain functions were never intended to used inside a query.   Since they represent Sql statements there is no reason not to write the sql directly.  

They are totally un-optimizable by the query parser and will always yield poor performance.

Jim
0
 
LVL 18

Expert Comment

by:Simon
ID: 40471878
Good point, Jim. Not sure I'd go quite as far as billing it as the 'real answer'. A lot does depend on the size of the domains being aggregated and how important performance is in that particular application. An extra 1/4 second on opening a form based on the query might not be the end of the world, given the ease of use and readability of the query to those who have to maintain it, especially if the query is only returning a single row. Agreed, a listing of several thousand records, where each row contains such functions IS going to perform like a sloth on valium and deserve a re-write.

I just read your article for a refresher on the functions as I rarely use them and found it very clear, concise and useful:
http://www.experts-exchange.com/Database/MS_Access/A_12-Dlookup-and-the-Domain-Functions.html

"With all of these functions you can carry out the same logic (i.e. summing a value for a given field) yourself by opening a recordset, scanning through the records, and analyzing them appropriately as you go or by executing a SQL statement (a query).  In some cases it makes sense to do this, but it is a lot of extra work in terms of coding.  The beauty of the domain functions is that they are so simple to use.  They wrap up commonly needed SQL logic in a neat easy to use function. "

I recall tuning some of the multi-user Access systems I've written or worked on and eliminating all the domain functions by re-writing code to use recordsets instead. It did make some key forms feel snappier to the users, though there were no significant gains in many cases where single-use dlookup functions were switched to recordsets.

I have always preferred correlated subqueries to domain formulae in queries, but I can see how people choose the domain functions option - they are there in the query builder list of built-in functions, whereas it doesn't offer a wizard for designing an appropriate correlated subquery.
0
 
LVL 58
ID: 40472979
Well their really bad in queries because as I said,  there is no way the query parser can optimize them.   You can get dismal performance with as few as a few thousand records.

jim
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

916 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