Solved I Want to add Balance Column and Previous balance field in my Petty Cash Report.

[Khurramshaikh47]

Hi experts,
I need your help regarding petty cash report. I've created a petty cash with debit and credit column, I want to add Balance column, also want to add previous balance field as well. I am sharing screenshots of a Sample Petty Cash Report (Created in Ms Excel), DebitVoucher Table, DebitVoucherForm, PettyCashQuery, and PettyCash Report.

Thank you
SAMPLE PETTY CASH REPORT CREATED IN MS EXCEL

DEBIT VOUCHER TABLE

VOUCHER FORM

PETTY CASH QUERY

\
PETTY CASH REPORT

Kindly guide me how to do it.

Thank you

 

[arnelgp]

you just Create a summary of previous period (previous balance) in a query.

 

[Khurramshaikh47]

you just Create a summary of previous period (previous balance) in a query.

Can you explain in detail?

 

[June7]

One approach:

1. textbox in report header section named tbxBalFor with DSum() calculation to provide total through specified end date
=DSum("AmountReceived - AmountPaid", "DataVoucher", "PaymentDate<#" & Forms!formname.tbxDate & "#")

2. textbox in report detail section named tbxAmt with calculation =AmountReceived - AmountPaid and set RunningSum property to Yes (this textbox can be set not visible)

3. textbox named tbxBal in report detail section with calculation =tbxAmt + tbxBalFor

4. apply date filter to report to limit records
DoCmd.OpenReport "reportname", , , "PaymentDate >= #" & Forms!formname.tbxDate & "#"

Another method to obtain starting balance and running balance: https://answers.microsoft.com/en-us...-balance/7317ea59-5cb5-462f-96ea-29fe93b5fbed

 

[Khurramshaikh47]

One approach:

1. textbox in report header section named tbxBalFor with DSum() calculation to provide total through specified end date
=DSum("AmountReceived - AmountPaid", "DataVoucher", "PaymentDate<#" & Forms!formname.tbxDate & "#")

2. textbox in report detail section named tbxAmt with calculation =AmountReceived - AmountPaid and set RunningSum property to Yes (this textbox can be set not visible)

3. textbox named tbxBal in report detail section with calculation =tbxAmt + tbxBalFor

4. apply date filter to report to limit records
DoCmd.OpenReport "reportname", , , "PaymentDate >= #" & Forms!formname.tbxDate & "#"

Another method to obtain starting balance and running balance: https://answers.microsoft.com/en-us...-balance/7317ea59-5cb5-462f-96ea-29fe93b5fbed

Hi can you tell me what is "DataVoucher" in the above code?

 

[June7]

Sorry, I misread your image. Took closer look and see now table name is DebitVoucher.

 

[Khurramshaikh47]

Sorry, I misread your image. Took closer look and see now table name is DebitVoucher.

Ok dear no problem.
Thank you so much June7

 

[Pat Hartman]

Reports provide a runningSum property. The options are Over All and Over Group. This may solve the problem without the overhead of domain functions or other queries.

 

[June7]

That will not provide a starting balance from a prior period records.

 

[Pat Hartman]

If you need a starting balance from data that is not included in the report, create a query that sums the data. Then make a union query to union the starting balance with the dtal for the report. Make sure to add a dummy field so you can make the starting balance sort first. Make the starting balance dummy value = 1 and the dummy in the other query = 2. Sort by the dummy and whatever other columns you need to sort by.