Choosing one address over another in a table

[Ilan]

Hello,
I have a table in access which stores my customer
information. It allows the customer to enter in a
billing address and a shipping address (if different than
the billing address). There is a check box which is
checked if there is a different shipping address. I
would like to merge to word to print out my customer
information, however i would like all of the shipping
addresses to be printed out (not the billing addresses).
How do i send only the shipping addresses to word? I
tried a query, but it will not hold my alphabetical order
sort like the table does, so i would like to send it via
a table.

Thanks in advance for your help. I'm also going to ask
word people too since my problem encompasses both
programs.

Ilan

 

[Steve Schapel]

Ilan

I do not understand your comment about the query "not hold my
alphabetical order sort like the table does". Normally the
functionality is just the opposite... tables do not have the data in any
particular order, whereas you have total control over the sorting in a
query.

In your query you can make a calculated field like this...
OnlyShipping: Nz([Shipping Address],[Billing Address])

In other words, if there is an entry in the Shipping Address field, this
will be used, but if there is no Shipping Address entry, the Billing
Address data will be used. Is this what you want?

 

[Ilan]

See, i sort my information on three levels. Last name,
then first name, then middle name (i.e. I want John B.
Colangelo to come before John P. Colangelo). If i send
to word i can sort with these levels while i have not
been able to do so in the query. As for your suggestion
of the query structure, that is what i want, but since i
broke down my fields into add line 1, add line 2, city,
state, zip, etc. for each address will this still be
plausible. If yes, should i (and would you mind) if
i "borrowed" your SQL structure for my query? Also, do i
need to drop down to SQL view to use it and what does the
NZ stand for?

Thanks,
Ilan

-----Original Message-----
Ilan

I do not understand your comment about the query "not hold my
alphabetical order sort like the table does". Normally the
functionality is just the opposite... tables do not have the data in any
particular order, whereas you have total control over the sorting in a
query.

In your query you can make a calculated field like this...
OnlyShipping: Nz([Shipping Address],[Billing Address])

In other words, if there is an entry in the Shipping Address field, this
will be used, but if there is no Shipping Address entry, the Billing
Address data will be used. Is this what you want?

--
Steve Schapel, Microsoft Access MVP

Hello,
I have a table in access which stores my customer
information. It allows the customer to enter in a
billing address and a shipping address (if different than
the billing address). There is a check box which is
checked if there is a different shipping address. I
would like to merge to word to print out my customer
information, however i would like all of the shipping
addresses to be printed out (not the billing addresses).
How do i send only the shipping addresses to word? I
tried a query, but it will not hold my alphabetical order
sort like the table does, so i would like to send it via
a table.

Thanks in advance for your help. I'm also going to ask
word people too since my problem encompasses both
programs.

Ilan

.

 

[Steve Schapel]

Ilan

If you look at the deign view of a query, you will see that there is a
row in the query design grid for "Sort", which you can use to have
control over the order of the records. This is not possible in a table.

If your addresses are split over a number of fields, it makes things a
bit more complicated, but it is still possible. When the address is
exported to Word, do you want it still split into the separate
components, or do you want the address "unified".

Nz stands for "null to zero". It is one of Access's standard built-in
functions.

 

[Ilan]

ok i will sort my query accordingly. i would like to keep
the fields separated when they are exported to word.

The NZ feature sounds like it could help, how could i go
about finding more information on how to use it?

Thanks,
Ilan

 

[Steve Schapel]

Ilan,

You will find a topic on the Nz() function in Access Help.
The syntax is as I gave it before...
Nz([NameOfField],ValueToUseIfFieldIsNull)

 

[Ilan]

Ok, final question. I have my data stored on two tables
as i mentioned earlier. But data stored in the shipping
address table does not store data in the table at all if
the person is using the billing address for everything.
Therefore, how do i pull the data so it shows everyone.
If i run it right now i only get those records that are
in the shipping address table.

Thanks,
Ilan

-----Original Message-----
Ilan,

You will find a topic on the Nz() function in Access Help.
The syntax is as I gave it before...
Nz([NameOfField],ValueToUseIfFieldIsNull)

--
Steve Schapel, Microsoft Access MVP

ok i will sort my query accordingly. i would like to keep
the fields separated when they are exported to word.

The NZ feature sounds like it could help, how could i go
about finding more information on how to use it?

Thanks,
Ilan

.

 

[Steve Schapel]

Ilan,

Actually, you didn't mention earlier that you had the shipping address
in a separate table. What is the reason for this design?

You willl need to use a Left Join between the tables in the query. In
design view of the query, double-click on the join line between the
tables, and then in the properties dialog that pops up, select the
option that says something like "all records from the table with the
billing address, and matching records from the shipping address table".

 

[Ilan]

I decided to have them on separate tables just because i
thought it would be cleaner. I ran the Nz functions in
my query and it worked perfectly!! Thanks for all of your
help Steve!

Ilan