Re: Switch formula in query results in "Text too long to be edited."

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

It appears to me as if you are extracting only ten digits from the phone numbers. If that is the case I would use a routine to only extract the digits in the number.

Here is a function that will take a string and return only the number characters in the string. Copy the function and paste it into a VBA module and save the module with a name like mod_StringFunctions

Field: WorkPhone: fStripToNumbersOnly([WK_PHONE_NBR])


Public Function fStripToNumbersOnly(ByVal varText As Variant) As String
'Takes input and returns only the numbers in the input. Strips out
'all other characters. Handles nulls, dates, numbers, and strings.

Const strNumbers As String = "0123456789"
Dim strOut As String
Dim intCount As Integer

If Len(varText & "") = 0 Then
strOut = ""

Else
varText = varText & ""
For intCount = 1 To Len(varText)
If InStr(1, strNumbers, Mid(varText, intCount, 1)) > 0 Then
strOut = strOut & Mid(varText, intCount, 1)
End If
Next intCount
End If

fStripToNumbersOnly = strOut

End Function


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


anwest75 wrote:
I am extracting phone numbers from our HRIS system and there are several
types of formatting on the phone numbers. I need the format to be consistent
- xxxxxxxxx - so I have constructed a SWITCH formula to address the
reformatting needs instead of using a series of nested IIFs. Unfortunately,
the formula produces a "test is too long to be formatted" error when I try to
paste it from WordPad to an A2002 query. I'm not sure if I'm missing a comma
or something very simple, or if the field truly has a limit that I've
exceeded. Any thoughts/comments/help is appreciated! Thanks!!

Here is the formula:

DPSwitch: Switch(Len(Trim([WK_PHONE_NBR]))=10,Trim([WK_PHONE_NBR]),
Len(Trim([WK_PHONE_NBR]))=11,Mid([WK_PHONE_NBR],1,3)+Mid([WK_PHONE_NBR],5,7),
Len(Trim([WK_PHONE_NBR]))=12 And InStr(1,[WK_PHONE_NBR],"(")=1,Mid(
[WK_PHONE_NBR],2,3)+Mid([WK_PHONE_NBR],6,7),
Len(Trim([WK_PHONE_NBR]))=12 And InStr(1,[WK_PHONE_NBR],"(")<>1,Mid(
[WK_PHONE_NBR],1,3)+Mid([WK_PHONE_NBR],5,3)+Mid([WK_PHONE_NBR],9,4),
Len(Trim([WK_PHONE_NBR]))=13 And InStr(1, [WK_PHONE_NBR],"(")<>1,Null,
Len(Trim([WK_PHONE_NBR]))=13 And InStr(1,Trim([WK_PHONE_NBR]),"(")=1,Mid(
[WK_PHONE_NBR],2,3)+Mid([WK_PHONE_NBR],6,2)+Mid([WK_PHONE_NBR],10,4),
Len(Trim([WK_PHONE_NBR]))=14 and Instr(1,[WK_PHONE_NBR],"1")=1,Mid(
[WK_PHONE_NBR],3,3)+Mid([WK_PHONE_NBR],7,3)+Mid([WK_PHONE_NBR],11,4),
Len(Trim([WK_PHONE_NBR]))=14 and Instr(1, [WK_PHONE_NBR],"(")=1 and Instr(1,
[WK_PHONE_NBR],")")<>5,Null,
Len(Trim([WK_PHONE_NBR]))=14 and Instr(1,[WK_PHONE_NBR],"(")=1 and Instr(1,
[WK_PHONE_NBR],")")=5,Mid([WK_PHONE_NBR],2,3)+Mid([WK_PHONE_NBR],7,3)+Mid(
[WK_PHONE_NBR],11,4),
Len(Trim([WK_PHONE_NBR]))=15,Mid([WK_PHONE_NBR],2,3)+Mid([WK_PHONE_NBR],8,3)
+Mid([WK_PHONE_NBR],12,4))

.

Relevant Pages

  • Re: Removing Formatting Symbols from Phone numbers
    ... Const strNumbers As String = "0123456789" ... Dim strOut As Variant ... For intCount = 1 To Len ...
    (microsoft.public.access.queries)
  • Re: How do I delete characters using a query?
    ... You could use a custom VBA function to do this. ... Public Function fStripToSpecifiedOnlyAs String ... Dim strOut As String ... For intCount = 1 To Len ...
    (microsoft.public.access.queries)
  • Re: conver text to number
    ... The "expression" is a vba function that you should copy and paste into a vba module and save it with a name other than fStripToNumbersOnly. ... Here is a function that can be used to return a string ... Dim strOut As String ... For intCount = 1 To Len ...
    (microsoft.public.access.queries)
  • Re: Update qry removing ONLY Alpha
    ... Now you can use the function in an update query if you want to change the ... Dim strOut As String ... For intCount = 1 To Len ...
    (microsoft.public.access.queries)
  • Re: How to convert extra long strings into their equivalent Hex Strings in VBA (Word 2K)
    ... numbers (upto 18 digits max) into its equivalent Hex String ... Public Function ExpressServiceCode(ByVal ServiceTag As String) As String ... 'the number dblTemp in the specified base, ... Dim lngTemp As Long ...
    (microsoft.public.vb.general.discussion)