Re: Running Balance

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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


.

Relevant Pages

  • Re: Running Balance
    ... The Date field has the propertys Date/Time in the table, ... you would have to force that format instead of using the explicit format ... I would have probably had a single field for transaction amount ... I've used the following expression in the query. ...
    (microsoft.public.access.queries)
  • can anyone provide a query for this
    ... I have a table with this format: ... I want to write a query which will arrange all the records in the ... ascending orderand give the seconds between the ... current transaction and the next transaction. ...
    (microsoft.public.access.queries)
  • RE: Combo box bound field change
    ... Modify the row source to include both fields. ... Make the bound column the company name column. ... now you can change the company name any time and your query ... Make a copy of your transaction table, but copy only the structure, not the ...
    (microsoft.public.access.forms)
  • Re: Transaction Isolation Level
    ... This means that while I initiate a transaction selecting all new orders it ... My query is executed on a SQL server 2005. ... the isolation level you should use is snapshot isolation. ...
    (comp.databases.ms-sqlserver)
  • RE: Combo box bound field change
    ... now you can change the company name any time and your query ... Make a copy of your transaction table, but copy only the structure, not the ... Modify the New transaction table and change the company name field to ... I'd run a report to show what all that company purchased. ...
    (microsoft.public.access.forms)