print calculated field

[mixalisaspr]

Hello,

I really need help with this

I have 3 tables

BASIC
TOPAY
PAID

TOPAY and PAID have a field called AMOUNT.
They are connected:
BASIC.ID=TOPAY.ID
BASIC.ID=PAID.ID

What I want is to make a report with all information, and also a subtract of sum(TOPAY.AMOUNT) - sum(PAID.AMOUNT)

I have made a form, where this is calculated, but i dont seem able to use this for printing. Is there any way to store this to my BASIC table?

 

[jzwp22]

In general, calculated values are not stored in tables (only the raw data used to do the calculation). Just as you did in the form, you can add a calculated control to your report. An alternative would be to add a calculated field to a query and base your report on that query.

 

[MarkK]

Probably Paid and ToPay should be in the same table, since they probably differ only by status. Introduce as Yes/No field called Paid and put all that data in one table. Then you can easily sum the amounts because they are all in the same table, as they ought to be.
Cheers,

 

[mixalisaspr]

@jzwp22 i tried to make the same calculation in the report, but i get an error. Adding everything in queries and then creating report makes sense, and i will try it tomorrow in the office.

@lagbolt the two tables are totally different. the first one describes the service that the client bought while the second one how he paid. for example someone might pay once per month for 4 services.

thnx both, i will tell you tomorrow if it works

 

[mixalisaspr]

I tried it and works fine! I made a query calculating the TotalToPay and another one with the TotalPaid, so everything appears well enough in the report!

But,... when someone hasn't paida anything, then the query doesn't return results and the cell Rest=TotalToPay-TotalPaid returns error, because totalpaid doesnt exist!

 

[jzwp22]

You will have to set up a query that returns all people/organizations and do a left join to the query that returns those who have paid something. In that query, you will have to use the IIF() & IsNull functions relative to the amount paid and if null, return 0 otherwise return totalpaid.

IIF(IsNull(Totalpaid),0,totalpaid)