changing a two line address into one

  • Thread starter Thread starter JulieD
  • Start date Start date
J

JulieD

hi all

is this possible (and, if so, how can i achieve it???)
i have a form which allows the user to enter an addres into text box that
allows two or more lines of text, suburb, state & postcode are stored in
separate fields.

this works fine for mailing lists etc, however, i also need to print out an
address list with the address all on one line - i can do this, however, the
address bit only shows the first line of the address entered into the text
box and not the whole lot.

e.g. user enters

Level 1 The Building
Nowhere Street

into the address box and i want it to look like this
Level 1 The Building Nowhere Street in the report

Please help.

Thanks
JulieD
 
Julie,

You have discovered why it is not a good idea to allow this sort of data
entry; your two-line address field should be two fields.

That having been said, you will need to write a query, if you have not
already done so, and insert the following calculated field:

OneLineAddr: Replace(Replace([TwoLineAddr],Chr(10)," "),Chr(13),"")

Replace the field "TwoLineAddr" with the actual name of your field.

hth,
 
Hi Cheryl

totally agree with you, but trying to convince other people of this is not
so easy :)

i'll give your solution a go.

Thanks
JulieD

Cheryl Fischer said:
Julie,

You have discovered why it is not a good idea to allow this sort of data
entry; your two-line address field should be two fields.

That having been said, you will need to write a query, if you have not
already done so, and insert the following calculated field:

OneLineAddr: Replace(Replace([TwoLineAddr],Chr(10)," "),Chr(13),"")

Replace the field "TwoLineAddr" with the actual name of your field.

hth,
--
Cheryl Fischer
Law/Sys Associates
Houston, TX

JulieD said:
hi all

is this possible (and, if so, how can i achieve it???)
i have a form which allows the user to enter an addres into text box that
allows two or more lines of text, suburb, state & postcode are stored in
separate fields.

this works fine for mailing lists etc, however, i also need to print out an
address list with the address all on one line - i can do this, however, the
address bit only shows the first line of the address entered into the text
box and not the whole lot.

e.g. user enters

Level 1 The Building
Nowhere Street

into the address box and i want it to look like this
Level 1 The Building Nowhere Street in the report

Please help.

Thanks
JulieD
Click to expand...
Click to expand...
 
Hi Cheryl

i'm getting an "Undefined function "Replace" in expression" error message
i'm using Access 97, if that makes a difference.

Appreciate your help.

Regards
JulieD

Cheryl Fischer said:
Julie,

You have discovered why it is not a good idea to allow this sort of data
entry; your two-line address field should be two fields.

That having been said, you will need to write a query, if you have not
already done so, and insert the following calculated field:

OneLineAddr: Replace(Replace([TwoLineAddr],Chr(10)," "),Chr(13),"")

Replace the field "TwoLineAddr" with the actual name of your field.

hth,
--
Cheryl Fischer
Law/Sys Associates
Houston, TX

JulieD said:
hi all

is this possible (and, if so, how can i achieve it???)
i have a form which allows the user to enter an addres into text box that
allows two or more lines of text, suburb, state & postcode are stored in
separate fields.

this works fine for mailing lists etc, however, i also need to print out an
address list with the address all on one line - i can do this, however, the
address bit only shows the first line of the address entered into the text
box and not the whole lot.

e.g. user enters

Level 1 The Building
Nowhere Street

into the address box and i want it to look like this
Level 1 The Building Nowhere Street in the report

Please help.

Thanks
JulieD
Click to expand...
Click to expand...
 
Yes, it does make a difference that you are using Access 97, as the
Replace() function does not exist in that version. For Access 97, I would
use two calculated fields in the query, as follows:

AddrLine1: Left([TwoLineAddr],InStr([TwoLineAddr],Chr(10))-2)

and

AddrLine2: Mid([TwoLineAddr],InStr([TwoLineAddr],Chr(13))+2)

Just tested this out and it will split correctly.

--
Cheryl Fischer
Law/Sys Associates
Houston, TX

JulieD said:
Hi Cheryl

i'm getting an "Undefined function "Replace" in expression" error message
i'm using Access 97, if that makes a difference.

Appreciate your help.

Regards
JulieD

Cheryl Fischer said:
Julie,

You have discovered why it is not a good idea to allow this sort of data
entry; your two-line address field should be two fields.

That having been said, you will need to write a query, if you have not
already done so, and insert the following calculated field:

OneLineAddr: Replace(Replace([TwoLineAddr],Chr(10)," "),Chr(13),"")

Replace the field "TwoLineAddr" with the actual name of your field.

hth,
--
Cheryl Fischer
Law/Sys Associates
Houston, TX

JulieD said:
hi all

is this possible (and, if so, how can i achieve it???)
i have a form which allows the user to enter an addres into text box that
allows two or more lines of text, suburb, state & postcode are stored in
separate fields.

this works fine for mailing lists etc, however, i also need to print
Click to expand...
out
an
address list with the address all on one line - i can do this,
Click to expand...
however,
the
address bit only shows the first line of the address entered into the text
box and not the whole lot.

e.g. user enters

Level 1 The Building
Nowhere Street

into the address box and i want it to look like this
Level 1 The Building Nowhere Street in the report

Please help.

Thanks
JulieD
Click to expand...
Click to expand...
Click to expand...
 
Hi Cheryl

Thanks - this seems to work (added an IIF statement in to deal with CHR10
not being found.)

Cheers
JulieD

Cheryl Fischer said:
Yes, it does make a difference that you are using Access 97, as the
Replace() function does not exist in that version. For Access 97, I would
use two calculated fields in the query, as follows:

AddrLine1: Left([TwoLineAddr],InStr([TwoLineAddr],Chr(10))-2)

and

AddrLine2: Mid([TwoLineAddr],InStr([TwoLineAddr],Chr(13))+2)

Just tested this out and it will split correctly.

--
Cheryl Fischer
Law/Sys Associates
Houston, TX

JulieD said:
Hi Cheryl

i'm getting an "Undefined function "Replace" in expression" error message
i'm using Access 97, if that makes a difference.

Appreciate your help.

Regards
JulieD

Cheryl Fischer said:
Julie,

You have discovered why it is not a good idea to allow this sort of data
entry; your two-line address field should be two fields.

That having been said, you will need to write a query, if you have not
already done so, and insert the following calculated field:

OneLineAddr: Replace(Replace([TwoLineAddr],Chr(10)," "),Chr(13),"")

Replace the field "TwoLineAddr" with the actual name of your field.

hth,
--
Cheryl Fischer
Law/Sys Associates
Houston, TX

hi all

is this possible (and, if so, how can i achieve it???)
i have a form which allows the user to enter an addres into text box that
allows two or more lines of text, suburb, state & postcode are
Click to expand...
Click to expand...
stored
in the
text
Click to expand...
Click to expand...
 
Back
Top