Find and Replace Programatically

'===============================================================================
'Purpose to modify the date
'from Excel so that they would work in Access. The date in the excel table was in
'text format, which Access didn't like!!!!

'please note the access prefers American format dates, so double check to
'make sure that your dates Have been correctly interpreted before destroying your old data!
'
'if you're not careful access interprets the
'forward slash / as a division instruction if you put the dates in a number field.

Public Function fFixDate(strDate As String) As String
Dim intLenDate As Integer
Dim x As Integer
Dim strNewDate As String
Dim strChar As String

intLenDate = Len(strDate)

For x = 1 To intLenDate
strChar = Mid(strDate, x, 1)
 If strChar = "." Then
 Let strNewDate = strNewDate & "/"
 Else
 strNewDate = strNewDate & strChar
 End If
Next x

Select Case strNewDate 'If the date returned is invalid this case statement replaced it with a default date

Case ""
strNewDate = "1/6/58"
Case "."
strNewDate = "1/6/58"
Case "/"
strNewDate = "1/6/58"

End Select

fFixDate = CDate(strNewDate) 'this don't look right!

End Function

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

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