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

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)
0
pdvsa
Asked:
pdvsa
  • 7
  • 7
  • 2
1 Solution
 
mbizupCommented:
Try putting your query name in square brackets ...or preferably rename it without any special characters.
0
 
pdvsaAuthor 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
NorieCommented:
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
 
pdvsaAuthor 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
 
NorieCommented:
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
 
pdvsaAuthor 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
 
NorieCommented:
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
 
pdvsaAuthor 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
 
NorieCommented:
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
 
NorieCommented:
Here's a picture:

 A picture of a query.
0
 
pdvsaAuthor 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
 
NorieCommented:
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
 
pdvsaAuthor 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
 
NorieCommented:
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
 
pdvsaAuthor 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

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 7
  • 7
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now