Input Mask

[OldManEd]

I have created a function that returns 2 or more cell phone number.
Something like:

2031236789
2031239876

The actual string returned is '2031236789 & vbCrLf & 2031239876'

How do I format these as a telephone number '(999) 999-9999' ???

The function searches (loops) a table using an ID to find members of a
group, each having a different cell phone number. Group size can be from 2
to 7. One string is created for all the numbers with a vbCRLf between each;
the final vbCrLf is deleted.

The data source is a table. The Cell Phone numbers in that data table are
already formated but don't carry over to the report that used the cell phone
function described above because, I think, the vbCrLF interfers.

Thanaks,
OMEd

Thanks for help.
OMEd

 

[Jim Burke in Novi]

After stripping off an individual phone #, use this input mask:

!\(999") "000\-0000;;_

 

[OldManEd]

How? Where? I already use this mask in data table.

My function is

Dim MyConnection As ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSQL As String
Dim strConcat As String
Dim lngLen As Long
Const strcSep = vbCrLf

If IsNumeric(FamilyLookUp) Then
Set MyConnection = CurrentProject.Connection
strSQL = "SELECT CellPhone FROM tblAllIndividuals2000 WHERE FamilyID = " &
FamilyLookUp & ";"
rs.ActiveConnection = MyConnection
rs.Open strSQL
With rs
Do While Not rs.EOF
strConcat = strConcat & rs![CellPhone] & strcSep
.MoveNext
Loop
End With

' I tried here: strConcat.InputMask = "(999) 999-9999 That doesn't work.

rs.Close
End If

lngLen = Len(strConcat) - Len(strcSep)
If lngLen > 0 Then
CPhone = Left(strConcat, lngLen)
Else
CPhone = Null
End If

' I tried here: CPhone.InputMask = "(999) 999-9999" This didn't work.


End Function

I also set the InputMask property of the report 's text box that generates
this to your value. That doesn't work.

What will work?? What do you mean by, 'stripping off.....'???

OMEd

 

[fredg]

I have created a function that returns 2 or more cell phone number.
Something like:

2031236789
2031239876

The actual string returned is '2031236789 & vbCrLf & 2031239876'

How do I format these as a telephone number '(999) 999-9999' ???

The function searches (loops) a table using an ID to find members of a
group, each having a different cell phone number. Group size can be from 2
to 7. One string is created for all the numbers with a vbCRLf between each;
the final vbCrLf is deleted.

The data source is a table. The Cell Phone numbers in that data table are
already formated but don't carry over to the report that used the cell phone
function described above because, I think, the vbCrLF interfers.

Thanaks,
OMEd

Thanks for help.
OMEd

You haven't posted the function code, so I don't really know how you
are doing this.

And no ... the vbCrLf is not interfering with the formatting of the
phone number. Your Phone number value is NOT formatted. What you are
seeing in table or form view is either the overlay of an Input Mask
(which is not properly set up to be saved with the with the data) or
you have the control's format property set to your expected (xxx)
xxx-xxxx format.

What you are getting in your function is the actual data as stored.

Format the phone number in the function BEFORE you add the vbCrLf.
Use something like this to format the number:

MyVariable = MyVariable & Format(Phone],"(@@@) @@@-@@@@") & vbCrLf

then get the next number.

 

[OldManEd]

Fred,
Thanks for comments. I posted the whole function in an earlier message.

I'm using a loop to get all the numbers. So.......do I stick your line in
the loop?

============
Don't understand your comment above saving the input mask. It's part of the
property of the phone field in my data table. I am using the function to
generate part of a report. The property of that text box control is also set
to an input mask. What else can I do?

I have created a function that returns 2 or more cell phone number.
Something like:

2031236789
2031239876

The actual string returned is '2031236789 & vbCrLf & 2031239876'

How do I format these as a telephone number '(999) 999-9999' ???

The function searches (loops) a table using an ID to find members of a
group, each having a different cell phone number. Group size can be from
2
to 7. One string is created for all the numbers with a vbCRLf between
each;
the final vbCrLf is deleted.

The data source is a table. The Cell Phone numbers in that data table
are
already formated but don't carry over to the report that used the cell
phone
function described above because, I think, the vbCrLF interfers.

Thanaks,
OMEd

Thanks for help.
OMEd

You haven't posted the function code, so I don't really know how you
are doing this.

And no ... the vbCrLf is not interfering with the formatting of the
phone number. Your Phone number value is NOT formatted. What you are
seeing in table or form view is either the overlay of an Input Mask
(which is not properly set up to be saved with the with the data) or
you have the control's format property set to your expected (xxx)
xxx-xxxx format.

What you are getting in your function is the actual data as stored.

Format the phone number in the function BEFORE you add the vbCrLf.
Use something like this to format the number:

MyVariable = MyVariable & Format(Phone],"(@@@) @@@-@@@@") & vbCrLf

then get the next number.

 

[fredg]

Fred,
Thanks for comments. I posted the whole function in an earlier message.

I'm using a loop to get all the numbers. So.......do I stick your line in
the loop?

============
Don't understand your comment above saving the input mask. It's part of the
property of the phone field in my data table. I am using the function to
generate part of a report. The property of that text box control is also set
to an input mask. What else can I do?

I have created a function that returns 2 or more cell phone number.
Something like:

2031236789
2031239876

The actual string returned is '2031236789 & vbCrLf & 2031239876'

How do I format these as a telephone number '(999) 999-9999' ???

The function searches (loops) a table using an ID to find members of a
group, each having a different cell phone number. Group size can be from
2
to 7. One string is created for all the numbers with a vbCRLf between
each;
the final vbCrLf is deleted.

The data source is a table. The Cell Phone numbers in that data table
are
already formated but don't carry over to the report that used the cell
phone
function described above because, I think, the vbCrLF interfers.

Thanaks,
OMEd

Thanks for help.
OMEd

You haven't posted the function code, so I don't really know how you
are doing this.

And no ... the vbCrLf is not interfering with the formatting of the
phone number. Your Phone number value is NOT formatted. What you are
seeing in table or form view is either the overlay of an Input Mask
(which is not properly set up to be saved with the with the data) or
you have the control's format property set to your expected (xxx)
xxx-xxxx format.

What you are getting in your function is the actual data as stored.

Format the phone number in the function BEFORE you add the vbCrLf.
Use something like this to format the number:

MyVariable = MyVariable & Format(Phone],"(@@@) @@@-@@@@") & vbCrLf

then get the next number.

strConcat = strConcat & Format(rs![CellPhone] ,"(@@@) @@@-@@@@") &
strcSep

I would suggest that you read Access help on the Input Mask property.
The Input Mask is used to force data ENTRY in a specific manner, not
to display the data in a particular way. The mask is NOT stored with
the data unless you have explicitly coded it to do so.
If you look at your Input Mask property (in the table or on the data
entry form) it probably look like this:
!\(999") "000\-0000;;_
To store the mask with the new data you must use
!\(999") "000\-0000;0;_

To change previously entered data you would need to run an Update
query using the same Format function I gave you.

Update YourTable Set YourTable.[CellPhone] = Format([CellPhone],"(@@@)
@@@-@@@@")

Then change the Mask so that new entries include the mask format.