Separate values in a field with hyphens

  • Thread starter Thread starter Jeannette
  • Start date Start date
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
 
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.
 
try to use this


=Left([text3],2) & "-" & Mid([text3],3,2) & "-" & Mid(
[text3],5,4) & "-" & Right([text3],1)
 
Jeannette said:
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
Click to expand...

I would suggest that most of the time it would be better to change only
the display of the numbers and not the numbers.

BTW is the filed a number type or a text type? Do they all have the
same number of digits? Are there any leading zeros?
 
Back
Top