Specific formatting

[WaltLukeIII]

I have two fields in a table one that is a phone number. and the other which tells what kind of phone number
9 digit
9 digit with a 3 digit extension
15 digit phone number
ect...
I have setup a table with the description of the phone number and a format for the numbers.

I need to take that format and apply it to the other table with the numbers in it.

for example
In table One I have the number "123456789" and the "9 digit" as the type of number.
In the second table I have the "9 digit" name and the format I want used "(@@@) @@@-@@@@".

So I can take the number from the first table and the format to use from the second table and come up with
"(123) 456-789" to be used elsewhere

I trust this makes sense.


Walt III
SAElukewl@netscape.net

http://www.muc.edu/~lukewl

 

[Tranman]

Walt,
Here's a snippet that shows how to use a format stored in one table to format a field stored in another (approximately what you want to do). It was not clear to me whether you are wanting to batch update all of the numbers to the appropriate format, or just look them up and use the formatted numbers somewhere else, leaving them stored the way they are.

If you will let me know, I'll be glad to bang out the looping code if you want.

Private Sub testsub()
Dim rs As New ADODB.Recordset
Dim strFormatted As String
rs.Open "SELECT tb1.*, tb2.* FROM tb1 INNER JOIN tb2 ON tb1.DESCR = tb2.DESCR;", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
strFormatted = Format(rs.Fields("tb1.PHONE_NBR"), rs.Fields("tb2.FORMAT"))
rs.Close
Set rs = Nothing
End Sub


Tranman

 

[smiley0q0]

This is how i do it.

in the input mask put \(###") "###" - "####

you can mess around with it and do your own spacing etc.

hope that helps