Re: Running Balance
- From: Fox <Fox@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 20 Aug 2009 06:38:01 -0700
Hi Vanderghast,
Thanks again....
The Date field has the propertys Date/Time in the table, and I've used []
around the field name date in the expression. What I am not sure about is
whether or not when the date is imported into the table is if each date for
the same date actually has a time on it...being imported from an excel file.
Would it work to create the expression with the ID field as that would be
individual for each record? I'm not really sure how this dsum works. I've
tried the ID field but I guess I do not know how to include it in the
expression properly.
this expression actually gave a correct ending total...however each line
isn't coming back with the correct calculation...below is what the expression
brings.
ID Date Activity Payments Fees Expr1
27 6/2/2009 Listing $0.00 $0.20 -4
23 6/2/2009 listing $0.00 $0.20 -4
22 6/10/2009 renew $0.00 $0.20 -5.4
21 6/10/2009 renew $0.00 $0.20 -5.4
20 6/10/2009 renew $0.00 $0.20 -5.4
19 6/10/2009 renew $0.00 $0.20 -5.4
18 6/10/2009 renew $0.00 $0.20 -5.4
17 6/10/2009 renew $0.00 $0.20 -5.4
16 6/10/2009 renew $0.00 $0.20 -5.4
28 6/2/2009 trans $0.00 $2.80 -4
24 6/2/2009 trans $0.00 $2.80 -4
26 6/2/2009 listing refund $0.20 $0.00 -4
25 6/2/2009 trans refund $2.80 $1.00 -4
15 7/5/2009 Trans $4.00 $0.00 -1.4
--
Foxy
"vanderghast" wrote:
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" <Fox@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:0A3D2566-FD05-4E1B-BF6B-7738ECA451CB@xxxxxxxxxxxxxxxx
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
"vanderghast" wrote:
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
"Fox" <Fox@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:092078D1-EDB4-4C18-9FFD-50444A1EC2DB@xxxxxxxxxxxxxxxx
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
"Duane Hookom" wrote:
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
"Fox" wrote:
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?
--
Foxy
- Follow-Ups:
- Re: Running Balance
- From: vanderghast
- Re: Running Balance
- References:
- Running Balance
- From: Fox
- RE: Running Balance
- From: Duane Hookom
- RE: Running Balance
- From: Fox
- Re: Running Balance
- From: vanderghast
- Re: Running Balance
- From: Fox
- Re: Running Balance
- From: vanderghast
- Running Balance
- Prev by Date: Need to match two field of data before updating table
- Next by Date: change data type
- Previous by thread: Re: Running Balance
- Next by thread: Re: Running Balance
- Index(es):
Relevant Pages
|
