Using MS Access/Report

Advertisement


Question
QUESTION: Hi ,
I work with reports in Ms Access and in bill-report which I use for all bills in my system I need to change a field. I have a table which 'Subject' is one of its column and the value for all records is 0.00 and it means that in bill-report it shows me  0.00
for all.

Subject_bill:      0.00

But now I need to change it for special Subject, for example for
Subject = 795  will show me  347.00 in bill-report :

Subject_bill       347.00

Could you help me how I should change the report?

ANSWER: Shouldn't this change be made on the table level? I would run an Update query that would look like this:

UPDATE table SET Subject_bill=347 WHERE Subject = 795;

If you only want to do this on the report, then you would change the Controlsource for the Subject_bill Control on the report to:

=IIF([Subject]=795,347,0)

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: Thank you Scott. I used the second solution and changed the report but when I tried to open the report for all bills it showed me a little message box that I should to enter the Subject(for example 795) in the field and then Ok and it showed me the report. But everyday I work with more than 60-80 bills and can not to enter Subject for all of them.

Could you help me how to change it?And why it shows me this box?
Regards.

Answer
I'm a little unclear here. Is it only this one Subject that has a different bill or does each subject have its own bill?

As for being prompted, that generally means you are reference a variable that Access can't find so it assumes you want to enter it at runtime. So Subject is probably not the name of a control on your report or maybe not the right fieldname.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.