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?

 
TerryOhAsked:
Who is Participating?
 
JCEConnect With a Mentor Commented:
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
 
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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
 
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
 
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
All Courses

From novice to tech pro — start learning today.