String Manipulation help needed

[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

 

[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
..

 

[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
..

 

[jamesfreddyc]

Thanks John...

How do I fit this into a Query?

j

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
..

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

 

[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

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
..

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

 

[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
..

Thanks John...

How do I fit this into a Query?

j

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
..

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

 

[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
..

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

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
..

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