| re: opening balance - closing balance
"Greg" <greg.anti.spam@removethis.anet.net.th> wrote in
news:c86qum$pj0$1@news.inet.co.th:
[color=blue]
> But i don't want to see all other months. For example if user
> selects Feb-2004 report should look like:
>
> DESCRIPTION DATE IN OUT BALANCE
> Opening Balance 31-01-2004 200
>
> DescriptA 02-02-2004 20 220
> DescriptB 05-02-2004 5 215
> DescriptC 17-02-2004 9 224
> DescriptD 19-02-2004 8 232
> DescriptE 22-02-2004 20 252
> DescriptF 26-02-2004 12 232
>
> Closing Balance 29-02-2004 230
>
> So maybe the opening balance represents many months of
> transactions.
>[/color]
As Mal said, a totals query would be the answer.
This is one approach.
Create a query like
SELECT
"Opening Balance" AS transactionDescription,
#01/31/2004# as transactiondate,
sum(nz(in) - nz(out)) AS balance
Sum(0) as IN,
sum(0) AS OUT,
sum(nz(in) - nz(out)) AS balance
GROUP BY transactionDescription,TransactionDate
WHERE transactionDate < #02/01/2004#
The fields should match the ones in your month's transactions
query. This will return a one row result with the opening
balance.
Now you need to create a union query of this one and the month's
transactions query.
You use that query as the recordsource for your report.
Another approach is to just create a group header on the form,
and put a textbox in the form with a DSum() function that returns
the opening balance.
[color=blue]
> Any Ideas.
>
> Greg[/color]
[color=blue]
> "Mal Reeve" <lorimal@earthlink.net> wrote in message
> news:OcBpc.8343$zO3.7767@newsread2.news.atl.earthl ink.net...[color=green]
>> Opening Balance would be..
>>
>> All transactions up to the date of 'Opening'
>> Closing Balance would be all transactions up to date of
>> 'Closing'
>>
>> A totals query might work well...
>>
>> Mal.
>>
>>
>> "Greg" <greg.anti.spam@removethis.anet.net.th> wrote in
>> message news:c853b9$5j7$1@news.inet.co.th...[color=darkred]
>> > On my report I want to have an opening balance signifying
>> > all[/color][/color]
> transactions[color=green][color=darkred]
>> > up to the month selected
>> > and detailed transactions for the month selected
>> > and then a closing blance.
>> >
>> > I'm perpelexed how to do this Anyone knows how the banks do
>> > it with statement formatting?
>> >
>> > suggestions, ideas welcome.
>> >
>> > Greg
>> >
>> >[/color]
>>
>>[/color]
>
>[/color]
| 
