Separate values in a field with hyphens

J

Jeannette

Hi!

I am currently processing some data in Access. I need to
separate values in a field with hyhens. For example: I
have "123456789" in a [ID] field and I need to add hyphens
in between those numbers so that it would become "12-34-
5678-9". There are a total of 1733403 records in the
table. What would be the easiest way for me to modify the
values in the [ID] field with hyphens? How can that be
done? I'd greatly appreaciate it if you can help me with
this. Thanks in advance.

Jeannette
 
A

Albert D. Kallal

Perhaps in your reports,a nd on your screens where you DISPLAY the data, you
stick in the hyphens,and not worry about modify the data?

If you place a control on a form (or a report), you can use the format
field.

So, you could go:

@@-@@-@@@@-@

Thus, if you later add new data, or import more data..you will NEVER have to
bother with inserting the hyphens.

The above concept is also recommend for stuff like phones numbers etc, and
thus you don't have extra spaces, hyphens, brackets etc, and this
facilitates searching and sorting the data since you never store that junk
in the data field.
 
X

x

try to use this


=Left([text3],2) & "-" & Mid([text3],3,2) & "-" & Mid(
[text3],5,4) & "-" & Right([text3],1)
 
Top