Update query to remove spaces

[DeanRowe]

Hi,

I'm trying to run an update query on a field called "DisplayURL" to remove all of the spaces that the field contains. Some have 1 space, some 5 and some have no spaces.

I'm using Access 2000 and the replace function ( Replace("DisplayURL"," ","") or Replace([DisplayURL]," ",""). However it produces an error stating that there is an "Undefined function "Replace" in expression".

I've searched online and found that Access 2000 doesn't have this function, but its easy to create a "wrapper" to make it work.

I've tried various examples online with no luck. Does anyone know what "module" I'd have to create to get this working, and how to "call" the function as I do not understand how to do this?

Thanks in advance for your help, I've been having a nightmare with this problem.

Thanks again.

Dean

 

[boblarson]

Dean - I don't know for sure why, but I just opened Access 2000 and did a test and used Replace in a query just fine.

In the UPDATE area of the update query (in the QBE Grid) I put:

Replace([MyFieldName]," ","")

and it replaced the spaces with no spaces

 

[DeanRowe]

Hi Bob,

I think it varies depending on whether it was an early or late version of 2000, I presume I have the early one.

here's a printscreen of the code and the error:

Any other ideas how I can achieve the aim of replacing the various spaces?

Thanks for your help Bob.

Dean

 

[DeanRowe]

GOT A RESULT AT LAST! For anyone who searches this in future, create a module and enter the following...

Public Function fReplace(ByVal Expression As String, ByVal Find As String, ByVal Replace As String) As String
Dim strTmp As String, n As Integer
 
strTmp = Expression
n = InStr(strTmp, Find)
 
Do While n > 0
  strTmp = left(strTmp, n - 1) & Replace & _
   Mid(strTmp, n + Len(Find))
  n = InStr(n + Len(Replace), strTmp, Find, 1)
Loop
 
fReplace = strTmp
  
End Function

Save it as anything. Then create a query, drag your field into the design view, select update query. then write the following in the update to area:

fReplace([THEFIELDNAME]," ","")

The first " " states which character you want replacing (in this case spaces) and the second "" state what you want to turn it into/replace it with (in this case nothing) - be aware that this will replace ALL spaces, and doesn't just select ones at the start or the end.

Credit goes to GHUDSON who wrote this all here

 

[ggodwin]

Dean,
I very much appreaciate what you have done here. I have been looking all day for a solution to the same problem.

You went the extra mile and saved me tons of time.

Thank you!!!

 

[datAdrenaline]

Just an FYI ... A2000 does indeed have the Replace() function available to it in VBA. However, by some unfortunate circumstance that function was NOT included in the JET Expression Service (which evaluates expressions in the SQL statements sent to JET) ... a Service Pack fixed the issue, but I can not recall the service pack that did in. However, you can just wrap the internal Replace with a UDF replace, just an GHudson did ... except you don't need to create your own replacement algorithm ...

Public Function fReplace(ByVal strExpression As String _
                         , ByVal strFind As String _
                         , ByVal strReplace As String _
                         , Optional lngStart As Long = 1 _
                         , Optional lngCount As Long = -1 _
                         , Optional intCompare As Integer = vbTextCompare) As String
    
    fReplace = VBA.Replace(strExpression, strFind, strReplace, lngStart, lngCount, intCompare)
    
End Function

You could even "overload" it by naming your UDF "Replace" instead of "fReplace" ... but honestly I would not recommend it .. ...