Bank Statement ** urgent **

[Guest]

i have a table with 4 coluns (date,descripton,debit,credit), in the the
report i have de same coluns plus another that is the balance
([debit]-[credit]). the problem is when i ask for a report limited for
example for 1 month the balance is never correct because access is only
doing
the sum in that month. how can i resove this...

i'm trying to do a bank statement, and when i ask only the statement of 1
month i want that the balance to have the correct balance (including the past
movements, previous balance). thanks, lightning

 

[Steve Schapel]

Lightning,

You'll need to separately calculate the "Opening Balance" as at the
beginning of the date period, and then use that in your subsequent
calculations. One way to do this wopuld be to put an unbound textbox in
the Report Header section, with its Control Source property set to the
equivalent of...
=DSum("[credit]-[debit]","a table","[date]<#" & [YourStartDate] & "#")

By the way, as an aside, 'date' is a Reserved Word (i.e. has a special
meaning) in Access, and preferably not be used as the name of a field or
control.

 

[Joseph Meehan]

i have a table with 4 coluns (date,descripton,debit,credit), in the
the report i have de same coluns plus another that is the balance
([debit]-[credit]). the problem is when i ask for a report limited for
example for 1 month the balance is never correct because access is
only doing
the sum in that month. how can i resove this...

You will want to read Steve's response, but also consider this.

It appears you are storing the result of a calculation in a table and
may be relying or at least expecting a table to maintain a specific order
(sort) All this calculation stuff should be done in a query, form or
report, not in the table. You can run into all kinds of additional problems
doing it that way.

 

[Guest]

And how can i do a running sum including the openning Balance ????
can you help...

"Steve Schapel" escreveu:

Lightning,

You'll need to separately calculate the "Opening Balance" as at the
beginning of the date period, and then use that in your subsequent
calculations. One way to do this wopuld be to put an unbound textbox in
the Report Header section, with its Control Source property set to the
equivalent of...
=DSum("[credit]-[debit]","a table","[date]<#" & [YourStartDate] & "#")

By the way, as an aside, 'date' is a Reserved Word (i.e. has a special
meaning) in Access, and preferably not be used as the name of a field or
control.

--
Steve Schapel, Microsoft Access MVP

i have a table with 4 coluns (date,descripton,debit,credit), in the the
report i have de same coluns plus another that is the balance
([debit]-[credit]). the problem is when i ask for a report limited for
example for 1 month the balance is never correct because access is only
doing
the sum in that month. how can i resove this...

i'm trying to do a bank statement, and when i ask only the statement of 1
month i want that the balance to have the correct balance (including the past
movements, previous balance). thanks, lightning

 

[Steve Schapel]

Lightning,

The textbox for the amount on the report has a Running Sum property.
You haven't given us enough information to be specific, but I think you
will find that this property is applicable. Set it to Over Group or
Over All, as required.

 

[Guest]

Lightning,

The textbox for the amount on the report has a Running Sum property.
You haven't given us enough information to be specific, but I think you
will find that this property is applicable. Set it to Over Group or
Over All, as required.

 

[Steve Schapel]

Lightning,

Ah, now I understand the question!

Make the Balance control hidden (set its Visible property to No). Then
put another unbound textbox on the report, Running Sum set to No, and
with its Control Source property set to the equivalent of...
=[Balance]+[OpeningBalance]
Should do the trick I think.

 

[Guest]

Great,
thanks for the help
....and now a easy question...
when the opening balance is NULL the report is blank, how can i convert this
field to show "0,00€" instead of null. Because the opening balance is null
the report doesn't show the records.

"Steve Schapel" escreveu:

Lightning,

Ah, now I understand the question!

Make the Balance control hidden (set its Visible property to No). Then
put another unbound textbox on the report, Running Sum set to No, and
with its Control Source property set to the equivalent of...
=[Balance]+[OpeningBalance]
Should do the trick I think.

--
Steve Schapel, Microsoft Access MVP

on the field "balance" i have already the property to do a runningsum but how
can i include the "openning balance" on this sum...