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.
jpawlowAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jpawlowAuthor Commented:
Adjusted points to 100
0
sblawrenceCommented:
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]),[StreetAddress],[POBOX]&", "&[StreetAddress])

i.e. if the Pobox is missing then only the street address, otherwise the PoBox and Street address together.

Hope some of this helps.
0
jpawlowAuthor Commented:
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.
0
Redefining Cyber Security w/ AI & Machine Learning

The implications of AI and machine learning in cyber security are massive and constantly growing, creating both efficiencies and new challenges across the board. Join our webinar on Sept. 21st to learn more about leveraging AI and machine learning to protect your business.

sblawrenceCommented:
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]),[StreetAddress],[PoBox_StreetAddress])
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.
0
jpawlowAuthor Commented:
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.
0
jpawlowAuthor Commented:
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]),[STREET],[POBOX])
0
sblawrenceCommented:
Hello again - bit puzzled.
You say you have no records? In that case there must be something else that is acting as a criteria - the expression IIF(IsNull([POBOX]),[STREET],[POBOX]) works fine (I've just double checked) - it will only create a new field, not screen on the records that you're selecting.
Take out the expression and try again, are there still no records? (my guess is there wouldn't be). I guess there is something else going on - possibly in how you have related the charges table with the client info table (double click on the line joining the tables and make sure you have the right option selected).

The expression should be in the field box in query design view, it will look something like:
Expr1: IIf(IsNull([POBOX]),[STREET],[POBOX])
Here Expr1 is the name of the new field that we have created.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jpawlowAuthor Commented:
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!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Hardware

From novice to tech pro — start learning today.