Solved

DSum syntax in query designer

Posted on 2014-11-29
6
152 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 250 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 250 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 57
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 57
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 Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

820 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