RE: Switch formula in query results in "Text too long to be edited."
- From: Klatuu <Klatuu@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Mon, 28 Apr 2008 15:13:00 -0700
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
--
Dave Hargis, Microsoft Access MVP
"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))
- References:
- Prev by Date: RE: parameter with iif statement in query criteria
- Next by Date: Re: parameter with iif statement in query criteria
- Previous by thread: RE: Switch formula in query results in "Text too long to be edited."
- Next by thread: Re: Switch formula in query results in "Text too long to be edited."
- Index(es):
Relevant Pages
|
