Search Like & Phone Number Format

[luism]

I have the following VBA code to search for a string

'Search by Phone
Private Sub CmdSearchPhones_Click()
    Me.Refresh
    strSQL = "SELECT Tbl_Contacts.ContactID, Tbl_Contacts.FName, Tbl_Contacts.LName, Tbl_Contacts.Address, Tbl_Contacts.City, Tbl_Contacts.State, Tbl_Contacts.Zip, Tbl_Contacts.HomePhone, Tbl_Contacts.WorkPhone, Tbl_Contacts.CellPhone, Tbl_Contacts.Email, [LName] & IIf([FName] Is Not Null,', ' & [FName],'') AS ContactName FROM Tbl_Contacts WHERE"
    
    strSQL = strSQL & " HomePhone like """ & Forms!Frm_SearchAllContacts!EnterPhone & "*"""
    strSQL = strSQL & "Or  WorkPhone like """ & Forms!Frm_SearchAllContacts!EnterPhone & "*"""
    strSQL = strSQL & "Or  CellPhone like """ & Forms!Frm_SearchAllContacts!EnterPhone & "*"""
    
    strSQL = strSQL & "ORDER BY Tbl_Contacts.LName;"
    Forms![Frm_SearchAllContacts].Form.RecordSource = strSQL
    
    DoCmd.GoToControl "EnterPhone"
End Sub

My issue is that this works 50/50, because phone numbers on the database are NOW being stored in this format (000) 000-0000 and previous DB phone numbers got stored as 0000000000. The above code finds previous DB phone numbers (even if we dont enter all 10 digits) without a problem but has issues with new phone numbers stored in the new format UNLESS I specify the search with (000) 000-0000 (but we want to be able to search without adding that and without typing all 10 digits - in case we forget part of the number we are looking for)

How can I tell my query to find both formats, with the (000) 000-0000 AND 0000000000 any help appreciated!

 

[Pat Hartman]

I would update the database to remove the formatting characters so that all the dates are just stored as 10 digit strings and the formatting is only applied for display purposes rather than stored.

 

[luism]

i had to update over 11,000 records to change the format. wasn't that easy but this was basically the best option. thanks.

 

[Pat Hartman]

I hope you didn't do it one number at a time

 

[luism]

no way, after exporting the table on excel, removing all (xxx) xxx-xxx formatting then importing the data and finally did an UPDATE query on the phone numbers.