Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 649
  • Last Modified:

Dsum problem

I'm creating a membership database and have problem about Dsum function. Well, i'm in the client form, but wanna know how much each client's paid so far. When i looked at the Sample Db from MSAccess ( Named Membership), i can use Dsum to get the sum from another table. This is the code from the sample
"=IIf(IsNull([MemberID]),0,DSum("[PaymentAmount]","Payments","[MemberID]=" & [MemberID]))"

So, i copied the code and paste it to my form, and changed from the [MemberID] to be [CaseID]. I also have "Payment" table, and "paymentAmount" field, The relationship is one to many. Unfortunately, i got the #error. Do u have any idea?

 
0
TerryOh
Asked:
TerryOh
  • 3
  • 2
  • 2
  • +1
1 Solution
 
bknouseCommented:
Try changing the name attribute of the control on the form (you find this in the properties OTHER tab).  If you originally had this named, Access gets confused and displays the #error instead of the value.

Brent
0
 
JCECommented:
If MemberId is a integer then I think your code should be like this.
=IIf(IsNull([MemberID]),0,DSum("[PaymentAmount]","Payments","[MemberID]=" & [MemberID] & ""))

If MemberId is text then it should be like this.
=IIf(IsNull([MemberID]),0,DSum("[PaymentAmount]","Payments","[MemberID]='" & [MemberID] & "'"))
Good luck
JCE
0
 
TerryOhAuthor Commented:
JCE, My MemberID is Texe, but when I tried the code that u told me, i got Blank field instead of #error. Why?
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
paaskyCommented:
Hello TerryOh,

You should add quotes or double quotes to criteria if you're comparing string (text) type fields:

=IIf(IsNull([MemberID]),0,DSum("[PaymentAmount]","Payments","[MemberID]='" & [MemberID] & "'"))

or

=IIf(IsNull([MemberID]),0,DSum("[PaymentAmount]","Payments","[MemberID]=" & Chr(34) & [MemberID] & Chr(34)))

chr(34) = "

Hope this helps,
Paasky

0
 
JCECommented:
Try this first
=DSum("[PaymentAmount]","Payments","[MemberID]='" & [MemberID] & "'")
and next
=DSum("[PaymentAmount]","Payments","[MemberID]='abc'")
abc is your MemberID.
Let me know if it works.
JCE

0
 
paaskyCommented:
Oh oh! Sorry JCE, my intention was not to step on your toes.

Paasky
0
 
paaskyCommented:
Hmm.. I think the Iif is not necessary needed here, try this instead:

=DSum("[PaymentAmount]","Payments","[MemberID]='" & NZ([MemberID],"####") & "'")

Paasky



0
 
TerryOhAuthor Commented:
Hey, JCE, it works!!!!!

Thank you!!

JCE, paasky, and bknouse
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.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

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