Removing Wildcards from fields via VBA

[Keith]

A2003, XP Pro.

I'm writing a VBA procedure to remove wildcard characters from a number of
fields in various tables. I'm calling a sub routine and passing in strings
representing the character to be replaced, the field name and the table
name. All works fine until I try to get the data string out of the field by
referencing the string variable, at which point I get an "object variable
not set" error. I know this is going to be something daft but I can't quite
fathom it out - any ideas? Many thanks, Keith.

Code:

Sub libReplaceWildcards()

Call libWildcards("&", "DRG_TITLE", "OMSADM_OMS_DRG_TBL") 'Call the routine
for "&"
Call libWildcards("%", "DRG_TITLE", "OMSADM_OMS_DRG_TBL") 'Call the routine
for "%"
Call libWildcards("#", "DRG_TITLE", "OMSADM_OMS_DRG_TBL") 'Call the routine
for "#"

Call libWildcards("&", "VER_DRG_TITLE", "OMSADM_OMS_VERSION_TBL") 'Call the
routine for "&"
Call libWildcards("%", "VER_DRG_TITLE", "OMSADM_OMS_VERSION_TBL") 'Call the
routine for "%"
Call libWildcards("#", "VER_DRG_TITLE", "OMSADM_OMS_VERSION_TBL") 'Call the
routine for "#"

End Sub

Sub libWildcards(strSearchChar As String, strField As String, strTable As
String)

Dim db As DAO.Database, rs As DAO.Recordset, strSQL As String, intPosition
As Integer
Dim strSearchString As String, strNewString As String, strReplaceChar As
String
Set db = CurrentDb

If strSearchChar = "&" Then strNewString = "AND"
If strSearchChar = "%" Then strNewString = "PERCENT"

strSQL = "Select " & strField & " From " & strTable & " Where " & strField &
" Like '*[" & strSearchChar & "]*'"
Set rs = db.OpenRecordset(strSQL)
If rs.RecordCount = 0 Then Exit Sub

With rs
.MoveFirst
Do Until .EOF
strSearchString = rs!strField *** THIS IS WHERE THE ERROR OCCURS ***
intPosition = InStr(1, strSearchString, strSearchChar, 1) - 1 'Count
the characters before the wildcard

If strSearchChar = "#" Then 'Hashes are removed but may leave spaces
behind
strNewString = Left(strSearchString, intPosition) 'Get string
before the hash and space characters
Else
strNewString = Left(strSearchString, intPosition) _
& strReplaceChar 'Add the replacement for the wildcard
End If

strNewString = strNewString _
& Right(strSearchString, Len(strSearchString) - (intPosition + 2))
'Add the characters after the wildcard

.Edit
![strField] = strNewString
.Update

Debug.Print strNewString
.MoveNext
Loop
End With

End Sub

[Justin Hoffman]

"Keith" <ke*********@baeAWAYWITHITsystems.com> wrote in message
news:42**********@glkas0286.greenlnk.net...

A2003, XP Pro.

I'm writing a VBA procedure to remove wildcard characters from a number of
fields in various tables. I'm calling a sub routine and passing in
strings representing the character to be replaced, the field name and the
table name. All works fine until I try to get the data string out of the
field by referencing the string variable, at which point I get an "object
variable not set" error. I know this is going to be something daft but I
can't quite fathom it out - any ideas? Many thanks, Keith.

Code:

Sub libReplaceWildcards()

Call libWildcards("&", "DRG_TITLE", "OMSADM_OMS_DRG_TBL") 'Call the
routine for "&"
Call libWildcards("%", "DRG_TITLE", "OMSADM_OMS_DRG_TBL") 'Call the
routine for "%"
Call libWildcards("#", "DRG_TITLE", "OMSADM_OMS_DRG_TBL") 'Call the
routine for "#"

Call libWildcards("&", "VER_DRG_TITLE", "OMSADM_OMS_VERSION_TBL") 'Call
the routine for "&"
Call libWildcards("%", "VER_DRG_TITLE", "OMSADM_OMS_VERSION_TBL") 'Call
the routine for "%"
Call libWildcards("#", "VER_DRG_TITLE", "OMSADM_OMS_VERSION_TBL") 'Call
the routine for "#"

End Sub

Sub libWildcards(strSearchChar As String, strField As String, strTable As
String)

Dim db As DAO.Database, rs As DAO.Recordset, strSQL As String, intPosition
As Integer
Dim strSearchString As String, strNewString As String, strReplaceChar As
String
Set db = CurrentDb

If strSearchChar = "&" Then strNewString = "AND"
If strSearchChar = "%" Then strNewString = "PERCENT"

strSQL = "Select " & strField & " From " & strTable & " Where " & strField
& " Like '*[" & strSearchChar & "]*'"
Set rs = db.OpenRecordset(strSQL)
If rs.RecordCount = 0 Then Exit Sub

With rs
.MoveFirst
Do Until .EOF
strSearchString = rs!strField *** THIS IS WHERE THE ERROR OCCURS
***
intPosition = InStr(1, strSearchString, strSearchChar, 1) - 1
'Count the characters before the wildcard

If strSearchChar = "#" Then 'Hashes are removed but may leave
spaces behind
strNewString = Left(strSearchString, intPosition) 'Get string
before the hash and space characters
Else
strNewString = Left(strSearchString, intPosition) _
& strReplaceChar 'Add the replacement for the wildcard
End If

strNewString = strNewString _
& Right(strSearchString, Len(strSearchString) - (intPosition + 2))
'Add the characters after the wildcard

.Edit
![strField] = strNewString
.Update

Debug.Print strNewString
.MoveNext
Loop
End With

End Sub

rs!strField would be what you would write if your field name was literally
"strField"
rs.Fields(strField) or simply rs(strField) is what you need
Don't forget that if your field could be null, then you should use the Nz
function before assigning a a field's value to a variable of type string.

[Keith]

"Justin Hoffman" <[e-mail]> wrote in message
news:d6**********@nwrdmz02.dmz.ncs.ea.ibs-infra.bt.com...

rs!strField would be what you would write if your field name was literally
"strField"
rs.Fields(strField) or simply rs(strField) is what you need
Don't forget that if your field could be null, then you should use the Nz
function before assigning a a field's value to a variable of type string.

That's it! It's so darned obvious now you've shown me, many thanks Justin
:o)

Regards,
Keith.