Running Balance


F

Fox

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?
 
Ad

Advertisements

D

Duane Hookom

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] & "#")
 
F

Fox

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 said:
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 said:
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?
 
V

vanderghast

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 said:
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 said:
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 said:
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?
 
F

Fox

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 said:
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 said:
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 said:
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?
 
F

Fox

Yes I would have a single field as well...however the data is downloaded from
a financial site and rather having to reformat the data to import the info
each time I'd like to be able to do this on the data as it is if
possible......
--
Foxy


Duane Hookom said:
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 said:
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?
 
Ad

Advertisements

V

vanderghast

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


vanderghast said:
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 said:
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?
 
F

Fox

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 said:
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


vanderghast said:
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?
 
V

vanderghast

Indeed, without a real time part in the date field, the result you got, for
each record, is the cumulative running sum 'at the end of that day'. Note
that the data you supplied is NOT ordered by date since a bloc of records
dated the 6/10/2009 in inserted between two blocs dated 6/2/2009.

If there is really no time part in your date and time values, can you
,arbitrairy, add one, such as (after you made a backup just to be on the
safe side, and/or working on a copy of the real data), update the date field
like this:

UPDATE tableName
SET [date] = [date] + id / (1+DMax("id" , "tablename"))


That should give an arbitrairy time value to each record. You can then try
the running sum query on THAT table with MODIFIED date getting an arbitrary
TIME value (increasing as the ID increases). Be sure to have your data
ORDER BY [Date] when you check the running sum, which can ALSO be done by
'sorting' the data view (and it is probably easier).



Vanderghast, Access MVP



Fox said:
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 said:
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?
 
F

Fox

Thanks again vanderghast you've been so helpful!!!

No, there is no time, the excel spreadsheet is downloaded from a sight a
bit like ebay. When I downloaded the file and looked at the date field the
time on all dates is 0:00. I had a feeling it would be a problem....
hm....I could do this but to get others to do this before import would be a
bit much to ask...
So...the dsum feature has to have a date to calculate a running balance. I
was hoping that maybe the id field without the date field would be enough.

Well, I think I'll scrap this idea. They'll just have to get the old
calculator out to get the total or maybe I'll create a report and just
calculate a total....

Thanks so much for your help!




--
Foxy


vanderghast said:
Indeed, without a real time part in the date field, the result you got, for
each record, is the cumulative running sum 'at the end of that day'. Note
that the data you supplied is NOT ordered by date since a bloc of records
dated the 6/10/2009 in inserted between two blocs dated 6/2/2009.

If there is really no time part in your date and time values, can you
,arbitrairy, add one, such as (after you made a backup just to be on the
safe side, and/or working on a copy of the real data), update the date field
like this:

UPDATE tableName
SET [date] = [date] + id / (1+DMax("id" , "tablename"))


That should give an arbitrairy time value to each record. You can then try
the running sum query on THAT table with MODIFIED date getting an arbitrary
TIME value (increasing as the ID increases). Be sure to have your data
ORDER BY [Date] when you check the running sum, which can ALSO be done by
'sorting' the data view (and it is probably easier).



Vanderghast, Access MVP



Fox said:
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 said:
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


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?
 
Ad

Advertisements

F

Fox

I wanted to update you....I was able to complete this task in VB instead. In
case anyone would like see the code then I've attached it here.
Date fields are a pain!!!!

Function readtable(tablename As String)

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim bal As Currency
Dim posneg As Currency
Dim tbl As String
tbl = "tblES"
Set db = Access.Application.CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM tblES ORDER BY ID")

With rst
.MoveLast
.MoveFirst



Do While .EOF = False
blnkvalue = IsNull(rst.Fields("Payments").Value)

If blnkvalue = True Then

rst.Edit
rst.Fields("Payments").Value = "0.00"
rst.Update

End If


blnkvalue = IsNull(rst.Fields("Fees").Value)

If blnkvalue = True Then

rst.Edit
rst.Fields("Fees").Value = "0.00"
rst.Update


End If

blnkvalue = IsNull(rst.Fields("Balance").Value)

If blnkvalue = True Then

rst.Edit
rst.Fields("Balance").Value = "0.00"
rst.Update


End If

posneg = (rst.Fields("Payments").Value - rst.Fields("Fees").Value)

bal = bal + posneg
Debug.Print bal
rst.Edit
rst.Fields("Balance").Value = bal 'st.Fields("Balance").Value +
posneg
rst.Update
.MoveNext

Loop

End With

Set rst = Nothing
Set db = Nothing
End Function



--
Foxy


vanderghast said:
Indeed, without a real time part in the date field, the result you got, for
each record, is the cumulative running sum 'at the end of that day'. Note
that the data you supplied is NOT ordered by date since a bloc of records
dated the 6/10/2009 in inserted between two blocs dated 6/2/2009.

If there is really no time part in your date and time values, can you
,arbitrairy, add one, such as (after you made a backup just to be on the
safe side, and/or working on a copy of the real data), update the date field
like this:

UPDATE tableName
SET [date] = [date] + id / (1+DMax("id" , "tablename"))


That should give an arbitrairy time value to each record. You can then try
the running sum query on THAT table with MODIFIED date getting an arbitrary
TIME value (increasing as the ID increases). Be sure to have your data
ORDER BY [Date] when you check the running sum, which can ALSO be done by
'sorting' the data view (and it is probably easier).



Vanderghast, Access MVP



Fox said:
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 said:
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


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?
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top