Find and Replace Programatically

SteveC24 · May 3, 2005

Hello,

I have a list of dates of births in a table, but the way they are always imported (from some piece of crappy software somewhere) is in the format of: 01.01.2005 (using full stops).

Access doesn't like this, and insists on slashes, or dashes. Now, if I do a find/replace, it works perfectly, just replacing all the full stops with slashes. However, I want to do this quite regularly...any ideas how to do it programatically in VBA?

Thanks!

Any help much appreciated!

ghudson · May 4, 2005

The later versions of Access have the builtin Replace() function. Somebody has posted a custom Replace() function that will work in earlier versions of Access. Try searching the forum for the Replace function.

ghudson · May 4, 2005

I was determined to find it for myself. Ricky Hicks gets the credit for this oldie but goodie for those with an older version of Access can use this.

Code:

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

Here is how you would use it with a query...

Code:

fReplace([FieldName],".","/")

SteveC24 · May 4, 2005

Thanks for your input people! Much appreciated. I did take a poke around the search of the forum, and on Google, and did find the replace() function, but could only make it work on a predefined string, not on a field or anything useful!

So - I will try GHudson's suggestion first - as I think that looks less scary!

Thanks - I will let you know how it goes!

Find and Replace Programatically

SteveC24

Registered User.

ghudson

Registered User.

ghudson

Registered User.

SteveC24

Registered User.

Similar threads

Users who are viewing this thread