String Manipulation help needed

J

jamesfreddyc

I have a text field, but consists of numbers -- always the same number of
characters in length. For example,

"0001040047"

I need to change all records in this field -- I need 2 seperate "-"
characters after the first 4 characters, then again before the last 4
characters. Like this,

"0001-04-0047"

Any help is much appreciated!!!

j
 
J

John Spencer

Use an expression like

LEFT([YourText],4) & "-" & Mid([YourText],5,2) & "-" & Mid([YourText],7)

If YourText field can be null or a zero-length string then use
IIF([YourText] & "" = "",[YourText],LEFT([YourText],4) & "-" &
Mid([YourText],5,2) & "-" & Mid([YourText],7) )

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
J

John Spencer

OR even easier use
Format([YourText],"@@@@-@@-@@@@")

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
J

jamesfreddyc

I tried to set the "Format" for the table. Yes, it visually changes the
apperance of the values, but the underlying data has not changed at all.

j

John Spencer said:
OR even easier use
Format([YourText],"@@@@-@@-@@@@")

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

jamesfreddyc said:
I have a text field, but consists of numbers -- always the same number of
characters in length. For example,

"0001040047"

I need to change all records in this field -- I need 2 seperate "-"
characters after the first 4 characters, then again before the last 4
characters. Like this,

"0001-04-0047"

Any help is much appreciated!!!

j
Click to expand...
Click to expand...
 
J

John Spencer

In an empty field "Cell" in the query enter the expression. Replacing
YourText with the name of the field. If you have two fields with the same
field name, but a different table, you must use [Name of Table].[Name of
Field]

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

jamesfreddyc said:
Thanks John...

How do I fit this into a Query?

j

John Spencer said:
Use an expression like

LEFT([YourText],4) & "-" & Mid([YourText],5,2) & "-" & Mid([YourText],7)

If YourText field can be null or a zero-length string then use
IIF([YourText] & "" = "",[YourText],LEFT([YourText],4) & "-" &
Mid([YourText],5,2) & "-" & Mid([YourText],7) )

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

jamesfreddyc said:
I have a text field, but consists of numbers -- always the same number
of
characters in length. For example,

"0001040047"

I need to change all records in this field -- I need 2 seperate "-"
characters after the first 4 characters, then again before the last 4
characters. Like this,

"0001-04-0047"

Any help is much appreciated!!!

j
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

If you want to permanently change the data, then you would need to use an
UPDATE query.

UPDATE YourTable
SET [Your field] = Format([Your Field],"@@@@-@@-@@@@")
WHERE [Your Field] & "" <> ""

In the query grid
-- Add your table
-- Add your field
-- SELECT Query: Update from the menu
-- Type the following into the UPDATE TO
Format([Your Field],"@@@@-@@-@@@@")
-- SELECT Query: Run from the menu

BEFORE YOU DO THIS, make a backup of your data. If this goes wrong, you
will have no other way to recover.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

jamesfreddyc said:
I tried to set the "Format" for the table. Yes, it visually changes the
apperance of the values, but the underlying data has not changed at all.

j

John Spencer said:
OR even easier use
Format([YourText],"@@@@-@@-@@@@")

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

jamesfreddyc said:
I have a text field, but consists of numbers -- always the same number
of
characters in length. For example,

"0001040047"

I need to change all records in this field -- I need 2 seperate "-"
characters after the first 4 characters, then again before the last 4
characters. Like this,

"0001-04-0047"

Any help is much appreciated!!!

j
Click to expand...
Click to expand...
Click to expand...
 
Top