Dsum with multiple criteria (1 Viewer)

jeran042 · Aug 9, 2017

My question has a little bit of a twist from the information (which is extensive) I find in my search.

I want to do a dsum in an unbound text box on a form, I need it to look at a query and sum the "Debit" field based on 2 criteria,

Criteria 1:account number in question
Criteria 2: Paid in the previous month, (Based on a formula)

Here is the formula I have tried so far:

Code:

DSum("[DEBIT]"," qryExpense_Report ","ACCOUNT_NUMBER = '"  5040823 & " ' And Month = 'Format$(DateAdd("m",-1,Now()),"mmmm"'")

The "Month" field looks like this in the query:

Code:

Month: Format([MO_DAY],"mmmm")

Any help would be greatly appreciated,

Minty · Aug 9, 2017

Month is a reserved word in access, you'll need to put it brackets [Month}

jeran042 · Aug 9, 2017

Even with the brackets, I still get an error,
Is there a more efficient way to get this answer? For example taking the left 2 characters from the date field? But then wouldn't there have to be an array to say "01"=January...

I thought it may be easier to have an expression where it gives the name of the month, but I may be wrong?

Minty · Aug 9, 2017

What type of field is is MO_DAY ? A date?

jeran042 · Aug 9, 2017

Its only a 4 digit date field (01/28), as everything relates to the current year. Nothing I can do about this format

plog · Aug 9, 2017

Code:

,"ACCOUNT_NUMBER = '"  5040823 & " ' And Month = 'Format$(DateAdd("m",-1,Now()),"mmmm"'")

Month is a reserved word, but you have bigger issues. You are not building a valid string for your criteria.

A string can be made of 2 things--code and hard-coded text. You can use as many of those 2 things as you want, but when you want to splice them together you need to user an ampersand (&).

variableOne ="This Is hardcoded text."
variableTwo = " So is this."

That hardcoded text is now in variables which is code. I can combine them like so with a space between them by using ampersands:

variableThree= variableOne & " " & variableTwo

I can even use them together like so:

variableFour= variableOne & " And here's even more hard coded text. " & variableTwo

variableFive = variableOne & " And Today's date is:" & Date()

You need to break in and out of hardcoded text and code correctly in building that filter string. Can the value of [Month] literally be the string ''Format$(DateAdd("m",-1,Now()),"mmmm"'? Seems like an odd value for the value of a month.

arnelgp · Aug 9, 2017

Your query is right athough some space and wxtra bracjet there.

Code:

DSum("DEBIT","qryExpense_Report","ACCOUNT_NUMBER = '"  & 5040823 & "' And Month = 'Format$(DateAdd("m",-1,Now()),"mmmm"'")

arnelgp · Aug 9, 2017

Your query is right athough some space and wxtra bracjet there.

Code:

DSum("DEBIT","qryExpense_Report","ACCOUNT_NUMBER = '"  & 5040823 & "' And Month = Format$(DateAdd(" & chr(34) & "m" & chr(34) & ",-1,Now())," & chr(34) & "mmmm" & chr(34))

jeran042 · Aug 9, 2017

plog said:
Code:

,"ACCOUNT_NUMBER = '" 5040823 & " ' And Month = 'Format$(DateAdd("m",-1,Now()),"mmmm"'")

Month is a reserved word, but you have bigger issues. You are not building a valid string for your criteria.

A string can be made of 2 things--code and hard-coded text. You can use as many of those 2 things as you want, but when you want to splice them together you need to user an ampersand (&).

variableOne ="This Is hardcoded text."
variableTwo = " So is this."

That hardcoded text is now in variables which is code. I can combine them like so with a space between them by using ampersands:

variableThree= variableOne & " " & variableTwo

I can even use them together like so:

variableFour= variableOne & " And here's even more hard coded text. " & variableTwo

variableFive = variableOne & " And Today's date is:" & Date()

