Dsum #error

Experts,
I have a #error in the below.  FWIW In [txtBankName2] I have this: =[cboBankName_FAcility].[column](1) and it is a txt field with control name of txtBankName.  I do have a similar Dsum and it works but in the one below I ahve changed the name of the qry.  [Approved Amount In USD Equivalent] is currency.

=NZ(DSum("[Approved Amount In USD Equivalent]","qryLCIssued_Sum_Import-CSM_2","[Guarantor Description] = " & [txtBankName2]),0)
pdvsaProject financeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
mbizupCommented:
Try putting your query name in square brackets ...or preferably rename it without any special characters.
0
 
pdvsaProject financeAuthor Commented:
Hi Mbizup, that unfortunatly gave me a #name (changing to square brackets)

any other ideas?
0
 
mbizupCommented:
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)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
NorieVBA ExpertCommented:
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.
0
 
pdvsaProject financeAuthor Commented:
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
0
 
NorieVBA ExpertCommented:
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.
0
 
pdvsaProject financeAuthor Commented:
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
0
 
NorieVBA ExpertCommented:
There is a field called Expr1 in the query, with the expression  [Guarantor Description].

If you remove the Expr1 then the DSUM works with the criteria.

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

It returns $0.00 for all the records though probably because Banks in the records  aren't returned by the query.
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
pdvsaProject financeAuthor Commented:
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
0
 
NorieVBA ExpertCommented:
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.
0
 
NorieVBA ExpertCommented:
Here's a picture:

 A picture of a query.
0
 
pdvsaProject financeAuthor Commented:
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
0
 
NorieVBA ExpertCommented:
No problem.

I usually test expressions in a query before trying them on a form - slightly easier to debug and/or try alternative versions.
0
 
pdvsaProject financeAuthor Commented:
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
0
 
NorieVBA ExpertCommented:
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?
0
 
pdvsaProject financeAuthor Commented:
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)
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.

All Courses

From novice to tech pro — start learning today.