It seems you have more that one record by date, isn't it? If so, we have
to
include ALSO the time of the transaction, in our format:
DSum("Nz(Payments,0)-Nz(Fees,0)","[Store Statements]","[Date] <= " &
Format([Date], "\#mm/dd/yyyy hh:nn:ss\#") )
where I assumed that there is a date AND TIME value in your Date field.
Right now, it seems that the first two records are for the same date
(although I don't understand why -0.2 + -0.2 results in -4. I would
have
expected -0.4) and the next seven records also of some other date, but
all
of the same date ( 7 times -0.2 = -1.4, added to the previous -4
give -5.4). In other words, you get the sum at the end of that day,
rather
than a strictly running sum, as the time in the day would indicate it.
That
is why we need to re-introduce the time part, as suggested here up.
As ar as the name of the field, since it is Date, which is a word than
can
be use to designate other thing, there is a high risk of ambiguity: does
Date refers to the field, or, say, to the function Date( ) which returns
the actual day? To AVOID such confusion, it is required to use [ ]
around
Date to SPECIFY that you mean the field, and not other thing, but it
would
have been preferable to use a different name for that field, at design,
such
as, TransactionDate, or TransacDate, not just Date (which can be too
many
things at the same time).
Vanderghast, Access MVP
Fox said:
I changed my expression as you suggested below....I do get results
however
the results are wrong as shown below:
Also Date is the field name and I'm not sure I understand the other
post
refering to the name of the date field.
Payments Fees RunSum Should be
$0.00 $0.20 -4 -20
$0.00 $0.20 -4 -40
$0.00 $0.20 -5.4 -60
$0.00 $0.20 -5.4 -80
$0.00 $0.20 -5.4 -1.00
$0.00 $0.20 -5.4 -1.20
$0.00 $0.20 -5.4 -1.40
$0.00 $0.20 -5.4 -1.60
$0.00 $0.20 -5.4 and so on
$0.00 $2.80 -4
$0.00 $2.80 -4
$0.20 $0.00 -4
$2.80 $1.00 -4
$4.00 $0.00 -1.4
--
Foxy
:
If your PC does not use US default dates string representation,
mm/dd/yyyy,
you would have to force that format instead of using the explicit
format
(to
your dates string representation) as you did, kind of something like:
DSum("Nz(Payments,0)-Nz(Fees,0)","[Store Statements]","[Date] <= " &
Format([Date], "\#mm/dd/yyyy\#") )
Vanderghast, Access MVP
Hi Duane,
Thank you so much for looking at this for me....
Yes they were but for this test I made sure that the fields were
populated
with 0.00 on the nulls because I could not get any result when I
left
the
records empty.
--
Foxy
:
Are all of the Payments and Fees values numeric or might some be
Null?
If
there is a possibility of Null, you need to use Nz([Fees],0)
Also, I would have probably had a single field for transaction
amount
with
either positive or negative numbers.
DSum("[TransAmount]","[Store Statements]","[Date] <= #" & [Date] &
"#")
--
Duane Hookom
Microsoft Access MVP
:
I have a query with the following fields,
ID, Date, Description, Activity, Payments, Fees
I am trying to get a running balance of Payments minus Fees in a
column
called runbal. I've used the following expression in the query.
I've
tried
the expression in a group query and in a simple query and it does
load
data
in the runbal expression field however the data is not correct.
I've tried both of these with and without the date part:
,"[Date]
<=
#" &
[Date] & "#")
RunSum: DSum("[Payments]-[Fees]","Store Statements","[Date] <= #"
&
[Date] &
"#")
Also tried this one
RunSum: DSum([SumofPayments],"Store
Statements")-DSum([SumofFees],"Store
Statements","[Date] <= #" & [Date] & "#")
Is there something in my expression that is just incorrect?