You need to break in and out of hardcoded text and code correctly in building that filter string. Can the value of [Month] literally be the string ''Format$(DateAdd("m",-1,Now()),"mmmm"'? Seems like an odd value for the value of a month.

This is very good background, and I believe I even had it written this way at one point but was still (and currently) getting the #error. I think this gets back to my original question, is there a more efficient way to say sum up "Debits" for Account Number X for the month of X on a form?

jeran042 · Aug 9, 2017

arnelgp said:
Your query is right athough some space and wxtra bracjet there.

Code:

DSum("DEBIT","qryExpense_Report","ACCOUNT_NUMBER = '" & 5040823 & "' And Month = Format$(DateAdd(" & chr(34) & "m" & chr(34) & ",-1,Now())," & chr(34) & "mmmm" & chr(34))

Thank you very much for your response, however this still goes not give me a result, only a #error?

Also, I never remember to use the "chr(34)" instead of the double quotes, it is much easier to read, I will make this a standard going forward!

arnelgp · Aug 9, 2017

It seems to me your getting the error because of those calculated columns on your first query.

My advise is to do your DSum() against the table/s.

plog · Aug 9, 2017

one point but was still (and currently) getting the #error.

Can you post your current DSUM?

jeran042 · Aug 9, 2017

arnelgp said:
It seems to me your getting the error because of those calculated columns on your first query.

My advise is to do your DSum() against the table/s.

My current formula is:

Code:

=DSum("DEBIT","qryExpense_Report","ACCOUNT_NUMBER = '" & 5040823 & "' And Month = Format$(DateAdd(" & Chr(34) & "m" & Chr(34) & ",-1,Now())," & Chr(34) & "mmmm" & Chr(34))

And I cant run it against the table, as I am trying to pull a variable from a calculated field,

plog · Aug 9, 2017

1. 'Month' is a reserved word. When you use reserved words it makes coding more difficult. You need to put brackets around it in your filter string.

2. If you used numbers for your months instead of words, this would get easier. I would rework the [Month] field of your query to use the Month() function instead. Also, give it a new name while you are at it.

3. You still have not used ampersands correctly. You need to identify which part of the criteria string is hard coded and which is actual code and you need to use ampersands appropriately.

arnelgp · Aug 9, 2017

Last resort is to create a function in a module where you can sum the debits of a particular account

Public Function fnActDebit(Byval act as String, byval sQuery As string) As Double
Dim rs As Dao.RecordSet
Dim ret as double
Dim sMonth as string
Smonth=format(dateadd("m",-1,Date),"mmmm")
Set rs=DbEngine(0)(0).OpenRecordset("select [debit] from " & sQuery & " where account_number=" CHR(34) & act & Chr(34))
With rs
If not (.bof and .eof) then .movefirst
While not .eof
If !month=smonth then ret=ret+!debit
.movenext
Wend
.close
End with
Set rs=nothing
fnActDebit=ret
End Function

So yiur unbound textbox rowsource:
=fnActDebit([acctno_textvox],"qryExpense_Report")

Also on the Current event of your form you should requery the unbound textbox:
Unboundtextbox.requery

arnelgp · Aug 9, 2017

Actually you can use the table and need to recreate the calculated column(Month) and directly compare it to another expression (which is last month)

Dsum with multiple criteria (1 Viewer)

jeran042

Registered User.

Minty

AWF VIP

jeran042

Registered User.

Minty

AWF VIP

jeran042

Registered User.

plog

Banishment Pending

arnelgp

..forever waiting... waiting for jellybean!

arnelgp

..forever waiting... waiting for jellybean!

jeran042

Registered User.

jeran042

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

plog

Banishment Pending

jeran042

Registered User.

plog

Banishment Pending

arnelgp

..forever waiting... waiting for jellybean!

arnelgp

..forever waiting... waiting for jellybean!

Similar threads

Users who are viewing this thread