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

[anwest75]

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))

 

[KARL DEWEY]

I would take a different approach. Add a flag field to mark records
extracted.
Do it in batches -- those with dashes, with periods, spaces, and other
separators. As I run a query to eatract a batch then update the flag field.
Next extraction use criteria so as not to pull the flagged records.

 

[Klatuu]

I suggest writing a function to parse the phone number field and return it in
the desired format. To call a function from a query, it has to be a Public
function in a standard module. Then use a calculated field in the query to
return the value:

in the query builder:
DPSwitch: ParsePhone([WK_PHONE_NBR])

or in SQL:

SELECT ParsePhone([WK_PHONE_NBR]) As DPSwitch

 

[John Spencer]

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
'====================================================

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))