insert opening and closing balance in report 1

[markbrum]

Hi,

I have this query

inv date value balance
1 05/12/04 £25.00 £25.00
2 05/12/04 -£20.00 £5.00
3 06/12/04 £10.50 £15.50
4 06/12/04 £8.00 £23.50
5 06/12/04 -3.00 £20.50
6 07/12/04 £40.00 £60.50
7 08/12/04 £2.00 £62.50

The user runs a report and enters a start and end date, I would like to put the opening balance in the header and closing balance in the footer of the report, I can draw both bits of info from the balance field but I'm not sure how to tell a bound textbox that it must display the last record in the query (for closing balance) and even worse the record BEFORE the first record for the opening balance, because the balance brought forward should be the closing balance on the day before the period starts.

Any help much appreciated.
Thanks, Mark.

 

[jksmi]

Hi,

how are you passing the user input to the report to filter it...

HTH, Jamie
FAQ219-2884

 

[markbrum]

Hi,

My query is based on a table which holds all the transactions, and includes:-

WHERE (((Transactions.Date) Between [Start date DD/MM/YY] And [End date DD/MM/YY]));

When you run the report it prompts for these 2 parameters

 

[jksmi]

Hi,

just to clarify - they open a query not a report...

HTH, Jamie
FAQ219-2884

 

[jksmi]

Hi, also is the inv incremental or does date include time?

HTH, Jamie
FAQ219-2884

 

[markbrum]

Sorry, not sure I understand. I need to display the information in a report. I run a report which draws its data from a query. The query requires 2 parameters so when I run the report it asks for them. So far all the work is done by the query, the report just displays the results.

No times are stored but inv is incremental.

Thanks, Mark.

 

[jksmi]

Hi,

I've assumed that you are definitely looking at a query, and inv is incremental...

SELECT
    Format(" Opening Balance") AS INV,
    Format(DMAX(
            "DATE","TRANSACTIONS", "DATE <= Format('" &  [Start date DD/MM/YY] & "', 'DD/MM/YY')"
            ), "dd/mm/yy") AS INV_DATE,
    Format(NULL,"##,##0.00") AS TRANS_VALUE,
    Format(DLOOKUP(
        "BALANCE", "TRANSACTIONS","INV = "
            & DMAX(
                "INV","TRANSACTIONS", "DATE < Format('" &  [Start date DD/MM/YY] & "', 'DD/MM/YY')")
            ), "##,##0.00") AS BALANCE
FROM TRANSACTIONS
    ORDER BY 2,1

UNION

SELECT
    Format(INV,"#0") AS INV_2,
    Format([DATE],"DD/MM/YY") AS INV_DATE,
    Format([VALUE],"##,##0.00") AS TRANS_VALUE,
    Format([BALANCE],"##,##0.00") AS BALANCE_2
FROM TRANSACTIONS
    WHERE (((Transactions.Date) Between [Start date DD/MM/YY] And [End date DD/MM/YY]))
    ORDER BY 2,1

UNION

SELECT
    Format("Closing Balance") AS INV,
    Format(DMAX(
            "DATE","TRANSACTIONS", "DATE <= Format('" &  [End date DD/MM/YY] & "', 'DD/MM/YY')"
            ), "dd/mm/yy") AS INV_DATE,
    Format(NULL,"##,##0.00") AS TRANS_VALUE,
    Format(DLOOKUP(
        "BALANCE", "TRANSACTIONS","INV = "
            & DMAX(
                "INV","TRANSACTIONS", "DATE <= Format('" &  [End date DD/MM/YY] & "', 'DD/MM/YY')")
            ), "##,##0.00") AS BALANCE
FROM TRANSACTIONS
    ORDER BY 2,1

You should try to avoid naming fields reserved words (like date) if possible...

HTH, Jamie
FAQ219-2884

 

[jksmi]

ah,

you've posted back in the meantime - this will still work as the basis for a report - but it would have been a hell of a lot easier if I wasn't doing it all in the SQL! Since the opening balance would have been a section header with a calc for balance = value for dmin invoice number and closing balance would just be the balance entry for dmax invoice number in a section footer.

There is an Access Reports forum here as well that might be worth checking out...

HTH, Jamie
FAQ219-2884

 

[jksmi]

Hi,

by the way - did the last solution (running totals) work for you? just curious, it would be good to know if you found another solution...

HTH, Jamie
FAQ219-2884

 

[markbrum]

Hi,

Thanks a lot for your help, this is great, sorry if I posted this in the wrong place.

That other thread, the problem was driving me mad then I realised I was looking at it totally the wrong way and ended up achieving what I wanted without doing this, so no I didn't use that. Hope you don't feel you wasted your time 'cos I made a note of it for the future and I'm sure someone else will see it and find it useful.

Thanks again, Mark.

 

[jksmi]

Hi,

no worries, that's often the way - a couple of days away from the problem, look at it from a whole new perspective and the answer just pops up...

HTH, Jamie
FAQ219-2884