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"))
SteveL13Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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)President / OwnerCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.