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

Tech-Archive recommends: Speed Up your PC by fixing your registry

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: Wandering Lines
    ... I'm scared of that "style" thing, John. ... health insurance premiums money and 180 polished manuscript pages ... To make persistent consistent changes, ... learn to work with the underlying styles, not the direct formatting that can ...
    (microsoft.public.mac.office.word)
  • Re: Wandering Lines
    ... To make persistent consistent changes, ... learn to work with the underlying styles, not the direct formatting that can ... especially if it's a PC -- even before the fonts and leading are changed the ... manuscripts are formatted at 25 lines per page. ...
    (microsoft.public.mac.office.word)
  • Re: Wandering Lines
    ... And for people who develop seriously complex or long documents, formatting ... health insurance premiums money and 180 polished manuscript pages ... To make persistent consistent changes, ... Clive Huggan wrote: ...
    (microsoft.public.mac.office.word)
  • Re: Formatting when working with phone numbers in queries
    ... Since you need to search on Area Code, it might be a good idea to break out ... You could run an update query to get the consistent formatting. ... Some Reasons to retain formatting (and not use an input mask) ...
    (microsoft.public.access.queries)
  • Re: Formatting when working with phone numbers in queries
    ... Since you need to search on Area Code, it might be a good idea to break out ... You could run an update query to get the consistent formatting. ... Some Reasons to retain formatting (and not use an input mask) ...
    (microsoft.public.access.queries)