format fax numbers in query field 1

[aonefun]

I have created a union query of two tables that list fax numbers in a text field (notice that I don't have authority to change this field format to numeric)

The resulting query has some fax numbers formatted in 2 different ways:

(123)456-7890 and 123-456-7890

How to I change the resulting union query fax field to display all numbers as follows:

1234567890?

 

[CaptainD]

This work?

Public Function FixFax(sNumber As String) As String
On Error GoTo ErrHandler
If Nz(sNumber, "") = "" Then
    Exit Function
Else
FixFax = Replace(sNumber, "(", "")
FixFax = Replace(FixFax, ")", "")
FixFax = Replace(FixFax, "-", "")
FixFax = Replace(FixFax, " ", "")

End If
Exit Function
ErrHandler:
    MsgBox "Error fixing fax number. Error # " & Err.Number & ", " & Err.Description, vbOKOnly, "Error"
End Function

 

[CaptainD]

I placed the FixFax() function in a module and ran the following sql statement against a table I have.

It works

Private Sub Command3_Click()
Dim strSql As String
Dim rst As DAO.Recordset

strSql = "SELECT FixFax(Home_Phone_No)AS [Phone Number] FROM tblPeople WHERE Last_Name = 'Adams'"

Set rst = DBEngine(0)(0).OpenRecordset(strSql)
    Do While Not rst.EOF
        MsgBox rst.Fields(0).Value
        rst.MoveNext
    Loop
End Sub

 

[aonefun]

Thanks for your reply.

In which portion of Access' interface do I insert the above code sniplets as I do not have hand-coding programming knowledge.

 

[aonefun]

From what I understand, some of the code is inserted into the query's SQL view and the other gets saved in a module?
How do I refer to the module code while inserting the code in SQL view?

 

[CaptainD]

Get into the VBA editor, on the menu click "Insert" - "Module" and you will see a New "Window" pop up that probably says "Option Compare Database" at the top"

Paste this under the "Option Compare Database"

Public Function FixFax(sNumber As String) As String
On Error GoTo ErrHandler
If Nz(sNumber, "") = "" Then
    Exit Function
Else
FixFax = Replace(sNumber, "(", "")
FixFax = Replace(FixFax, ")", "")
FixFax = Replace(FixFax, "-", "")
FixFax = Replace(FixFax, " ", "")

End If
Exit Function
ErrHandler:
    MsgBox "Error fixing fax number. Error # " & Err.Number & ", " & Err.Description, vbOKOnly, "Error"
End Function

I assume you are using Access's query builder. At the top where you place the fields to query enter it something like this instead of just the field:

AliasFieldName: FixFax([FaxFieldName])

The query will run the returns through the function

If you want to do it through vba then place this under the click event to a command button, change the "red" text to what fits your database and see what it does.

Dim strSql As String
Dim rst As DAO.Recordset

strSql = "SELECT FixFax([[COLOR=red]FaxFieldName[/color]])AS [[COLOR=red]Fax Number[/color]] FROM [COLOR=red]tblYourTable[/color]"

Set rst = DBEngine(0)(0).OpenRecordset(strSql)
    Do While Not rst.EOF
        MsgBox rst.Fields(0).Value
        rst.MoveNext
    Loop

 

[aonefun]

Thanks again for your assistance. I'm still having trouble though.

What do I save the module as (so that the SQL code refers to it)?
Do I have to customize the function code at all?
I assume that I should not be puting the words "AliasFieldName: " into the SQL?

 

[aonefun]

One more question:

What if I need to change from this formating:
123-456-789
to
(123)456-789
or from
123456789
to
(123)456-789

How would the function code change?

 

[CaptainD]

What do I save the module as (so that the SQL code refers to it)?"

Just save the module and name it what you want or leave it as the defualt. (Module1) Access will see the function and should run it from inside the query. (It did on mine)

In a click event you could also type in

MsgBox FixFax("(555)345-2233")

and you should see a message with 5553452233

Do I have to customize the function code at all?


No, the code should work as it is for what you initially posted>

I assume that I should not be puting the words "AliasFieldName: " into the SQL?


You can name it what you want or leave as is. What you will see when you run the query is the "AliasFieldName" at the top of the column.

(or if you called it "Fax Number" you would see that at the top of the column)


What if I need to change from this formating:
123-456-789
to
(123)456-789

You can add another "User function" like "FixFax()" to make the changes and change the "Replace()" function accordingly.
You might want to research "VBA Functions" to see how they work.

(123)456-789
This one can can be done using the built in "Format() function.

Take a simple form with a text box and a command button. (Change the code below to fit) Enter 5554872233 and click the command button, it should read as (555) 487-2233

Private Sub Command4_Click()
Dim sFaxNumber As String
txtFaxNumber.SetFocus
    sFaxNumber = Format(txtFaxNumber.Text, "(###) ###-####")
    MsgBox sFaxNumber

End Sub