Show data by check the checkbox into report (1 Viewer)

[ZKHADI]

hy friends.....

i delete first post. i got another idea if it work by i need your help.

i made a form link with 2 tables ... Student detail and Fee table.

first i will put fee details and take a print to student with Dateofissue.
when student pay and give me i will check the box as paid and put the DateofDeposite.
now the problem is that when i issue the print i get the result as paid on accounts report. that should be zero if the DateofDeposite is null.
how i will code on check box. when the date put and check the box it will consider as paid and show that amount as paid on report. see image

1 is Main form with subform
2 is checkbox
3 is fee voucher
4 is Accounts report
5 is DateofDeposite textbox on report
6 is DateofDeposite Texbox on Form

in vocher and accounts reports you can see the same amount.
the vocher is still unpaid and no DateofDeposite is type and checkbox is uncheck.
i need the Accounts report is zero because status is unpaid
when check the box and put the date then show the amount in accounts report.

 

[The_Doc_Man]

OK, first remember that you normally DON'T update anything via reports. They are data-passive (or at least are SUPPOSED to be that way.) So what I am going to suggest involves a query that will not be usable to update anything; it will only SHOW things.

Take whatever query you use for this report. In the field that would show the amount, don't show the amount directly. Instead, use

IIF( IsNull([DateofDeposite]), 0, [Amount]) AS AmtPaid

Then build your report off of this query, which will do the logic for you. Note that because of the IIF, the query has to be used only for reports, not for anything that would need to update stuff. But don't worry, the extra query is cheap. An unopened query takes up only a small fixed QueryDef header plus the SQL string that defines the query. So you can easily afford to have one query for forms and a separate, logic-enhanced query for reports.

 

[ZKHADI]

OK, first remember that you normally DON'T update anything via reports. They are data-passive (or at least are SUPPOSED to be that way.) So what I am going to suggest involves a query that will not be usable to update anything; it will only SHOW things.

Take whatever query you use for this report. In the field that would show the amount, don't show the amount directly. Instead, use

IIF( IsNull([DateofDeposite]), 0, [Amount]) AS AmtPaid

Then build your report off of this query, which will do the logic for you. Note that because of the IIF, the query has to be used only for reports, not for anything that would need to update stuff. But don't worry, the extra query is cheap. An unopened query takes up only a small fixed QueryDef header plus the SQL string that defines the query. So you can easily afford to have one query for forms and a separate, logic-enhanced query for reports.

first of all i wrote the code the AmtPaid i didnt understand what is this?
and i want zero all these textboxes mentioned in below image how i will code for all in query?

 

[Pat Hartman]

Your table design is the foundation of your application. If you don't have a firm foundation, you will forever be fixing cracks. Normalize the tables as your first step.

You are assuming that if you want a report that looks like a spreadsheet that you need a table that looks like a spreadsheet and that is not true. You can use a cross tab to produce your spreadsheet like report.