Removing Wildcards from fields via VBA

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

"Keith" <keith.wilby@baeAWAYWITHITsystems.com> wrote in message
news:428af8ea$1_1@glkas0286.greenlnk.net...[color=blue]
> 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[/color]


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.

"Justin Hoffman" <j@b.com> wrote in message
news:d6euvp$enh$1@nwrdmz02.dmz.ncs.ea.ibs-infra.bt.com...[color=blue]
>
> 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.
>
>[/color]
That's it! It's so darned obvious now you've shown me, many thanks Justin
:o)

Regards,
Keith.