Insert a character into a string

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Samantha - 08 Nov 2005 20:02 GMT

Hello,
I need to insert a hyphen into a text field (Field1) in a table.
For example, the data for Field1 = "1234567". I want to insert a hyphen
after the 3rd character, so Field1 = "123-4567". Is there a built-in function
in Access 2000? Any help is very much appreciated.

Reply to this Message

Klatuu - 08 Nov 2005 20:09 GMT

Field1 = replace(Field1,mid(Field1,3,1),mid(Field1,3,1) & "-")

> Hello,
> I need to insert a hyphen into a text field (Field1) in a table.
> For example, the data for Field1 = "1234567". I want to insert a hyphen
> after the 3rd character, so Field1 = "123-4567". Is there a built-in function
> in Access 2000? Any help is very much appreciated.

Reply to this Message

Marshall Barton - 08 Nov 2005 20:20 GMT

>I need to insert a hyphen into a text field (Field1) in a table.
>For example, the data for Field1 = "1234567". I want to insert a hyphen
>after the 3rd character, so Field1 = "123-4567". Is there a built-in function
>in Access 2000? Any help is very much appreciated.

Not that I know of, but you can use an expression in a query
field:

IIf(Mid(field1, 4, 1) <> "-", Left(field1, 3) & "-" &
Mid(field1, 4), field1)

Signature

Marsh
MVP [MS Access]

Reply to this Message

Samantha - 08 Nov 2005 21:59 GMT

It works! thank you so much!.

> >I need to insert a hyphen into a text field (Field1) in a table.
> >For example, the data for Field1 = "1234567". I want to insert a hyphen
[quoted text clipped - 6 lines]
> IIf(Mid(field1, 4, 1) <> "-", Left(field1, 3) & "-" &
> Mid(field1, 4), field1)

Reply to this Message