Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 175
  • Last Modified:

DSum syntax in query designer

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
SteveL13
Asked:
SteveL13
  • 2
  • 2
  • 2
2 Solutions
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
 
SimonCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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
 
SimonCommented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now