Solved

DSum syntax in query designer

Posted on 2014-11-29
6
159 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 
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

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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…

622 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