By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,121 Members | 1,782 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,121 IT Pros & Developers. It's quick & easy.

DSum with multiple criteria

P: 8
I am using a simple DSum function to add donations made by each member of an organization. It looks like this:

dTotal = DSum("SplitAmt", "[GiftSplits]", "[Gift ID] = " & Format(lGiftID))

SplitAmt is the field that contains the amount of the donation. GiftSplits is the name of the table. Gift ID is the field that contains the ID number given to each donor.

In the GiftSplits table is another field called Restrictions. Some records in this field contain a value of "OutsideIN" meaning that the gift was restricted so that the money has to be used outside the state of Indiana. I want to adapt this so that it adds all gifts expect those that were restricted for use outside Indiana.

For example:
Joe donor made donations of $100; $500; and $250 that are restricted for use in Indiana and one donation of $1,000 for use outside Indiana. I want dTotal to queal $850.

I know this should probably be simple, but this is my first exposure to VB and I've tried everything I can think of. Thanks in advance for your help.
Dec 28 '07 #1
Share this Question
Share on Google+
13 Replies


jaxjagfan
Expert 100+
P: 254
I am using a simple DSum function to add donations made by each member of an organization. It looks like this:

dTotal = DSum("SplitAmt", "[GiftSplits]", "[Gift ID] = " & Format(lGiftID))

SplitAmt is the field that contains the amount of the donation. GiftSplits is the name of the table. Gift ID is the field that contains the ID number given to each donor.

In the GiftSplits table is another field called Restrictions. Some records in this field contain a value of "OutsideIN" meaning that the gift was restricted so that the money has to be used outside the state of Indiana. I want to adapt this so that it adds all gifts expect those that were restricted for use outside Indiana.

For example:
Joe donor made donations of $100; $500; and $250 that are restricted for use in Indiana and one donation of $1,000 for use outside Indiana. I want dTotal to queal $850.

I know this should probably be simple, but this is my first exposure to VB and I've tried everything I can think of. Thanks in advance for your help.
Try this:

dTotal = DSum("SplitAmt", "[GiftSplits]", "[Restrictions] <> 'OutsideIN' AND [Gift ID] = " & Format(lGiftID))
Dec 28 '07 #2

P: 8
Try this:

