Dsum function using mulitple criteria (specific: dates)

[QA_Compliance_Advisor]

I have read a few post on here however they have not helped, I personally think I am missing something while using Dsum() function. as you can see the code below, the expression and table names are correct I think I just have the incorrect statement to search between dates- AND yes I am in the UK so the date format(dd/mmm/yyyy). The result for the Dsum function below is Null.

The idea is to have a running total of income as you would see in a bank statement.

Dim GrossIncome As Double
Dim TodayDate, ToDate As Date

TodayDate = Date()
ToDate = Me.ToDate
GrossIncome = Me.GrossIncome

If IsNull(GrossIncome) Then
GrossIncome = DSum("[Amount]", "Income/Expense", "[ToDate] > #06/04/2015#" & " AND [ToDate]< #" & TodayDate & "#")
Me.GrossIncome = GrossIncome
End If

I have used this function:

DSum("[Amount]", "Income/Expense", "[ToDate] > #06/04/2015#" & " AND [ToDate]< # 05/04/2016 # ")

and it gives me the whole total between the dates. This is why I think I have the incorrect statement when dealing with the Todaydate Variable or the date() function.

GrossIncome = DSum("[Amount]", "Income/Expense", "[ToDate] >  #06/04/2015#" & " AND [ToDate]< #" & Date() & "#")

Anyone have any suggestions.

[CJ_London]

when using the # char for dates, the date must be in US date format - mm/dd/yyyy

try

"[ToDate] > #06/04/2015# AND [ToDate]< #" & format(Date(),"mm/dd/yyyy") & "#")

[IrogSinta]

If this is in a report, you can just make use of the Running Sum property.

Ron

[CJ_London]

if you want as you see in a bank statement, then the running sum as suggested by irog in a report is a good option. However in a query for use in a form, using dsum is potentially very slow, depending on the number of transactions you have

insteadt you would have something like this

SELECT *, (SELECT SUM(Amount) FROM [Income/Expense] AS T WHERE ID<= [Income/Expense].ID AND ToDate<= [Income/Expense].ToDate) AS RunningTotal
FROM  [Income/Expense]
ORDER BY ToDate, ID

Note you need an extra column that is unique (as a minimum within a date) - in this case ID - otherwise two entries on the same date would have the same runningsum

[QA_Compliance_Advisor]

thank you Ajax, the idea will be for the sum will be added to a table, the Dsum Function will be used in a form and running on a single record by record basis.

I did use your first suggestion, which work (thank you). However, I have a bit confused why your statement worked.

"[ToDate] >  #06/04/2015# AND [ToDate]< #" & format(Date(),"mm/dd/yyyy") & "#")

compared to

 "[ToDate] >  #06/04/2015#" & " AND [ToDate]< #" & format(Date(),mm/dd/yyyy) & "#")

Suppose you could give an explanation to using multiple date criteria. As most forums have suggested the way I have written the statement, would be greatly appreciated.

[June7]

Aggregate calc data should not be saved into table.

Format() function parameter strings must be enclosed in quote marks. That is the difference between the two expressions.

[CJ_London]

As June says, mm/dd/yyyy needs to be in quotes - google VBA format function to find out more

[QA_Compliance_Advisor]

I am using the query base calculation - completely correct shouldn't be using this type of calculations into tables, via, forms.

if you want as you see in a bank statement, then the running sum as suggested by irog in a report is a good option. However in a query for use in a form, using dsum is potentially very slow, depending on the number of transactions you have

insteadt you would have something like this

SELECT *, (SELECT SUM(Amount) FROM [Income/Expense] AS T WHERE  ID<= [Income/Expense].ID AND ToDate<= [Income/Expense].ToDate) AS  RunningTotal
FROM  [Income/Expense]
ORDER BY ToDate, ID

Note you need an extra column that is unique (as a minimum within a date) - in this case ID - otherwise two entries on the same date would have the same runningsum

by using this which work to a point. The last two records have the same running total even when it should be, and the amount is going up by 800 for each entry:

Should be	currently displaying
800	800
1600	1600
2400	2400
3200	3200
4000	3200

any ideas. Casue it is the first time i have seen "AS T WHERE" being used.

[CJ_London]

without knowing what your query sql actually is and seeing the relevant source data (id, date, amount), not possible to comment.

AS T is simply aliasing the table so the subquery does not get confused between the table in the subquery and the table in the main query. You could just as easily use AS A, AS temp, AS anythingyoulike.

[QA_Compliance_Advisor]

Ajax, that makes sense.

[CJ_London]

if you want to post the sql you are actually using and some example data from the tables used in the sql, I can advise further - or had you missed off the 'AS T' and putting it in solved the problem?