jpawlow
asked on
Design report to print customer's address in Access 97
I'm using the report feature to print invoices. In design view for the report, what are the steps to get the customers name and address from the master client/address file? Also, if there is a P.O. Box field, I want that address and corresponding zipbox field to print on the invoice, instead of the street address. Any help would be most appreciated.
In report/design view, click a text box and drag it onto the detail of the report, then right click the text box and edit the properties. The control source will point to the field in the table you need.
Is this what you are asking? Or do you mean that the name and address are in a different table from the invoice information?
If this is the case then you will have to design a query first (I would recommend this anyway) with the customer name, address and invoice information as you need it. Also at this stage add another field that will take care of the second part of your question - the field should be something like:
Newfield: IIf(IsNull([POBOX]),[Stree
i.e. if the Pobox is missing then only the street address, otherwise the PoBox and Street address together.
Hope some of this helps.
Is this what you are asking? Or do you mean that the name and address are in a different table from the invoice information?
If this is the case then you will have to design a query first (I would recommend this anyway) with the customer name, address and invoice information as you need it. Also at this stage add another field that will take care of the second part of your question - the field should be something like:
Newfield: IIf(IsNull([POBOX]),[Stree
i.e. if the Pobox is missing then only the street address, otherwise the PoBox and Street address together.
Hope some of this helps.
ASKER
Thanks sblawrence for the prompt response. Your response is getting me closer to a solution. Since I am new to this exchange, I hope it's ok to clarify my situation so that the help is on target.
I have an existing query for the invoice details, charges. If I add the client table with address info. to the same query, do I need just *one* query field to print lastname, firstname, company name and address to the report? or do I need to drag all the pertinent fields into the query and setup some type of criteria? If the POBox field is null for example, then get street address field, zip and state fields. Re: records with POBox information, I want the POBox printed only and not the street address.
I hope this is clear as to what I need to accomplish.
I have an existing query for the invoice details, charges. If I add the client table with address info. to the same query, do I need just *one* query field to print lastname, firstname, company name and address to the report? or do I need to drag all the pertinent fields into the query and setup some type of criteria? If the POBox field is null for example, then get street address field, zip and state fields. Re: records with POBox information, I want the POBox printed only and not the street address.
I hope this is clear as to what I need to accomplish.
I'm new to the exchange too! Second day, and I think we're on very different timezones, so apologies for the delay!
While you're working on this I would advise setting up another query, using your existing query with invoice details, changes and the client table. Join the query and the table and make sure you're getting output that looks sensible (I guess one record per client with all the relevant invoice information).
There's no problem with having all the fields in your new query - at the report stage you can add as few or as many fields as you like. The trick is to have all the necessary fields in the query and then add new fields (expressions, like the example I gave before) which have some function to decide what they should contain.
I think I understand a little better what you need re. the POBox. Here goes:
If there is no PoBox, then use the customer address fields.
If there is a PoBox, then use the corresponding address fields for that PoBox.
The new fields that you will want to usee in the report will be:
NewStAddress: IIf(IsNull([POBOX]),[Stree
NewState: IIf(IsNull([POBOX], [State], [PoBox_State])
etc. - same for Zip.
I am assuming that PoBox has a corresponding State, Zip etc. If not (you only want the PoBox), then just replace [PoBox_State] with " " (an empty string) or whatever text you want to see.
While you're working on this I would advise setting up another query, using your existing query with invoice details, changes and the client table. Join the query and the table and make sure you're getting output that looks sensible (I guess one record per client with all the relevant invoice information).
There's no problem with having all the fields in your new query - at the report stage you can add as few or as many fields as you like. The trick is to have all the necessary fields in the query and then add new fields (expressions, like the example I gave before) which have some function to decide what they should contain.
I think I understand a little better what you need re. the POBox. Here goes:
If there is no PoBox, then use the customer address fields.
If there is a PoBox, then use the corresponding address fields for that PoBox.
The new fields that you will want to usee in the report will be:
NewStAddress: IIf(IsNull([POBOX]),[Stree
NewState: IIf(IsNull([POBOX], [State], [PoBox_State])
etc. - same for Zip.
I am assuming that PoBox has a corresponding State, Zip etc. If not (you only want the PoBox), then just replace [PoBox_State] with " " (an empty string) or whatever text you want to see.
ASKER
sblawrence, I read your answer posted on Dec 8th. I got too busy to try it. I will grade your answer on Monday 13th.
Thanks for your help.
Thanks for your help.
ASKER
sblawrence
Sorry for the delay in trying your latest solution for me. I edited the bill query to test the logic, if pobox field is empty, then show the street address field, but when I run the query there are no records. What did I do wrong?
I did relate the charges table with the client info table. Here's the exact statement in the query:
IIF(IsNull([POBOX]),[STREE
Sorry for the delay in trying your latest solution for me. I edited the bill query to test the logic, if pobox field is empty, then show the street address field, but when I run the query there are no records. What did I do wrong?
I did relate the charges table with the client info table. Here's the exact statement in the query:
IIF(IsNull([POBOX]),[STREE
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry for the delay, I have been busy because of the holidays and will be gone until Jan 3, 2000. So I'll respond to your last comment. Originally I had the IIF query as a criteria on the POBOX field and got no records, because my test file had no records with pobox field. This explains the zero records when I run the query. When I put in a new field in the client database, namely Expr1 and put the IIF query into that field criteria and run the query. I get no records.
I'm a MS Access 97 novice and trying to troubleshoot this through this exchange may not be best for my question.
I'll go ahead and rate the response and perhaps post again in 2000! Thanks so much anyway!
I'm a MS Access 97 novice and trying to troubleshoot this through this exchange may not be best for my question.
I'll go ahead and rate the response and perhaps post again in 2000! Thanks so much anyway!
ASKER