Query taken away "-"

[Hemish]

Hi i'm just wondering on a query i have a field called sort code and i am trying to take away the "-"

Eg 60-60-50 I want it to be 606050. Is there an easy way to do this is a query as i have over 500 records.

Thanks

 

[pbaldy]

Look at the Replace() function.

 

[Hemish]

Hi the replace function does not seem to work in Access 2000. Do you have any other ideas please?

 

[doco]

Just tested this silly little snippet and it returned "606060" just as expected...

Sub test()
Dim szReplace As String

    szReplace = Replace("60-60-60", "-", "")
    Debug.Print szReplace
    
End Sub

 

[doco]

Not sure how you are using the query but I created a little table with one field 'sort_field' and placed three records of hyphenated text then queried that table for output to the Immediate window

Sub test()
Dim SQL As String
Dim rs As DAO.Recordset

    SQL = "SELECT sort_field " & _
          "FROM MyTable;"
          
    Set rs = CurrentDb.OpenRecordset(SQL, dbOpenSnapshot)
    
        With rs
            .MoveFirst
            
            Do Until .EOF
                Debug.Print Replace(.Fields("sort_field"), "-", "")
                .MoveNext
            Loop
        End With
        
    Set rs = Nothing
    
End Sub

Producing
606061
202021
051015
as expected.

 

[pbaldy]

I have Access 2000 and the Replace function works fine. How are you using it?