How to use one line in an address

[Rastamon]

I am writing a query in Access2000 just with entries from one table.
One of my entries is Cust, for the Customer and the table is in the
data type of a memo with the customer's info set up like this:

Name
address
town
phone #

How can I set the criteria so that the query only comes back with the
first line? Thank you.

[Douglas J. Steele]

Are you saying that all of that information, including Carriage Return/Line
Feeds, is being stored in a single field? If there's a chance to change your
design so that the address fields are properly normalized, do it!

To get everything up to the first Carriage Return/Line Feed, try

Left([AddressField], InStr([AddressField], Chr(13) & Chr(10)) - 1)

(change AddressField to the appropriate name)

If there's a chance that some of the fields don't have the Carriage
Return/Line Field combo in them, try

IIf(InStr([AddressField], Chr(13) & Chr(10)) = 0, [AddressField],
Left([AddressField], InStr([AddressField], Chr(13) & Chr(10)) - 1))

(all on one line)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Rastamon" <Ra*********@gmail.com> wrote in message
news:11*********************@l41g2000cwc.googlegro ups.com...

I am writing a query in Access2000 just with entries from one table.
One of my entries is Cust, for the Customer and the table is in the
data type of a memo with the customer's info set up like this:

Name
address
town
phone #

How can I set the criteria so that the query only comes back with the
first line? Thank you.

[Mike Turco]

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:zr********************@rogers.com...

Are you saying that all of that information, including Carriage
Return/Line Feeds, is being stored in a single field? If there's a chance
to change your design so that the address fields are properly normalized,
do it!

What does seperating the address fields have to do with normalization?

[Rastamon]

My table field is called Cust, it is set up as a memo. In the form,
when I come to that field I manually type:

Name (Carriage return)
Address (carriage Return)
Town, zip (Carriage Return)
Phone Number (Tab over to the next field)

[Douglas J. Steele]

"Mike Turco" <mi*******@yahoo-no-spam-4-me.com> wrote in message
news:030Zd.108846$bu.69757@fed1read06...

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:zr********************@rogers.com...

Are you saying that all of that information, including Carriage
Return/Line Feeds, is being stored in a single field? If there's a chance
to change your design so that the address fields are properly normalized,
do it!

What does seperating the address fields have to do with normalization?

One of the rules of database normalization is that a field holds only a
single piece of data. Having multiple pieces of data (address, town, phone
number, etc.) in a single field is a violation of that rule.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

[rkc]

Mike Turco wrote:

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:zr********************@rogers.com...

Are you saying that all of that information, including Carriage
Return/Line Feeds, is being stored in a single field? If there's a chance
to change your design so that the address fields are properly normalized,
do it!

What does seperating the address fields have to do with normalization?

Define address.

[Rastamon]

One of the rules of database normalization is that a field holds only a single piece of data. Having multiple pieces of data (address, town, phone number, etc.) in a single field is a violation of that rule.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

OK, I am self taught I didn't realize that. I will separate the
address into fields, thanks for the tip.