Searching/Filtering for "###-####" within a string

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to set up something where,
If a record has a string that contains a phone number,
it will deliminate everything else and leave just the phone number remaining.

A query that checked the entire field would be nice.
It would have something like,
"Like *###-####*"
right?

that would just filter out the records that didn't have phone#'s tho.
Is there a way to,
If string contains ###-####, Then Return just ###-####

I would use Left$/Mid$ but there's an undetermined no. of digits to the left
and right of the potential phone #.

Any advice would be very very very helpful!
Thanks
 
Will there ever be two phone numbers in the data? If so, I think you should
write a function in VB and examine individual characters. Otherwise, you can
get pretty close with a query if your data format is consistent and the field
contains only one dash.

PhoneNum: Mid([Field],Instr([Field],"-")-3,8)

I admit that this isn't very robust but it might work for your purposes. hth
 
I want to set up something where,
If a record has a string that contains a phone number,
it will deliminate everything else and leave just the phone number remaining.

A query that checked the entire field would be nice.
It would have something like,
"Like *###-####*"
right?

that would just filter out the records that didn't have phone#'s tho.
Is there a way to,
If string contains ###-####, Then Return just ###-####

I would use Left$/Mid$ but there's an undetermined no. of digits to the left
and right of the potential phone #.
Click to expand...

I don't think there's any easy way to do this, since InStr() can only find
literal characters, not wildcards - so there's no simple way to determine the
start of the telephone number.

One question: what about numbers like 800-555-5555? Or (888)888-0000? Or
222-2222x234? I fear that this may require a USB interface (Using Someone's
Brain) to be effective!

You can probably write a VBA function to step through the (memo??) field
searching for strings. It might be worth doing a Google search for "Regular
Expressions" or "RegEx" in conjunction with Access - there are good
regular-expression toolkits that can do this kind of thing, but I haven't used
them.

John W. Vinson [MVP]
 
I meant to say I needed (###) ### (or ###-###, or ######) of (###) ###-####.

I was thinking something like,

check first digit in a string,
*you do that by treating it like an array? Or does that imply there are
multiple strings?*

Then there's a "IsNum(MyString[0])" function right?
If not, something like,
If MyString[0] = "1" Then
...
Else
If MyString[0] = "2" Then
etc

(a "0" for FirstD skipping the loop)

Then if IsNum returns false, cut the first digit off MyString.
*I'm not sure how to remove the first digit of a string though-- unless
there's a way to find how many digits are in a string, then Right$(that many
digits - 1)*

THEN loop that process til IsNum returns true,
if it's true then,
If IsNum(MyString[1]) & (MyString[2]) also return true, Then MyString[3]
either returns true for IsNum or " " or "-" or ")",
THEN
MyString[4] needs to be either " " or #.
If MyString[4] = " ", then [5], [6], & [7] must = #.

Then to make sure it's a phone # and not some other number,
If [8] <> #, then SequenceIsPhoneNoBoolean = true
If [8] = #, Then [9], [10], [11], [12] must = #
If true, then SequenceEqualsPhoneNoBoolean = true

If [4] = "#", then [5] & [6] must = #
Then repeat the above 4 lines.

It's assumed that there'll only be 1 phone # in the String, so once
SequenceEqualsPhoneNoBoolean returns true, the rest of the string can be
deliminated.
*Left$(MyString, 6 or 7 or 8 or 10 or 11 or 12 based on booleans set in
the above coding)*

Then I'd cut out all the non-numbers from the string so it looked like
###### or ##########. I'm pretty sure I know how to do that though.


Any Advice on the above strategy for the code would be great--
like how to select the first digit in a string,
how to IsNum,
or anything that won't work up there?

thanks SOO much for any help.
 
Back
Top