Dsum() Function


I am using the following Dsum function to get the total personel time hours used by each employee. It works well with the selected year like 2010, 2011, 2012 etc.

DSum("[hoursused]","[annual vacasion]","[EMPLOYEEID]=" & [Forms]![Employee Details]![EmployeeID] & " And Year([startdate])=" & [Forms]![Employee Details]![YearSelect])

But in our office records, year starts from July 01, and eds at June 30 every year. So I need to change my second criteria to select record in between two dates (ie. like "between [yearstart] and [yearends]"). Is it possible to change the second criteria to between two dates in Dsum() function or any other input to sort this out? I have already these date fileds available in the form.

Appreciate your help

Thank you

Post your answer or comment

comments powered by Disqus
Hey guys,

Is there an easy way to put a field in a report that DSum a field from another table than the Report’s Source?

Something like

Customer_ID (from Customer table)
Customer Info (from Customer table)
Total purchases (from the Sales table)

Would the Source of the field be something using the DSum Function?
Something like:
= DSum(“Total_Amount”, “Sales”, WHERE Customer_ID = [Customer_ID]

Thanks for any assistance,


I am using the DSum function on a field that is specified as having:

Field size: Single
Format: General
Decimal Places: 2

I add 5 records to the new table. The contents of the records are:

Record 1: 1
Record 2: 2
Record 3: 3
Record 4: 4
Record 5: 5

The results of the DSum function is 15. Cool. But when I change Record 4's value to 4.1, the result is 15.0999999046326 (instead of 15.1). I get this problem any time using the DSum function on Single field types.

How can I stop this?

Kind regards, Adam.

i have a query summing sales data by month.
what i need to do is be able to list in this query the sales summed for same period last year.
I have some other criteria but this is the just of it.
trying to use a dsum function in the query itself and using product id and date range derived from fields that calc starting date last year and ending date last year for time period.

example , sum this years jan and feb 2007 sales and also have the dsum function sum jan and feb sales of 2006 in a field right next to the 07 values.

I can not get the dsum function to work???

i enen tried creating a temp table with the 07 sales data summed and values for each period in seperate fields ie date1 = 01/01/06 and date2 = 1/31/2006 and the same for feb. The dsum did not work that way either.
If i hard code the dates into the dsum it works by i would like it to be more dynamic based on the date1 and date2 values.

Hi All,

I currently have a Pareto graph created by a query, that counts occurence and then uses the Dsum function to create the cumalative %. The function is as follows:

CumPct: DSum("[CountofConcern_Number]","qry_ProbByConcern","[CountofConcern_Number]>=" & [Total Concerns] & "")/DSum("[CountofConcern_Number]","qry_ProbByConcern")

This is as per MS article: http://support.microsoft.com/kb/208373

Now this is OK and is as per the MS article but if you have counts that are the same, it just give the same cumalative % for each one that is the same. Please see attached image for an example of what this query produces.

Does anyone know how I can sort this out? TIA

How date Criteria in DSum function is applied in a query .....! any help............

I have a Number field with value as
DSum function where the criteria is
Textfield which works well.

=DSum("[MCRef]","Miracle_Cloth_Main","[Cust_ID]='" & [Cust_ID] & "'")

It doesn't show the SUM when the type of
textBox is CURRENCY

Can anyone suggest IF Ineed to change the syntax ?

I am creating a database for a sports club which has membership details and finance details.

The tables are:-

tblMembers (FullName, Address, Email, etc)
tblFinance (ID, TransactionCategory, TransactionDate, TransactionAmount, TransactionMember).

The tblFinance "TransactionMember" is linked to tblMembers "FullName".

I have 2 forms:-

When I receive membership fees from a member, I open frmTransactions and input the details (e.g., Income, 22/07/2012, £20.00, John Smith).

In frmMembers, I have created a tab for "Member Finance" which I want to show a monthly breakdown of the fees paid. Therefore, I have created 12 unbound text boxes - one for each month.

In the VBA for the form, I have an update button linked to a function to carry out the DSUM function (I have only shown January's for now):-

	JanuaryFeesPaid = DSum("[TransactionAmount]", "tblFinance", "[TransactionMember] = _
'" & Forms!frmMembers!FullName & "' and [TransactionDate] between_
 #01/01/2012# and #31/01/2012# and [TransactionCategory]='Income'")

Now, if I put in the following data in frmTransactions:-

Income | 22/01/2012 | £20.00 | John Smith
Income | 20/02/2012 | £15.00 | John Smith

The frmMembers finance tab will show £25.00 in the January fees box but then shows £35.00 in the February fees box. It is ignoring the date range and is keeping a running total.

Is there any way to have a total for each month?


I am trying to report on a payment table by payment method ie 'Cash' and 'Cheque'. In the Table Design I have a text field called 'Method' which is a Value List.

I have created a module with the following function:

	Public Function ClassCash() As Currency
Dim cClass As Currency
cClass = Nz(DSum("Price", "ClassPayment", "ToPay=No AND DateUpdated=Forms!ReportDates!txtFrom AND Method=Cash"), 0)
ClassCash = cClass
End Function

In the report I have a text box with the control source of =ClassCash().

When I open the report I get Runtime Error 2001 - You cancelled the previous operation. Funny thing is, if I remove the 'And Method=Cash' from the Function, it works fine and works out the sum of all payments on a given date.

Does anybody know how to get this function working?

I have tried changing the Table Design to just Text for the Method field removing the Value List but still get Error 2001.

Can I use a Dsum function in a control box in my report and point the Dsum to a query that is not the recordsource for the report?

I tried the following in a text box in my report:


I get #error as result... this qryQuota_Qtr is not the query in my recordsource.

Is there any other way to get that?


=Access 2K=

I have this total I'm trying to calculate in the footer of a report I'm working on and I can't get it to work right. This is what I have:

	Me.Text114 = DSum("[MLDDT]", "PCTRET", "[MAILING] = '1ST' AND [TYPE] = 'NOTICE'")

So far, I haven't gotten any error messages, but it doesn't return any totals either. Can anyone point out to me the error of my ways?

I appreciate the help. I haven't used the DSum function much so I'm not sure if I'm doing something wrong or not.

Just to tell you, I'm a complete newbie with no programming notion...I have no problem passing a parameter to generate a report but I would also like to use that same parameter inside a dsum function to be used as my criteria.

What would be the proper syntax for that dsum function ?

Thanks for your help

Please, can some one please tell me what’s wrong with my DSum function?

=DSum("[AprilVCOS]","VCOSmaster","[D_Level]'H.SCOTT'AND 'SurtiVacCalaDB'”) -- this excludes “H.Scott” but includes “SurtiVacCalaDB”

=DSum("[AprilVCOS]","VCOSmaster","[D_Level]'H.SCOTT'AND 'SurtiVacCalaDB'") –This retures “#Error”

What I want is the sum of all but 'H.SCOTT' and ‘SurtiVacCalaDB’

Always with much appreciation!

I am currently programming a report that needs to spit out all of the money paid to a specific client. The Dsum function I used was:

=DSum("CashforVisit","Financials","Financials.SSN= " & [SSN])

Which worked excellent. However, I need a way to add another criteria to the DSum function, specifically that the payments happenned in 2011. This report grabs information from table Financials, which documents every payment, the SSN of the recipient, and the date of the payment.

Values to sum: CashforVisit
Table name: Financials
Criteria1:"Financials.SSN= " & [SSN]
Criteria2: DateofVisit >= 1/1/2011
Criteria3: DateofVisit

I need help with dsum function. I have a form that the user will filter. At the footer of the form I have a textbox and a button, when the user presses the button I want to change the controlsourse of the textbox but unfortunatly it give me a error saying #Name?
Here is the code:

strLinkCriteria = Me.Filter
strLinkCriteria = Replace(strLinkCriteria, Chr(34), Chr(39))
strLinkCriteria = Replace(strLinkCriteria, "Date()", "GetDate()")
If strLinkCriteria "" Then
Text187.ControlSource = DSum("Volume", "QryCommandesDetails_LG_subform_volum", strLinkCriteria)
Text187.ControlSource = DSum("Volume", "QryCommandesDetails_LG_subform_volum")
End If

I've checked the name of the field Volume and the name of the query and they both are correct. What am I missing?

I have a basic summary report that pulls in data from a lot of tables. In order to populate this report, I am using the DLookup and DSum functions. Needless to say, this takes quite a bit of time for the report to open up, and I'm sure just hogs up system resources. Is there any alternatives to using these functions? I pull data in from at least 15-20 different queries/tables on my report. Any help would be greatly appreciated. Thanks.


I have a field in a query that uses the Dsum() function. I want the field to be formatted in currency. I have set it to be currency on the properties but it does not change it to currency. Why is that and how do I fix it?

My Dsum function is not working but seems nothing wrong with the null data.

DSum("[NumofRoom]", "RoomDetails", "[RoomId] = 'Main!Id' and [Pos] = 'D' and [Year] = 2000"))

Hey everyone,

I am trying to calculate YTD in a form using the selection made from a combobox . When I select Year, Month, and Sales Person from three comboboxes it auto populates text boxes. All this works, the problem is I cannot figure out how to get the year to date for the year that I choose from. I have been trying to use the DSum function to figure this out but it is not going to well. I have my date split up into metMonth (data is 01, 02, 03 etc) and metYear. Here is what I have for the VBA code:

Private Sub cmbsalesperson_AfterUpdate()
Me.txtwinstotal_month = Me.cmbsalesperson.Column(1)
Me.txtwins_month = Me.cmbsalesperson.Column(3)
Me.txtquotestotal_month = Me.cmbsalesperson.Column(2)
Me.txtquotes_month = Me.cmbsalesperson.Column(4)
Me.txtactualmonth = Me.cmbsalesperson.Column(5)

Dim holdmonth As String
Dim holdperson As String
Dim holdpyear As Long

holdpyear = CLng(Me.cmbYear) - 1
holdyear = CLng(Me.cmbYear)
holdmonth = Me.cmbMonth
holdperson = Me.cmbsalesperson

Me.txtwinstotalpym = DLookup("TOTALWINS", "METRICS", "SALES_PERSON_LAST_NAME='" & holdperson & "' AND clng(metMONTH)=" & holdmonth & " AND clng(metYEAR)=" & holdpyear)
Me.txtquotestotalpym = DLookup("TOTAL_QUOTES", "METRICS", "SALES_PERSON_LAST_NAME='" & holdperson & "' AND clng(metMONTH)=" & holdmonth & " AND clng(metYEAR)=" & holdpyear)
Me.txtwinspmonth = DLookup("WINSTOTALMONEY", "METRICS", "SALES_PERSON_LAST_NAME='" & holdperson & "' AND clng(metMONTH)=" & holdmonth & " AND clng(metYEAR)=" & holdpyear)
Me.txtquotespmonth = DLookup("QUOTESTOTALMONEY", "METRICS", "SALES_PERSON_LAST_NAME='" & holdperson & "' AND clng(metMONTH)=" & holdmonth & " AND clng(metYEAR)=" & holdpyear)
Me.txtplanmonth = DLookup("SALESPLAN", "SALESPLAN", "SALES_PERSON_LAST_NAME='" & holdperson & "' AND clng(SALES_YEAR)=" & holdyear)
Me.txtactualpym = DLookup("TOTAL_SALES", "[TOTAL SALES]", "EMPL_LAST_NAME='" & holdperson & "' AND clng(metMONTH)=" & holdmonth & " AND clng(metYEAR)=" & holdpyear)
Me.txtytdannual = DSum("YTDSALES", "YTD_SALES", "EMPL_LAST_NAME='" & holdperson & "' AND 'clng(metMonth) Between 01 and holdmonth' AND clng(metYear)=" & holdyear)
Me.txtplanannual = DLookup("SALESPLAN", "SALESPLAN", "SALES_PERSON_LAST_NAME='" & holdperson & "' AND clng(SALES_YEAR)=" & holdyear)

End Sub

The one in red is the only one that does not work. If snyone can help me I would be greatly appreciative.


I have searched the forums and threads trying to get a grasp on how to use quotes around variables in SQL WHERE clauses and in DSUM functions. Im having a lot of trouble trying to get this to work.
I have a table “tblOPCREW” which holds all pilot flight information. Im attempting to write a function using DSUM to enable me to calculate flight times for a pilot for different flight conditions ie Day, Night, NVG.. and between two dates based off of each pilots “flying year” which is basically birthmonth to birthmonth.
I have looked through every DSUM post and tried to figure this out on my own but have been unsuccessful. In addition I have used every combination of quotes and #’s but still does not work. Here is what I have:
DSum("[CREW_HRS]", "tblOPCREW", "[PID]='& myPID'" AND "[DATE] BETWEEN & myAPARTStart & "# AND #" & myAPARTEnd & "#")
Any help on this is greatly appreciated.

I have a Dsum function in an event on a subform that reads:

Totals = DSum("[Paid_Amt]", "tblPayments", "[Custcode] = " & Me.CUSTCODE)

The problem is that when executed, I get the following error message:

Run-Time error '2471':
'The object doesn't contain the Automation object 'ABC."

(where ABC is [custcode] for the current record on screen).

I tried several things but none seems to work.

Any ideas of what is wrong??


how do I programmatically update a calculated field with dsum in it?

the quote from ms is "Unsaved changes to records in domain aren't included when you use this function. If you want the DSum function to be based on the changed values, you must first save the changes by clicking Save Record on the Records menu, moving the focus to another record, or by using the Update method."

I can't seem to update the field from an AfterUpdate event on another control. Have I got the proper event?

Also what "Update method" are they talking about? Can't find anything on it!


I am trying to use VBA to change some the criteria in the DSUM function. As you see in the code below, a combobox value changes which field I want to use as a criteria and the value used as the criteria for that field is also in the combobox. These two values are stored as critfield and crit.

When I run the code, I get a "invalid use of null error", but I see the correct values passing through to critfield and crit. Would someone please take a look and let me know how I can fix this. Any help is much appreciated!!!

Here is my code:

Private Sub Combo191_AfterUpdate()
Dim crit As String
Dim critfield As String
Dim answer1 As Long

If Me.criteria1.value = "1" Then Me.CriteriaField.value = "region"
If Me.criteria1.value = "2" Then Me.CriteriaField.value = "Industry"
If Me.criteria1.value = "3" Then Me.CriteriaField.value = "Level 1"
If Me.criteria1.value = "4" Then Me.CriteriaField.value = "Level 2"
If Me.criteria1.value = "5" Then Me.CriteriaField.value = "Level 3"
critfield = Me.CriteriaField.value
crit = Me.Combo191.column(6)
answer1 = DSum("[sumofNet Amount - US]", "POSTSALECOSTv1", "[FML Account Code *] = 4428 and critfield = crit and [Accounting Period *]=" & [AccountingPeriod] & "")


I posted a similar question the other day on the forms forum, but no one responded, so I will try this group.

I have a form which displays financial results for our company by month. I use the DSUM function to sort data right out of our General Ledger. Right now this is a sample of the DSUM that used.

Me.CR = Val(Nz(DSum("[sumofNet Amount - US]", "SummarizedGLDATA", "[FML Account Code *] = '1111' and [" & field & "] = '" & crit & "' and [Accounting Period *]='" & [AccountingPeriod] & "'"), 0))

field and crit are fields that change dynanically based on the group that I am looking at and accountingperiod comes from a combo box on the form.

Right now, I can only sort by month and I would like to add the ability to sort by quarter as well. I have tried an IF statement like:

if me.accountingperiod = "Q1" then accountingperiod = "200801 and 200802 and 200803"
else accountingperiod = me.accountingperiod

This does not seem to work. I would appreciate any help. In fact, if you are able to help me solve this, you can say that you are smarter than the guys at the forms forum! Thanks



I need to sum values of currencies in one column (Expr2) considering to which currency I choose (Currency is written in column Expr8). I added a text box In Microsoft Access Form. I entered this formula:

=DSum("[Expr2]";"Query1";"[Expr8] = 'USD'")

However when I go to the 'Form View' window I receive an error in this field value. When I change this formula like this:


then everything is ok and Access sums all the values of all the currencies. But I need to use a criteria to set which currencies I want to sum. So does anybody know what is wrong?

Not finding an answer? Try a Google search.