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

Dsum syntax

experts,

I am getting a syntax here:

=DSum("[SumEquiv]","qryLCIssuedFacAvail","[BankName] = " 1)

thank you
0
pdvsa
Asked:
pdvsa
2 Solutions
 
pdvsaAuthor Commented:
it is after the qryLCIssuedFacAvail part I suspect
0
 
Amit KhilnaneyCommented:
This will be helpful

 
http://office.microsoft.com/en-us/access-help/dsum-function-HA001228829.aspx

Open in new window


0
 
pdvsaAuthor Commented:
It does but I will need more help than that... ;)
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Mike EghtebasDatabase and Application DeveloperCommented:
=DSum("[SumEquiv]","qryLCIssuedFacAvail","[BankName] = '1'")

Most likely [BankName] is text
0
 
pdvsaAuthor Commented:
I am adding a where clause and this is where the syntax is.   I need to limit it to [BankName] = 1
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
you possibly need:

=DSum("[SumEquiv]","qryLCIssuedFacAvail","[BankName] = '" & me!txtBankNo & "'")
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
When a criteria is text you add a ' to either side:

=DSum("[SumEquiv]","qryLCIssuedFacAvail","[BankName] = '" & me!txtBankName & "'")

When a criteria is a date, you add a # to either side:

=DSum("[SumEquiv]","qryLCIssuedFacAvail","[TransactionDate] = #" & me!txtDate & "#")

When a criteria is a number:

=DSum("[SumEquiv]","qryLCIssuedFacAvail","[Amount] = " & me!txtAmount)
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
Also, when a criteria is text and it has a ' in it like (O'Brian) then add chr(34) to either side:

Asumming txtBankName has O'Brian in it,

=DSum("[SumEquiv]","qryLCIssuedFacAvail","[BankName] = " & chr(34) & me!txtBankName  & chr(3))

This will also work for when txtBankName has Brian in it.

Mike
0
 
pdvsaAuthor Commented:
OK...what if it is not text and not Me!

=DSum("[SumEquiv]","qryLCIssuedFacAvail","[BankName] = " 1)

IN this case, BankName is not text.  I have tried many different combinations but cant get it right.

0
 
Mike EghtebasDatabase and Application DeveloperCommented:
try:

=DSum("[SumEquiv]","qryLCIssuedFacAvail")

without criteria to see if it returns something. We are just testing it. We can put the criteria back later.

0
 
Mike EghtebasDatabase and Application DeveloperCommented:
Also try:

=DLookup(VarType("[BankName]"),"qryLCIssuedFacAvail")

and tell be what do you get.
0
 
Rey Obrero (Capricorn1)Commented:
if BankName field is number


=DSum("[SumEquiv]","qryLCIssuedFacAvail","[BankName] = 1")
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
also, you could upload your database for us to take a look at if you want.
0
 
NorieCommented:
How about this?

=DSum("[SumEquiv]","qryLCIssuedFacAvail","[BankName] =1")

Or this

=DSum("[SumEquiv]","qryLCIssuedFacAvail","[BankName] ='1'")
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
your original post was missing &

=DSum("[SumEquiv]","qryLCIssuedFacAvail","[BankName] = " & 1)
0
 
pdvsaAuthor Commented:
I think it was a little confusing because BankName seems like text but it actually is a number.  thank you for the help and good explanation.
0
 
pdvsaAuthor Commented:
I see that they are both the same and i tested both of them and they both worked.  

=DSum("[USDEquiv]","qryLCIssued_Sum","[BankID] = " & 1)
=DSum("[USDEquiv]","qryLCIssued_Sum","[BankID] = 1")
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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