Coding so a report will use the correct addresses

[TonyVelasco]

Re-posting this here instead of in the reports forum. Not sure where is the best place to ask this, but here goes!

Here is what I am trying to accomplish:

I need to print mailing labels, using a report. However, I have two different sets of addresses in each record of my table. The first address is the street address of the company. The second address is the mailing address. There is an Address, City, State, And Zip field for each address, and also mailing address fields simply named MailingAddress, MailingCity, etc...

What I want to do is have a button on a form that when clicked, prints out the mailing labels, using the main address if there is no mailing address, but using the mailing address if it exists.

What do you think would be the best way to accomplish this? Do I call two queries from an if then, and if so, what would the syntax of all this look like? Do I somehow merge the data from the address fields and the mailing address fields together into a query or temporary table, something like LabelAddresses? Then have fields in it that are either the main address or mailing address, depending on the criteria for the query?

Some advice on the cleanest, quickest way to do this would be greatly appreciated! Also, any code examples for my button would be helpful too!

This post is about vba coding, queries, and reports, along with a form button, so who knows where it should best be posted?!?!

 

[Mile-O]

I don't know how the mailing labels/report part works as I've never needed to do labels or anything of the sort.

However, to get the query:

Select the table you want.

Now, you create the fields rather than select them from the table

Address1: IIf(IsNull([StreetName]), [MailingAddress], [StreetName])

Address2: IIf(IsNull([StreetName]), [MailingCity], [CityAddress])

etc.


always checking on the topmost part of an address..



Use this query on your report.

 

[TonyVelasco]

Thanks Mile-O-Phile!

Getting that query setup made it easy to put a button on the form that ouput the label report with the correct addresses! Thanks.

I hadn't found any documentation that showed how to make query fields like that, rather than using actual fields in a table. I had written queries that filtered based on actual fields, but had not done any using fields that only exist in the query based on calculations or data filtered from other fields. Your examples opened up a whole new area of access to me.

Is there anything in the access help that has more on the things you can do in the fields row of a query. I definitely need to learn more on all you can do in the query design window!

Next on my hit list is cleaning up the address on the license form so it prints with no spaces. Some addresses have a DBA, some don't, some have 2 lines in the address field, some don't. I need some syntax for how to create a text block that does that. I know there must be some examples of this on the forum somewhere, any help finding them would be appreciated!