Dsum and a Condition

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)

Hi mbizup, I no longer get a syntax but it returns a #Name.  What do you think?

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

that gave me a #error.  :(

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.  

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).

Mbizup:  access returned a syntax error on the missing parenthesis.  After i added it still gabe me the error.   Any other ideas?

Can you upload a sample - I'm not sure where the issue is.

Mbizup:  
 Maybe you cAn dl it here:
https://www.experts-exchange.com/questions/27422502/Dsum-and-Like-with-Len.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)

=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"

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

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)

Btw, everything in your new sample is a 'GIS' record, so this will return zero for all records.

OK that was it.  thank you.