Remove hyphens from a string in a table

[armesca]

I am trying to remove all hyphens from 1 column in a access table. I have created an update query, but when I try to insert a replace function, I get compile errors in my query expression. I am trying to strip out all hyphens from the column Award number from the Port Area table. Help Please!!

Field: Award Number

Table: Port Area

Update to: Replace([Award Number],"-","")


This is what I have in the update query I am creating. Do I have to write vba code for the replace function for it to work? Any help is appreciated.

 

[Jeanette Cunningham]

It is the hyphen itself that is the problem. Your update query with replace will work for almost any character, but it will not work for the hyphen.
Find the ascii character for hyphen and use that instead of "-" in your query.
Chr(45)

Replace([Award Number],Chr(45),"")

 

[DCrake]

Just tried this in the immediate window and did not get an error

?Replace("Fed-Ex","-","")
FedEx

 

[ChrisO]

The only way I can get a syntax error is if the regional settings are set to a region that uses a semicolon, rather than a comma, as the list separator.

For example, this is required for Portuguese: -

Replace([Award Number];"-";"")

Apart from that I can’t get it to fail on either a number or text field.

I was expecting a failure on numbers but the Replace function seems to work okay in both queries and VBA.

Sub Test()
    Dim x As Long
    
    x = -1
    
    MsgBox Replace(x, "-", "")   [color=green]’ <<<   1[/color]
    
End Sub

Looks like Access is up to its type casting again.

Learnt something today.

Chris.