Address problem

  • Thread starter Thread starter ian
  • Start date Start date
I

ian

Hi,

Thanks for your help in advance.

I have a clients address in 6 fields, however I would
like to put it into 1 field for printing.

I have name, add1,add2, city, state, zip and can get into
one line within the field but i need each to start on a
separte line witin the field.

eg

Name = Mr Jack Benny
Add1 = 123 Street
Add2 = Newton
City = Richmond
State = Vermont
Zip = VT

Becomes:

AddP = Mr Jack Benny
123 Street
Newton
Richmond
Vermont
VT


So far I have got

AddP = Mr Jack Benny 123 Street Newton Richmond Vermont VT
 
Try this in the Control Source of your text box:

=[Name] + Chr(13) + Chr(10) &
[Add1] + Chr(13) + Chr(10) &
[Add2] + Chr(13) + Chr(10) &
[City] + Chr(13) + Chr(10) &
[State] + Chr(13) + Chr(10) & [Zip]

The mixture of concatenation operators (+ and &) should produce correct
results if a field is Null, since:
"A" & Null => "A"
"A" + Null => Null
 
Alan thanks it works.

Just one problem it leaves a gap in the address field how
do i omit a blank field

Thanks

Ian




-----Original Message-----
Try this in the Control Source of your text box:

=[Name] + Chr(13) + Chr(10) &
[Add1] + Chr(13) + Chr(10) &
[Add2] + Chr(13) + Chr(10) &
[City] + Chr(13) + Chr(10) &
[State] + Chr(13) + Chr(10) & [Zip]

The mixture of concatenation operators (+ and &) should produce correct
results if a field is Null, since:
"A" & Null => "A"
"A" + Null => Null

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for your help in advance.

I have a clients address in 6 fields, however I would
like to put it into 1 field for printing.

I have name, add1,add2, city, state, zip and can get into
one line within the field but i need each to start on a
separte line witin the field.

eg

Name = Mr Jack Benny
Add1 = 123 Street
Add2 = Newton
City = Richmond
State = Vermont
Zip = VT

Becomes:

AddP = Mr Jack Benny
123 Street
Newton
Richmond
Vermont
VT


So far I have got

AddP = Mr Jack Benny 123 Street Newton Richmond
Click to expand...
Vermont VT


.
Click to expand...
 
Where is the blank? Between lines? Between this field and the next one?

You could try swapping some of the + and & opearators, e.g.:
=[Name] & Chr(13) + Chr(10) +
[Add1] & Chr(13) + Chr(10) +
[Add2] & Chr(13) + Chr(10) +
[City] & Chr(13) + Chr(10) +
[State] & Chr(13) + Chr(10) + [Zip]

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Ian said:
Alan thanks it works.

Just one problem it leaves a gap in the address field how
do i omit a blank field

Thanks

Ian




-----Original Message-----
Try this in the Control Source of your text box:

=[Name] + Chr(13) + Chr(10) &
[Add1] + Chr(13) + Chr(10) &
[Add2] + Chr(13) + Chr(10) &
[City] + Chr(13) + Chr(10) &
[State] + Chr(13) + Chr(10) & [Zip]

The mixture of concatenation operators (+ and &) should produce correct
results if a field is Null, since:
"A" & Null => "A"
"A" + Null => Null

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for your help in advance.

I have a clients address in 6 fields, however I would
like to put it into 1 field for printing.

I have name, add1,add2, city, state, zip and can get into
one line within the field but i need each to start on a
separte line witin the field.

eg

Name = Mr Jack Benny
Add1 = 123 Street
Add2 = Newton
City = Richmond
State = Vermont
Zip = VT

Becomes:

AddP = Mr Jack Benny
123 Street
Newton
Richmond
Vermont
VT


So far I have got

AddP = Mr Jack Benny 123 Street Newton Richmond
Click to expand...
Vermont VT


.
Click to expand...
Click to expand...
 
Hi Ian

If you put the parts of your expression that use + in parentheses, then it
will force them to be evaluated first.
Then, for example, if [Add2] is null, then:
([Add2] + Chr(13) + Chr(10)) will also be null, and will not generate a
blank line.

So, what you need is:
=([Name] + Chr(13) + Chr(10)) &
([Add1] + Chr(13) + Chr(10)) &
([Add2] + Chr(13) + Chr(10)) &
([City] + Chr(13) + Chr(10)) &
([State] + Chr(13) + Chr(10)) & [Zip]


--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand
Alan thanks it works.

Just one problem it leaves a gap in the address field how
do i omit a blank field

Thanks

Ian




-----Original Message-----
Try this in the Control Source of your text box:

=[Name] + Chr(13) + Chr(10) &
[Add1] + Chr(13) + Chr(10) &
[Add2] + Chr(13) + Chr(10) &
[City] + Chr(13) + Chr(10) &
[State] + Chr(13) + Chr(10) & [Zip]

The mixture of concatenation operators (+ and &) should produce correct
results if a field is Null, since:
"A" & Null => "A"
"A" + Null => Null

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for your help in advance.

I have a clients address in 6 fields, however I would
like to put it into 1 field for printing.

I have name, add1,add2, city, state, zip and can get into
one line within the field but i need each to start on a
separte line witin the field.

eg

Name = Mr Jack Benny
Add1 = 123 Street
Add2 = Newton
City = Richmond
State = Vermont
Zip = VT

Becomes:

AddP = Mr Jack Benny
123 Street
Newton
Richmond
Vermont
VT


So far I have got

AddP = Mr Jack Benny 123 Street Newton Richmond
Click to expand...
Vermont VT


.
Click to expand...
Click to expand...
 
Back
Top