dTotal = DSum("SplitAmt", "[GiftSplits]", "[Restrictions] <> 'OutsideIN' AND [Gift ID] = " & Format(lGiftID))
Thanks! I tried that (but with the [Gift ID] = " &Format(IGigftID) command before the [Restrictions] <> 'OutsideIN' command) and it didn't work. Does transposing them matter, or is it more likely that I screwed something else up when trying it previously?

Also, the code you provided works when there are multiple gifts from one GiftID, but when the only record is for an OutsideIN restriction it gives me this error message:

Run-time Error '94':
Invalid use of Null

I will search for an answer to this, but if you can answer it, that would make my day!
Dec 28 '07 #3

jaxjagfan
Expert 100+
P: 254
Thanks! I tried that (but with the [Gift ID] = " &Format(IGigftID) command before the [Restrictions] <> 'OutsideIN' command) and it didn't work. Does transposing them matter, or is it more likely that I screwed something else up when trying it previously?

Also, the code you provided works when there are multiple gifts from one GiftID, but when the only record is for an OutsideIN restriction it gives me this error message:

Run-time Error '94':
Invalid use of Null

I will search for an answer to this, but if you can answer it, that would make my day!
You have to be careful on the way you concatenate the lGiftID with the rest of the WHERE clause but the order doesn't matter. Since the 'OutsideIN' is a hardcoded value I personally find it easier to put it first.

Not sure how you are using this. In a form control, query or code but ...

dTotal = iif(DSum("SplitAmt", "[GiftSplits]", "[Restrictions] <> 'OutsideIN' AND [Gift ID] = " & Format(lGiftID)) = Null, 0, dTotal = DSum("SplitAmt", "[GiftSplits]", "[Restrictions] <> 'OutsideIN' AND [Gift ID] = " & Format(lGiftID)))
Dec 28 '07 #4

P: 8
You have to be careful on the way you concatenate the lGiftID with the rest of the WHERE clause but the order doesn't matter. Since the 'OutsideIN' is a hardcoded value I personally find it easier to put it first.

Not sure how you are using this. In a form control, query or code but ...

dTotal = iif(DSum("SplitAmt", "[GiftSplits]", "[Restrictions] <> 'OutsideIN' AND [Gift ID] = " & Format(lGiftID)) = Null, 0, dTotal = DSum("SplitAmt", "[GiftSplits]", "[Restrictions] <> 'OutsideIN' AND [Gift ID] = " & Format(lGiftID)))
Run-time Error '2001':
You cancelled the previous operation.
Dec 28 '07 #5

jaxjagfan
Expert 100+
P: 254
Run-time Error '2001':
You cancelled the previous operation.
Are you running this in code and returning the results? How are you using this?
Dec 28 '07 #6

P: 8
Run-time Error '2001':
You cancelled the previous operation.
cancel what I just said about Run-time Error '2001'.

Thanks for your help!
Dec 28 '07 #7

P: 8
Are you running this in code and returning the results? How are you using this?
Forgive my ignorance in this. I'm not a programmer, just an Access user trying to get someone elses program to work.

I have a form that contains a button that runs this operation and updates a "Total Gift" field both on the form and in a different table. I right-click on the button and open the properties window. When I scroll down the window, I can zoom in on where it says [Event Procedure] in the "On Click" field.

I hope that makes sense.
Dec 28 '07 #8

jaxjagfan
Expert 100+
P: 254
Forgive my ignorance in this. I'm not a programmer, just an Access user trying to get someone elses program to work.

I have a form that contains a button that runs this operation and updates a "Total Gift" field both on the form and in a different table. I right-click on the button and open the properties window. When I scroll down the window, I can zoom in on where it says [Event Procedure] in the "On Click" field.

I hope that makes sense.
I know the feeling very well - making other peoples' stuff work. And I'm not talking about this forum either.

Since it appears to be running from code that runs when you click a button:

If DSum("SplitAmt", "[GiftSplits]", "[Restrictions] <> 'OutsideIN' AND [Gift ID] = " & Format(lGiftID)) = Null Then
dTotal = 0
Else
dTotal = DSum("SplitAmt", "[GiftSplits]", "[Restrictions] <> 'OutsideIN' AND [Gift ID] = " & Format(lGiftID)))
End IF

If this isn't working - paste the code here and let us have a look.
Dec 28 '07 #9

P: 8
I know the feeling very well - making other peoples' stuff work. And I'm not talking about this forum either.

Since it appears to be running from code that runs when you click a button:

If DSum("SplitAmt", "[GiftSplits]", "[Restrictions] <> 'OutsideIN' AND [Gift ID] = " & Format(lGiftID)) = Null Then
dTotal = 0
Else
dTotal = DSum("SplitAmt", "[GiftSplits]", "[Restrictions] <> 'OutsideIN' AND [Gift ID] = " & Format(lGiftID)))
End IF

If this isn't working - paste the code here and let us have a look.
Below is the full code I am using. Please note that my criteria have changed. Instead of not counting "OutsideIN" gifts from the [Restrictions] field, I now wish to exclude items for which a proposal has been sent (value: 'Proposal Sent', field: [Gift Status 1]). This coding results in an "invalid use of null" error when all of the entries for a particular Gift ID have a 'Proposal Sent' value. However, when there is at least one entry for that particular Gift ID with a Gift Status value other than 'Proposal Sent' the coding works like a charm.


Option Compare Database

Private Sub cmdRecalcTotal_Click()
Dim dTotal As Currency
Dim lGiftID As Long
Dim S As String

lGiftID = Me.[Gift ID]
'lGiftID = Me.Gift_ID

If DSum("SplitAmt", "[GiftSplits]", "[Gift Status 1] <> 'Proposal Sent' AND [Gift ID] = " & Format(lGiftID)) = Null Then
dTotal = 0
Else
dTotal = DSum("SplitAmt", "[GiftSplits]", "[Gift Status 1] <> 'Proposal Sent' AND [Gift ID] = " & Format(lGiftID))
End If
'On Error Resume Next
Me.Text57 = dTotal
's = "SELECT SUM([SplitAmt"

End Sub


Private Sub Form_Open(Cancel As Integer)
Dim sSQL As String
Dim S As String

On Error Resume Next
S = Application.Forms("SwitchBoard").Form.CampaignID
On Error GoTo 0
If S <> "" Then
Me.Filter = "CampaignID=" & S
Me.FilterOn = True

