Formating field to number

[fludan]

Hi

I need to import some data daily from my shopping cart, the data is the phone numbers but I need to format the phone number to a number without spaces, dashes etc..
ex: (000)000-0000 (000) 000-0000 , +00 0000 00000 , 000-000-0000 etc... is this possible.
thanks
frank

 

[PHV]

Something like this ?
newPhone = ""
For i = 1 to Len(oldPhone)
x = Mid(oldPhone, i, 1)
If x >= "0" And x <= "9" Then
newPhone = newPhone & x
End If
Next i

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[fludan]

Thanks it works

 

[fludan]

Is there a way of looking up if the record exists?
Right now the code look to see if the phone number exist in the table customer if the customer dose not exist the code continue but my question is can it look up if the phone number I enter on my form match with the phone number in my database on the server and if it dos not exist it will give me a message.

Thanks

My code is:

Private Sub Command33_Click()

On Error GoTo Err_Command33_Click

DoCmd.Echo False, "Please Wait."
DoCmd.OpenForm "Form4", acViewNormal
If Me![customerID] = Forms![Form4]![phone] Then
DoCmd.Close acForm, "Form4", acSaveYes
DoCmd.Echo True
MsgBox "Already a Customer."


Else
DoCmd.Echo False, "Please Wait."
DoCmd.OpenQuery "ImportCustomer"
DoCmd.DoMenuItem acFormBar, acEditMenu, 9, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.Close acQuery, "ImportCustomer", acSaveYes
DoCmd.OpenTable "1", acViewNormal, acAdd
DoCmd.DoMenuItem acFormBar, acEditMenu, 9, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 3, , acMenuVer70
DoCmd.Close acTable, "1"
DoCmd.OpenTable "1", acViewNormal

DoCmd.OpenForm "Form1"
Forms![Form1]![Field11] = ""
For i = 1 To Len(customerID)
x = Mid(customerID, i, 1)
If x >= "0" And x <= "9" Then
Forms![Form1]![Field11] = Forms![Form1]![Field11] & x
End If
Next i

Forms![Form1]![Field12] = ""
For i = 1 To Len(customerID)
x = Mid(customerID, i, 1)
If x >= "0" And x <= "9" Then

Forms![Form1]![Field12] = Forms![Form1]![Field12] & x
End If
Next i

DoCmd.Close acForm, "Form1", acSaveYes

DoCmd.OpenQuery "q1"
DoCmd.DoMenuItem acFormBar, acEditMenu, 9, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.OpenTable "customer", acViewNormal, acAdd
DoCmd.DoMenuItem acFormBar, acEditMenu, 9, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 3, , acMenuVer70
DoCmd.Close acQuery, "q1"
DoCmd.Close acForm, "Form1"
DoCmd.Close acTable, "customer", acSaveYes




DoCmd.OpenQuery "ImportCustomerShip"
DoCmd.DoMenuItem acFormBar, acEditMenu, 9, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.Close acQuery, "ImportCustomerShip", acSaveYes
DoCmd.OpenTable "3", acViewNormal, acAdd
DoCmd.DoMenuItem acFormBar, acEditMenu, 9, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 3, , acMenuVer70
DoCmd.Close acTable, "3"
DoCmd.OpenTable "3", acViewNormal

DoCmd.OpenForm "Form2"

Forms![Form2]![Field14] = ""
For i = 1 To Len(customerID)
x = Mid(customerID, i, 1)
If x >= "0" And x <= "9" Then
Forms![Form2]![Field14] = Forms![Form2]![Field14] & x
End If
Next i

DoCmd.Close acForm, "Form2"
DoCmd.OpenQuery "q3"
DoCmd.DoMenuItem acFormBar, acEditMenu, 9, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.OpenTable "customership", acViewNormal, acAdd
DoCmd.DoMenuItem acFormBar, acEditMenu, 9, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 3, , acMenuVer70
DoCmd.Close acQuery, "q3"


DoCmd.OpenTable "customership", acViewNormal, acAdd
DoCmd.DoMenuItem acFormBar, acEditMenu, 9, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 3, , acMenuVer70
DoCmd.Close acQuery, "q3"
DoCmd.Close acForm, "Form2"
DoCmd.Close acTable, "customership", acSaveYes
DoCmd.Close acTable, "1", acSaveYes
DoCmd.Close acTable, "3", acSaveYes
DoCmd.Close acForm, "Form4", acSaveYes
DoCmd.OpenForm "Form2"
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.SetWarnings False
DoCmd.Close acForm, "Form2", acSaveYes


DoCmd.OpenForm "Form1"
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.Close acForm, "Form1", acSaveYes
DoCmd.SetWarnings False
End If

DoCmd.Echo True
Me!customerID = Null
Forms![Import Customer].SetFocus
Me![customerID].SetFocus


Exit_Command33_Click:
Exit Sub

Err_Command33_Click:
MsgBox Err.Description
Resume Exit_Command33_Click

End Sub