Concatenate four separate fields into one field

  • Thread starter Thread starter Bob Corcoran
  • Start date Start date
B

Bob Corcoran

Houe Number, Street Diretion, Street, into Address\

The House Number is set up on five characters. 00102 is to
become 102 Henry Street. How do i stripout the inital
two characters or two zeros?
 
Bob,

For what you are asking, you could do that with...

Mid([House Number],3)

but, isn't what you really need is how to strip any leading
zeros out no matter how many there are? I would think that
you would have a variety of lengths. In that case you would
want to use...

Val([House Number])

So now you have

Address = Val([House Number]) & " " & [Street Direction] & "
" & [Street]

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
 
Houe Number, Street Diretion, Street, into Address\

The House Number is set up on five characters. 00102 is to
become 102 Henry Street. How do i stripout the inital
two characters or two zeros?
Click to expand...

Your ADDRESS field *should not exist* in your table. Instead calculate
it on the fly in a Query. You can use the Val() function to convert
the text string House Number field to a number - if the House Number
field doesn't have address like "212 1/2" or "331B" or "Bush House"!
Does it?

Try

Address: Val([House Number]) & (" " + [Street Direction]) & " " &
[Street]

I'm assuming that the five character address field might have zero,
one, two or three leading zeros. If it's ALWAYS exactly two leading
zeros, you could use Mid([Address], 3) instead, but that's not very
likely!
 
Back
Top