End If

End Sub
Jan 2 '08 #10

jaxjagfan
Expert 100+
P: 254
Add dTotal = 0 just before my IF statement. I think where you are getting the null value error is in the portion of the code that sets Me.Text57 = dTotal. If at that point no value has been set for dTotal then a NULL is assumed. Presetting it to 0 should resolve error.

That portion of code should look like:


'lGiftID = Me.Gift_ID

dTotal = 0

If DSum("SplitAmt", "[GiftSplits]", "[Gift Status 1] <> 'Proposal Sent' AND [Gift

Below is the full code I am using. Please note that my criteria have changed. Instead of not counting "OutsideIN" gifts from the [Restrictions] field, I now wish to exclude items for which a proposal has been sent (value: 'Proposal Sent', field: [Gift Status 1]). This coding results in an "invalid use of null" error when all of the entries for a particular Gift ID have a 'Proposal Sent' value. However, when there is at least one entry for that particular Gift ID with a Gift Status value other than 'Proposal Sent' the coding works like a charm.


Option Compare Database

Private Sub cmdRecalcTotal_Click()
Dim dTotal As Currency
Dim lGiftID As Long
Dim S As String

lGiftID = Me.[Gift ID]
'lGiftID = Me.Gift_ID

If DSum("SplitAmt", "[GiftSplits]", "[Gift Status 1] <> 'Proposal Sent' AND [Gift ID] = " & Format(lGiftID)) = Null Then
dTotal = 0
Else
dTotal = DSum("SplitAmt", "[GiftSplits]", "[Gift Status 1] <> 'Proposal Sent' AND [Gift ID] = " & Format(lGiftID))
End If
'On Error Resume Next
Me.Text57 = dTotal
's = "SELECT SUM([SplitAmt"

End Sub


Private Sub Form_Open(Cancel As Integer)
Dim sSQL As String
Dim S As String

On Error Resume Next
S = Application.Forms("SwitchBoard").Form.CampaignID
On Error GoTo 0
If S <> "" Then
Me.Filter = "CampaignID=" & S
Me.FilterOn = True

End If

End Sub
Jan 2 '08 #11

P: 8
Add dTotal = 0 just before my IF statement. I think where you are getting the null value error is in the portion of the code that sets Me.Text57 = dTotal. If at that point no value has been set for dTotal then a NULL is assumed. Presetting it to 0 should resolve error.

That portion of code should look like:


'lGiftID = Me.Gift_ID

dTotal = 0

If DSum("SplitAmt", "[GiftSplits]", "[Gift Status 1] <> 'Proposal Sent' AND [Gift
I'm getting the same error. When I hit "debug" it goes to the VB script and highlights this line of the code:

dTotal = DSum("SplitAmt", "[GiftSplits]", "[Gift Status 1] <> 'Proposal Sent' AND [Gift ID] = " & Format(lGiftID))
Jan 2 '08 #12

P: 8
I'm getting the same error. When I hit "debug" it goes to the VB script and highlights this line of the code:

dTotal = DSum("SplitAmt", "[GiftSplits]", "[Gift Status 1] <> 'Proposal Sent' AND [Gift ID] = " & Format(lGiftID))
I found something that having a null value in a string field will cause an invalid use of null. It suggested changing the code this way:


dTotal = "" & DSum("SplitAmt", "[GiftSplits]", "[Gift Status 1] <> 'Proposal Sent' AND [Gift ID] = " & Format(lGiftID))

I'm not sure if adding the "" & fixed the invalid use of null issue, but it is giving me a Type Mismatch error. I tried this with all variations mentioned above.
Jan 4 '08 #13

jaxjagfan
Expert 100+
P: 254
I found something that having a null value in a string field will cause an invalid use of null. It suggested changing the code this way:


dTotal = "" & DSum("SplitAmt", "[GiftSplits]", "[Gift Status 1] <> 'Proposal Sent' AND [Gift ID] = " & Format(lGiftID))

I'm not sure if adding the "" & fixed the invalid use of null issue, but it is giving me a Type Mismatch error. I tried this with all variations mentioned above.
dTotal is currency data type and you are trying to set its value to a string using those changes.
Jan 4 '08 #14

Post your reply

Sign in to post your reply or Sign up for a free account.