Firstly, the date format is irrelevant. A value of date/time data type in
Access is stored as a 64 bit floating point number which is unchanged
whatever format you choose to see the dates in.
You don't need to include the # date delimiter characters if you are creating
the query in design view or SQL view, only if you are building the SQL for
the query as a string expression and wish to concatenate the values of
variables into the string as literal date/time values.
If creating the query in design view simply enter:
BETWEEN [date from] AND [date to]
in the 'criteria' row of the relevant data column. With date/time parameters,
however, its always a good idea to declare them as such. To do this select
Parameters form the Query menu in design view (or whatever the equivalent is
if using Access 2007). In the left hand column of the dialogue enter [date
from] in the first row and [date to] in the second. In the right hand column
for each row select Date/Time as the data type. Declaring the parameters
like this avoids the risk of their being misinterpreted as arithmetic
expressions if entered in a format which might be interpreted as such.
However, the fact that you are executing an update query suggests that you
are storing the running sum values in a column in a table. This would not
normally be necessary, and more importantly, would be inadvisable as it would
leave the table open to inconsistent data. Where a value can be computed
from other data it should not usually be stored, but computed on the fly in a
query. There are exceptions to this, e.g. you would not compute invoice
amounts from the unit prices of products in a products table as the price can
change, but the invoice amount must remain static as that at the time the
invoice was created, but in your case it doesn't sound like this sort of
reasoning would apply.
Here's an example of a query which returns a running balance per customer
from a table of transactions with Credit and Debit columns for a calendar
year entered as a parameter, bringing forward any balance from the previous
year:
SELECT CustomerID, TransactionDate, Credit, Debit,
(SELECT SUM(Credit)
FROM Transactions AS T2
WHERE T2.CustomerID = T1.CustomerID
AND T2.TransactionDate <= T1.TransactionDate
AND ( T2.TransactionID <= T1.TransactionID
OR T2.TransactionDate <> T1.TransactionDate)) -
(SELECT SUM(Debit)
FROM Transactions AS T2
WHERE T2.CustomerID = T1.CustomerID
AND T2.TransactionDate <= T1.TransactionDate
AND ( T2.TransactionID <= T1.TransactionID
OR T2.TransactionDate <> T1.TransactionDate)) AS Balance
FROM Transactions AS T1
WHERE YEAR(TransactionDate) = [Enter year:]
ORDER BY CustomerID, TransactionDate DESC , TransactionID DESC;
and here's one which does the same without bringing forward a balance from
the previous year, i.e. its zero-based:
SELECT CustomerID, TransactionDate, Credit, Debit,
(SELECT SUM(Credit)
FROM Transactions AS T2
WHERE T2.CustomerID = T1.CustomerID
AND T2.TransactionDate <= T1.TransactionDate
AND YEAR(TransactionDate) = [Enter year:]
AND ( T2.TransactionID <= T1.TransactionID
OR T2.TransactionDate <> T1.TransactionDate)) -
(SELECT SUM(Debit)
FROM Transactions AS T2
WHERE T2.CustomerID = T1.CustomerID
AND T2.TransactionDate <= T1.TransactionDate
AND YEAR(TransactionDate) = [Enter year:]
AND ( T2.TransactionID <= T1.TransactionID
OR T2.TransactionDate <> T1.TransactionDate)) AS Balance
FROM Transactions AS T1
WHERE YEAR(TransactionDate) = [Enter year:]
ORDER BY CustomerID, TransactionDate DESC , TransactionID DESC;
If the table has a single Amount column rather than Credit and Debit columns,
just omit the second subquery, if bringing a previous balance forward:
SELECT CustomerID, TransactionDate, Amount,
(SELECT SUM(Amount)
FROM Transactions AS T2
WHERE T2.CustomerID = T1.CustomerID
AND T2.TransactionDate <= T1.TransactionDate
AND ( T2.TransactionID <= T1.TransactionID
OR T2.TransactionDate <> T1.TransactionDate)) AS Balance
FROM Transactions AS T1
WHERE YEAR(TransactionDate) = [Enter year:]
ORDER BY CustomerID, TransactionDate DESC , TransactionID DESC;
or for a zero-based balance:
SELECT CustomerID, TransactionDate, Amount,
(SELECT SUM(Amount)
FROM Transactions AS T2
WHERE T2.CustomerID = T1.CustomerID
AND T2.TransactionDate <= T1.TransactionDate
AND YEAR(TransactionDate) = [Enter year:]
AND ( T2.TransactionID <= T1.TransactionID
OR T2.TransactionDate <> T1.TransactionDate)) AS Balance
FROM Transactions AS T1
WHERE YEAR(TransactionDate) = [Enter year:]
ORDER BY CustomerID, TransactionDate DESC , TransactionID DESC;
For an accounting year which is not the calendar year the following function
can be used:
Public Function AccountingYear(varDate As Variant, _
intStartMonth As Integer, _
intStartDay As Integer) As String
Dim intYear As Integer
If Not IsNull(varDate) Then
intYear = Year(varDate)
If intStartMonth = 1 And intStartDay = 1 Then
' accounting year is calendar year
AccountingYear = intYear
Else
' if date is before start of accounting year then
' accounting year starts in previous year
If varDate < DateSerial(intYear, intStartMonth, intStartDay) Then
AccountingYear = intYear - 1 & "-" & intYear
Else
' date is after start of accounting year so
' accounting year starts in current year
AccountingYear = intYear & "-" & intYear + 1
End If
End If
End If
End Function
and instead of using the YEAR function the above function would be called, so
for an accounting year starting 1st April you'd use:
WHERE ACCOUNTINGYEAR(TransactionDate,4,1)
= [Enter accounting year (yyyy-yyyy):]
so for the current accounting year you'd enter 2009-2110 at the parameter
prompt.
Ken Sheridan
Stafford, England
I want to use an update query to sum values of the previous twelve months. I
tried using
BETWEEN "#" & [date from] & "# AND #" & [date to] & "#"
in the criteria to limit records summed. I get a error message telling me I
did not include the and. I really would like to find a source that has the
explaination of each symbol and the parameters of the built in functions.
My dates are short date and time and always the first of the month. I have
several years of data that needs the rolling sum calculated so using a fixed
date would not work.
thanks in advance
