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

x
?
Solved

Dsum and a Condition

Posted on 2011-10-30
15
Medium Priority
?
550 Views
Last Modified: 2012-05-12
Experts,

I need to add a AND condition to the below (bold).
I get a #error.

=Nz(DSum("[SumOfApproved Amount In USD Equivalent]","qryLCIssued_Sum_Import-CSM_2","[Guarantor Description] Like '*" & Left([txtBankName2],6) & "*'") And [Actual Status]<>"GEC",0)

thank you
0
Comment
Question by:pdvsa
  • 7
  • 7
15 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 37054078

Give this a try...

=Nz(DSum("[SumOfApproved Amount In USD Equivalent]","qryLCIssued_Sum_Import-CSM_2","[Guarantor Description] Like '*" & Left([txtBankName2],6) & "*'" And [Actual Status]<>"GEC"),0)

0
 

Author Comment

by:pdvsa
ID: 37054104
Hi mbizup, I no longer get a syntax but it returns a #Name.  What do you think?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37054132
Try this, and I'll explain why if it works:


=Nz(DSum("[SumOfApproved Amount In USD Equivalent]","qryLCIssued_Sum_Import-CSM_2","[Guarantor Description] Like '*" & Left([txtBankName2],6) & "*' And [Actual Status]<> 'GEC'"),0
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:pdvsa
ID: 37054159
that gave me a #error.  :(
0
 

Author Comment

by:pdvsa
ID: 37054284
When using Dsum, it is necessary to include the same conditions that the query qryLCIssued_Sum_Import-CSM_2?   It seems as though I do.  I ask this because within the qry I already have the condition of [Actual Status]<>"GEC" but Dsum seems to ignore this condtion within the qry whihc means I have to reference the same condition in Dsum.  
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37054469
Sorry for the delay.

I had missed a closing parenthesis.

Try this:

=Nz(DSum("[SumOfApproved Amount In USD Equivalent]","qryLCIssued_Sum_Import-CSM_2","[Guarantor Description] Like '*" & Left([txtBankName2],6) & "*' And [Actual Status]<> 'GEC'"),0)

Regarding your query - if that criteria is in your query, it should work there as long as the criteria are correct and present (I would think - unless I'm missing something).

0
 

Author Comment

by:pdvsa
ID: 37054669
Mbizup:  access returned a syntax error on the missing parenthesis.  After i added it still gabe me the error.   Any other ideas?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37054703
Can you upload a sample - I'm not sure where the issue is.
0
 

Author Comment

by:pdvsa
ID: 37054801
Mbizup:  
 Maybe you cAn dl it here:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_27422502.html

it is the same db i believe.   The field is highlighted yellow.  Please copy you equatuon in that field.

Thank you
(dont have computer at moment)
0
 
LVL 44

Expert Comment

by:GRayL
ID: 37054869
=Nz(DSum("[SumOfApproved Amount In USD Equivalent]","qryLCIssued_Sum_Import-CSM_2","[Guarantor Description] Like '*" & Left([txtBankName2],6) & "*'") And [Actual Status]<>"GEC",0)


Should be:

=Nz(DSum("[SumOfApproved Amount In USD Equivalent]","qryLCIssued_Sum_Import-CSM_2","[Guarantor Description] Like '*" & Left([txtBankName2],6) & "*'"),0) And [Actual Status]<>"GEC"
0
 

Author Comment

by:pdvsa
ID: 37056243
GrayL:  I unfortunately got a #Name.  

I have attached the db.   Maybe if you can see it then it will be more clear.

Thank you
DatabaseNew.accdb
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37056348
Okay - I'm not sure what I was missing last nigt, but this works.  [Actual Status] needs to go inside the double quote and parentheses that define the DSum and its criteria.  Your original statement had it on the outside..


=Nz(DSum("[SumOfApproved Amount In USD Equivalent]","qryLCIssued_Sum_Import-CSM_2"," [Guarantor Description] Like '*" & Left([txtBankName2],6) & "*' AND [Actual Status] <> 'GIS'"),0)
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37056362
Btw, everything in your new sample is a 'GIS' record, so this will return zero for all records.
0
 
LVL 61

Accepted Solution

by:
mbizup earned 2000 total points
ID: 37056406
Sorry - we're trying to exclude GEC records, right?  That would be this:


=Nz(DSum("[SumOfApproved Amount In USD Equivalent]","qryLCIssued_Sum_Import-CSM_2"," [Guarantor Description] Like '*" & Left([txtBankName2],6) & "*' AND [Actual Status] <> 'GEC'"),0)
0
 

Author Comment

by:pdvsa
ID: 37056645
OK that was it.  thank you.
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

571 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