Dsum #error

Try putting your query name in square brackets ...or preferably rename it without any special characters.

Hi Mbizup, that unfortunatly gave me a #name (changing to square brackets)

any other ideas?

Also if your bank name is text you need quotes ...

=NZ(DSum("[Approved Amount In USD Equivalent]","[qryLCIssued_Sum_Import-CSM_2]","[Guarantor Description] ='" & [txtBankName2] & "'"),0)

Is [Guarantor Description] a text field?

If it is you need to enclose the criteria with 's.

So if the criteria is [txtBankName2]:

=NZ(DSum("[Approved Amount In USD Equivalent]","qryLCIssued_Sum_Import-CSM_2","[Guarantor Description] = '" & [txtBankName2] & "'"),0)

Though I don't understand where the 2 comes from if the control name is txtBankName.

Imnorie:  I actually did think it could be something liike that but that was not it.  

I have attached the pared down db in 2007  format.  the form opens auto.  YOu can see the #error on the yellow highlighted field.

Let me know where I am wrong.  
Database1-1.accdb

I tried to open the query you were using in the DSUM but it wouldn't let me saying it could find a table called Import-CSM.

If I rename the table called Import-CSM rpt to Import-CSM I can open the query.

However there is no field called [Guarantor Description] in the query for the DSum to use in the criteria.

Perhaps more importantly there is no field called [Approved Amount In USD Equivalent] to actually sum.

There is a field in the query called [SumOfApproved Amount In USD Equivalent] and if I use that in the DSum and remove the criteria there is result, but I'm pretty sure that isn't what you are looking for.

Imnorie:  Sorry about that.  The table name should have been changed like you did it.  

Hohwever, I do see the [Guarantor Description] and [Approved Amount In USD Equivalent] in the qry?  

I  have reattached the db.  It must be a simple solution...I am overlooking something.  I think I see those fields in the qry.
DatabaseNew.accdb

imnorie:  I really am lost here.  I do not see an Expr1 in the query.  
I atttached a screenprint of what I see in "qryLCIssued_Sum_Import-CSM_2"


untitled.JPG

Believe me, in the original database it was Expr1.

In fact it was Expr1:[Import-CSM].[Guarantor Description]

After I renamed the table it became this:

Expr1:[Guarantor Description]

This probably happened because of the order I did things in.

Anyway, did you try the last DSum expression I posted?

It works in both databases.

Here's a picture:

 

I have not tried the expression yet.  My internet seems to be down at the moment.  I feel confident that the expresaion will work.   Have a good night And sorry about the confusion

No problem.

I usually test expressions in a query before trying them on a form - slightly easier to debug and/or try alternative versions.

Imnorie:  that Dsum worked!  That is crazy complicated looking though.  I know it is because of txtBankName2 is TEXT.  

<It returns $0.00 for all the records though probably because Banks in the records  aren't returned by the query.
==>I will need to have a"Like" in that statement and I will post another question if I cant figure it out.  

thank you
untitled.JPG

If you mean change the criteria to Like that shouldn't be a problem, but what would you want the Description to be 'like'?

Or is that not what you mean?

Is the Like part going to be added to the criteria?

Mbizup basically had the solution but a field was named incorrrectly.

("[Approved Amount In USD Equivalent]","[
shb
("[SumOfApproved Amount In USD Equivalent]","[
=NZ(DSum("[Approved Amount In USD Equivalent]","[qryLCIssued_Sum_Import-CSM_2]","[Guarantor Description] ='" & [txtBankName2] & "'"),0)


=Nz(DSum("[SumOfApproved Amount In USD Equivalent]","qryLCIssued_Sum_Import-CSM_2","[Guarantor Description] = '" & [txtBankName2] & "'"),0)