Blank Data Cells (Access ’97)

Topic

New Reply

This a follow up of my last post. I have included a ZIP file with a small generic database, eventually it will have all the same variables for 25 different items. It needs to be set up so we may query by item or groups of items. My problem is that the table and a query ran from the “Simple Query Wizard” returns zeros for the percent fields and the cycle fields. The form and the form’s datasheet view show all the data. I need to these to agree and anyone else be able to view the calculated data or print a report as needed. I hope I am making sense here, but I need it to be as user friendly as possible. Thanks in advance.

Reply | Quote

Replies

The text boxes showing percentages in your form are NOT bound to the percentage fields in your table. The expressions in their Control Source property calculate the percentages on the fly – which is as it should be.

Since these text boxes are not bound to a field, the percentages do NOT get saved in the table, so when you open the table or query, you don’t see the calculated values you saw in the form. Instead, you see zeros, because the Default Value property of the percentage fields has been set to 0.

You can display the correct percentages in a report easily – create text boxes on the report the same way you did on the form – you can even copy them from the form to the report.

You can also calculate the percentages in a query; then you can use the query as Record Source for both forms and reports. To create a calculated field, type the name you want it to have, followed by a colon, and the expression, for example:

pctE: ([wtE]-[wtA])/[wtE]

Don’t worry that the percentages are not in your table. I will repeat what I wrote in a reply to the previous thread on this subject: having users open a table or query directly is NOT user-friendly. End-users should only open forms and reports.

I have attached a modified version of your database. The percentages are calculated in the query, and the query is used as record source of the form. The percentage fields have been removed from the table.

Reply | Quote