Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

DSum not working correctly

Posted on 2011-03-11
9
Medium Priority
?
337 Views
Last Modified: 2013-11-05
I am using DSum as a default value on a form but am not getting the correct result.  Probably because I have " and ' marks wrong.  Can someone help?  Here my code:

=DSum("Qty","tblProdInventory","'Style = #10 Product' & 'CompanyName = frmProjectDetail!txtCompany'")

--Steve
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
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 85
ID: 35111836
=DSum("Qty","tblProdInventory","Style = '#10 Product' AND CompanyName ='" &  frmProjectDetail!txtCompany & "'")
0
 
LVL 85
ID: 35111850
Sorry, posted too quickly:

Also, understand that a Default value ONLY impacts New records. It will have absolutely no impact on Existing records.

And, in your case, if there is no value in txtCompany, or if txtCompany is on the same Form where you're running this, then you will almost certainly run into issues with it.
0
 

Author Comment

by:SteveL13
ID: 35111880
I pasted this in the default value and am getting a #Name? result.   ??
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 35111893
Try this one

=DSum("[Qty]","tblProdInventory","[Style] = '#10 Product' And  [CompanyName] =' " & Forms!frmProjectDetail!txtCompany & "'")

Sincerely,
Ed
0
 

Author Comment

by:SteveL13
ID: 35111938
Tried that one and now I get no result in the field.
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 35111992
No leading space:

=DSum("Qty","tblProdInventory","Style = '#10 Product' And CompanyName = '" & frmProjectDetail!txtCompany & "'")

/gustav
0
 
LVL 10

Expert Comment

by:conagraman
ID: 35112152
i = DSum("Qty", "tblProdInventory", "Style = '#10 Product' And CompanyName = 'frmProjectDetail!txtCompany'")
0
 
LVL 10

Expert Comment

by:conagraman
ID: 35112176
cactus_data:

my bad. i didnt refresh before i saw your comment
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 35112361
<Tried that one and now I get no result in the field.>

Are you absolutely sure that your DSum will return a value? That is, are you sure this is at least one positive value in the Qty field for the Criteria you've entered? That is, there is at least one value in that field where your Style is "#10 Product" and the CompanyName is the value in Forms("frmProjectDetail").txtCompany?

Does the CompanyName contain any odd names, like O'Brian? If so, you'll need to "escape" those:

=DSum("Qty","tblProdInventory","Style = '#10 Product' AND CompanyName ='" &  Replace(Forms("frmProjectDetail").txtCompany, "'", "''") & "'")
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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 earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

670 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