query to apply filter and remove characters in results

[chaos18]

i want to eliminate some slashes in a table and not every record has one and some even have two...(go figure), could i possibly use a query to apply a filter to table X that lists all the entries that have a \ in it and then eliminate the \'s according to the results of the applied filter? Would that work or will i have to find another way. perhaps there is an easier way. Any ideas?

thanks
Thomas gunter

 

[AskMan]

Why not ...


Set rst = CurrentDb.OpenRecordset("SELECT MyField FROM MyTable WHERE MyField LIKE '*\*'&quot

While Not rst.EOF
NewString = ""

For i = 1 To Len(rst!MyField)
NewChar = IIf(Mid(rst!MyField, i, 1) = "\", "", Mid(rst!MyField, i, 1) = "\&quot
NewString = NewString & NewChar
Next

rst.Edit
rst!MyField = NewString
rst.Update
Wend

rst.Close
Set rst = Nothing



Hope that could help you
SEB

 

[chaos18]

Thanks for the help, though when i run the query i get an error to the effect that it cannot perform the query without knowing if its an update, delete, insert, procedure, or select query. I'm assuming it would be a procedure query. Would that be a correct assumption?




-Thomas Gunter-

 

[AskMan]

You have to put this code in a Sub with the declaration ...

Public Sub SubName
Dim rst as DAO.Recordset, NewString as String, NewChar as string, i as Long

Set rst = CurrentDb.OpenRecordset("SELECT MyField FROM MyTable WHERE MyField LIKE '*\*'&quot

While Not rst.EOF
NewString = ""

For i = 1 To Len(rst!MyField)
NewChar = IIf(Mid(rst!MyField, i, 1) = "\", "", Mid(rst!MyField, i, 1) = "\&quot
NewString = NewString & NewChar
Next

rst.Edit
rst!MyField = NewString
rst.Update
Wend

rst.Close
Set rst = Nothing



Hope that helps

 

[chaos18]

i suppose that i must be missing something but i still recieve the same error and i cut and pasted your sql statement and changed only that which needed to be changed, ie "my field" to "source_id"

-Thomas Gunter-

 

[chaos18]

I did notice however that when i redo the sql slightly to:

SELECT Source_ID
FROM x
WHERE (((x.Source_ID) LIKE "*\*&quot While Not rst.EOF)
NewString = ""

For i = 1 To Len(rst!Source_ID)
NewChar = If(Mid(rst!Source_ID, i, 1) = "\", "", Mid(rst!Source_ID, i, 1) = "\&quot
NewString = NewString & NewChar
Next

rst.Edit
rst!Source_ID = NewString
rst.Update
Wend

rst.Close
Set rst = Nothing;

the "while not" statement is where the query seems to hang up in running. it states its missing an operator but i have not been able to find out exactly what should go there.

-Thomas Gunter-

 

[AskMan]

??? don't understand ...
this works 'perfectly' on my PC
I just have forgotten the End Sub into the first code
I just can tell you to try once more, copying my first code and adding the 'End Sub'(if not already do)

Sorry

 

[chaos18]

i donno...i saw the end sub and added it before but it still wants the type of query selected...then i select any (procedure, select, etc)and it gives me an error in the code. thanks for all your help though. It is GREATLY appreciated.

-Thomas Gunter-