I'm struggling to follow this guide...

[templeowls]

I am very much an access novice. I am struggling to figure out how to follow this guide. I've been working on it all day. I'm not sure how to actually implement the function.

I have a table full of phone numbers and the code cited (also below) is exactly what I need. I just don't know how to actually implement it (I'm an idiot). Do I put that function into module? After that, then what? Do I use a query to call it? How exactly is the query coded? My table name is just tblCustomers.

If anyone could kindly walk me through it, I'd be incredibly grateful.



Thanks

Function ExtractPhoneNum(s As String) As String   Dim strS As String


   strS = Replace(Replace(s, "(", ""), ")", "")
   If InStr(strS, "x") <> 0 Then strS = Left(strS, InStr(strS, "x") - 1)
   If InStr(strS, "+") <> 0 Then strS = Mid(strS, InStr(strS, " ") + 1)
   ExtractPhoneNum = Val(strS) 
End Function

[pbaldy]

Yes, you would copy that function into a standard module (not behind a form or report). You could call it from anywhere, but in a query:

SELECT YourFieldName, ExtractPhoneNum(YourFieldName) As Test
FROM tblCustomers

[orange]

How about posting some samples of the phone numbers involved?
Did you see the link to International calling codes from Gustav at StackOverflow?

[ssanfu]

Here is the function from Post #1 with all of the HTML formatting removed (I hope)

Function ExtractPhoneNum(s As String) As String
   Dim strS As String

   strS = Replace(Replace(s, "(", ""), ")", "")
   If InStr(strS, "x") <> 0 Then strS = Left(strS, InStr(strS, "x") - 1)
   If InStr(strS, "+") <> 0 Then strS = Mid(strS, InStr(strS, " ") + 1)
   ExtractPhoneNum = Val(strS) 
End Function

[templeowls]

Here is the function from Post #1 with all of the HTML formatting removed (I hope)

Function ExtractPhoneNum(s As String) As String
   Dim strS As String

   strS = Replace(Replace(s, "(", ""), ")", "")
   If InStr(strS, "x") <> 0 Then strS = Left(strS, InStr(strS, "x") - 1)
   If InStr(strS, "+") <> 0 Then strS = Mid(strS, InStr(strS, " ") + 1)
   ExtractPhoneNum = Val(strS) 
End Function

Omg I didn't even realize I left the HTML in there. Sorry about that. I updated my post.

@pbaldy that solution worked! One issue though with the code itself though. Hoping someone could help:

It doesn't work with dashes. If a phone number is +1 (234) 567-8890, it is showing as 234567. It is correctly getting rid of the dash but then not including the 4 digits afterwards.

[CJ_London]

Always better to show the actual code you are actually using as the code provided does not remove '-'

Always recommend when you pick up a bit of code from the web, you spend a little bit of time to understand what it is actually doing by stepping though and checking values as you go

[templeowls]

Always better to show the actual code you are actually using as the code provided does not remove '-'

Always recommend when you pick up a bit of code from the web, you spend a little bit of time to understand what it is actually doing by stepping though and checking values as you go

That is the actual code I'm using. I did what pbadly suggested. I dropped that exact code into a module then created a select query. The module code and select query code are below. I've tested it multiple times; any time there's a dash, it removes it along with everything that comes after it.

Function ExtractPhoneNum(s As String) As String
   Dim strS As String
   strS = Replace(Replace(s, "(", ""), ")", "")
   If InStr(strS, "x") <> 0 Then strS = Left(strS, InStr(strS, "x") - 1)
   If InStr(strS, "+") <> 0 Then strS = Mid(strS, InStr(strS, " ") + 1)
   ExtractPhoneNum = Val(strS)
End Function

SELECT PhoneNumber, ExtractPhoneNum([PhoneNumber]) AS Test FROM tblCustomers;

[Micron]

Because Val stops at the first character that cannot be recognized as a number and the last - is not being removed first. Either nest another Replace function to remove the last - or create a loop that does it until Instr = 0.
EDIT - Actually, I'm not sure if spaces count wrt Val.

[CJ_London]

suggest check the before and after values as your comment 'any time there's a dash, it removes it along with everything that comes after it.' doesn't ring true. Think you will find it is not removing everything else but changing what comes before.

if you step through the code, what is the value of strS at each stage?

edit: - micron has given you a clue

[templeowls]

Because Val stops at the first character that cannot be recognized as a number and the last - is not being removed first. Either nest another Replace function to remove the last - or create a loop that does it until Instr = 0.
EDIT - Actually, I'm not sure if spaces count wrt Val.

Thanks. I'll try to figure it out

[templeowls]

I was able to figure it out using the below code. If you look at the guide I originally cited, the poster didn't have dashes in their numbers so the code never solved for that.

Thanks for the help everyone!

Function ExtractPhoneNum(s As String) As String   Dim strS As String
   strS = Replace(Replace(Replace(s, "(", ""), ")", ""), "-", "")
   If InStr(strS, "+") <> 0 Then strS = Mid(strS, InStr(strS, " ") + 1)
   ExtractPhoneNum = Val(strS)
End Function

[CJ_London]

the other problem you will have with val is it will remove initial 0's

so

val("0234 567 8890")

will return
2345678890

edit: you could just use replace

ExtractPhoneNum = val(replace(replace(Replace(Replace(Replace(s, "(", ""), ")", ""), "-", ""),"+","")," ",""))

[Micron]

Or

Function ExtractPhoneNum(s As String) As String

Do Until InStr(s, "-") = 0 And InStr(s, ")") = 0 And InStr(s, "(") = 0 And InStr(s, "+") = 0
     s = Replace(s, "-", " ")
     s = Replace(s, ")", "")
     s = Replace(s, "(", "")
     s = Replace(s, "+", "")
Loop

If InStr(s, "x") > 0 Then s = Left(s, InStr(s, "x") - 1)
ExtractPhoneNum = s

End Function

Might be easier to add to that vs more nested Replace functions. That also eliminates using val.
EDIT - As is so often the case, desired output not shown as far as I can see.

[CJ_London]

why use a loop? - replace will replace all instances of a character in one go

only time I would use a loop is where you want to replace 2 or more consecutive chars with a single one

Do Until InStr(s, "--") = 0
     s = Replace(s, "--", "-")
Loop

agree re val - it just removes the spaces in this case. Confusing since the function is a string and is being assigned a number

[Micron]

Yes, good point. That's what happens when I rush and have had no coffee yet. I'm about to take care of that right now. So more like?

s = Replace(s, "-", " ")
s = Replace(s, ")", "")
s = Replace(s, "(", "")
s = Replace(s, "+", "")

If InStr(s, "x") > 0 Then s = Left(s, InStr(s, "x") - 1)
ExtractPhoneNum = s

Note that I dropped the string variable as it's not needed.