[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Dsum and Me.

Posted on 2011-10-22
13
Medium Priority
?
327 Views
Last Modified: 2013-11-27
Experts,

I have a form (db attached) where I want to sum based on the Dsum Where criteria
I get a #Name and not sure why.  

Not certain if want I am trying to do can be done but it seems quite possible to do it.  

Please take a look at pared down db.  The form opens up auto and the field is hightlighted red.\

thank you
Database1.accdb
0
Comment
Question by:pdvsa
  • 6
  • 4
  • 2
  • +1
13 Comments
 

Author Comment

by:pdvsa
ID: 37012992
it is 2007 format
0
 

Author Comment

by:pdvsa
ID: 37012997
0
 

Author Comment

by:pdvsa
ID: 37012999
use the 2nd one...
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 61

Accepted Solution

by:
mbizup earned 2000 total points
ID: 37013003
You cant use "Me." in a property sheet - just VBA.

Try this:

=DSum("[USDEquiv]","qryLCIssued_Sum","[BankID] = " & [cboBankName_Facility])
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37013004
run or open query "qryLCIssued_Sum"  and you will see that the table "tblBanks_Participating" is not included in the db you uploaded
0
 
LVL 75
ID: 37013008
Take out the Me and the Dot, just leaving the combo box name.

However, there is something wrong with qryLCIssued_Sum ... because I cannot open it in Design view ... apparently a table(s) is(are) missing ...
0
 
LVL 75
ID: 37013011
OK ... I can open the query in the 2nd db you upload.  So ... the only is removing the Me.

so it;s just  .... = "  & [cboBankName_Facility]
0
 

Author Comment

by:pdvsa
ID: 37013053
were you able to get field to return a number?

I removed the Me. and while the #Name error is solved there is no amount.
If you open the query, you can see that there is an amout in USDEquiv for each bankID (named "Bank Name"

0
 
LVL 61

Expert Comment

by:mbizup
ID: 37013060
The first record was null, but there were values in others in the second upload.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37013064
This is with the expression I posted earlier - simply dropping the "Me.":

=DSum("[USDEquiv]","qryLCIssued_Sum","[BankID] = " & [cboBankName_Facility])


Database1.accdb
0
 

Author Comment

by:pdvsa
ID: 37013084
mbizup:  I see that you posted the solution in your first post.

CAn you help with a Nz in the below:

=IIf(NZ(DSum("[USDEquiv]","qryLCIssued_Sum","[BankID] = " & [cboBankName_Facility])=0),0,(DSum("[USDEquiv]","qryLCIssued_Sum","[BankID] = " & [cboBankName_Facility])))

For that first record that is Null I would like it to say $0 instead of a blank space.  
The above formula does not populate the blank with a 0.  I think this is an easy fix.

thank you
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37013096
>>> NZ(SomeExpression, ValueIfNull)

The NZ function has a 'Value if NULL' parameter.  If you want a given value to be used in place of a NULL, use the second parameter to specify that value.  This substitutes a zero for NULL DSums.

=NZ(DSum("[USDEquiv]","qryLCIssued_Sum","[BankID] = " & [cboBankName_Facility]),0)



0
 

Author Comment

by:pdvsa
ID: 37013104
got it.  thank you
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

834 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