Add a space to 11 numbers

[Guest]

I have a column of UK TELEPHONE no's with 11 digits in a line. I would like
to add a space between digit 5 and digit 6
12345678911
12345 678911

 

[Rick B]

This is done with the "format" in your forms and reports.

 

[Guest]

In the query, add a field

NEW_UK_TELEPHONE: Left([UK_TELEPHONE],5) & " " & Mid([UK_TELEPHONE],6)

Or In SQL
Select Left([UK_TELEPHONE],5) & " " & Mid([UK_TELEPHONE],6) As
NEW_UK_TELEPHONE From TableName

 

[Nick Coe \(UK\)]

Air code in a form class:- Assuming phone no field is
called PhoneNum

Dim strLeftPart, strRightPart, strOrig, strFixedUp as string

strOrig = Me.PhoneNum

strLeftPart = Mid(strOrig,1,5)
strRightPart = Mid(strOrig,6)
strFixedUp = strLeftPart & " " & strRightPart

'strFixedUp now contains the amended value.
Me.PhoneNum = strFixedUp

Alternatively you could run an update query against the
table with something like:

UPDATE tblPhoneNumbers
SET tblPhoneNumbers.HomePhone = Mid([HomePhone],1,5) & " " &
Mid([HomePhone],6);

You'll need to substitute your own table and field names.

--
Nick Coe (UK)
http://www.alphacos.co.uk/ AccHelp + pAnimal
http://www.pjandcoe.co.uk/ Online Store
http://www.mrcomputersltd.com/ Repairs Upgrades

In Paul K. typed:

 

[aaron.kempf]

more importantly; cant you just use a format string 00000 000000?

 

[John Vinson]

I have a column of UK TELEPHONE no's with 11 digits in a line. I would like
to add a space between digit 5 and digit 6
12345678911
12345 678911

This should certainly be a Text field, not a number.

You can use the suggestions posted in this thread to temporarily or
permanently insert a space into the table (if it's a text field -
numbers of course don't allow spaces); or you can simply set the
Format property of the field to

@@@@@ @@@@@@


John W. Vinson[MVP]

 

[Nick Coe \(UK\)]

Yup.

--
Nick Coe (UK)
http://www.alphacos.co.uk/ AccHelp + pAnimal
http://www.pjandcoe.co.uk/ Online Store
http://www.mrcomputersltd.com/ Repairs Upgrades

In
(e-mail address removed) typed: