• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 341
  • Last Modified:

Dsum and Me.

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
pdvsa
Asked:
pdvsa
  • 6
  • 4
  • 2
  • +1
1 Solution
 
pdvsaProject financeAuthor Commented:
it is 2007 format
0
 
pdvsaProject financeAuthor Commented:
0
 
pdvsaProject financeAuthor Commented:
use the 2nd one...
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
mbizupCommented:
You cant use "Me." in a property sheet - just VBA.

Try this:

=DSum("[USDEquiv]","qryLCIssued_Sum","[BankID] = " & [cboBankName_Facility])
0
 
Rey Obrero (Capricorn1)Commented:
run or open query "qryLCIssued_Sum"  and you will see that the table "tblBanks_Participating" is not included in the db you uploaded
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
 
pdvsaProject financeAuthor Commented:
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
 
mbizupCommented:
The first record was null, but there were values in others in the second upload.
0
 
mbizupCommented:
This is with the expression I posted earlier - simply dropping the "Me.":

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


Database1.accdb
0
 
pdvsaProject financeAuthor Commented:
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
 
mbizupCommented:
>>> 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
 
pdvsaProject financeAuthor Commented:
got it.  thank you
0
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

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