Find and Replace wild card

  • Thread starter Thread starter Oded Dror
  • Start date Start date
O

Oded Dror

Hi there,

I'm using Access 2003 and Windows XPPro. SP2
I have a table tblCustomers that I import from another program
the table have about 2,000 records and 20 columns
In my rows I have some time a ? inside the raw
Like CustomerName abc? or Address 123 main st.?

My question how do I create a macro or sub proc to find a ? in my entire
table
And replace with empty char. like " "

Also How do I find Chr(129),Chr(144) and clean this characters ?

How do I call to this Function to do the job on a specific table

Thanks you an advanced

Oded Dror
Email: (e-mail address removed)
 
In answer to the first question, you can use the Replace function in a
series of update queries (one for each field). Here's some code that
automates the task of re-writing the query for each field. Be sure to
replace 'tblTest' with the name of your table, and as always when performing
any kind of bulk update on data, make sure you have a reliable backup first,
just in case.

Public Sub TestSub3()

Const strcSQL As String = _
"UPDATE tblTest SET tblTest.%1 = Replace([%1],""?"",'') WHERE (([%1]
Like ""*[?]*""));"

Dim strSQL As String
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set db = CurrentDb
Set tdf = db.TableDefs("tblTest")
For Each fld In tdf.Fields
If fld.Type = dbText Or fld.Type = dbMemo Then
strSQL = Replace(strcSQL, "%1", fld.Name)
db.Execute strSQL
DBEngine.Idle dbRefreshCache
End If
Next fld

End Sub
 
And in answer to the second question ... well, as it turns out, the answer
to the second question is the same, with just a small change to the
'strcSQL' constant ...

Const strcSQL As String = _
"UPDATE tblTest SET tblTest.%1 = Replace([%1], Chr$(129), '') WHERE
" & _
"(((tblTest.%1) Like ""*"" & Chr$(129) & ""*""));"

--
Brendan Reynolds (MVP)

Brendan Reynolds said:
In answer to the first question, you can use the Replace function in a
series of update queries (one for each field). Here's some code that
automates the task of re-writing the query for each field. Be sure to
replace 'tblTest' with the name of your table, and as always when
performing any kind of bulk update on data, make sure you have a reliable
backup first, just in case.

Public Sub TestSub3()

Const strcSQL As String = _
"UPDATE tblTest SET tblTest.%1 = Replace([%1],""?"",'') WHERE
(([%1] Like ""*[?]*""));"

Dim strSQL As String
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set db = CurrentDb
Set tdf = db.TableDefs("tblTest")
For Each fld In tdf.Fields
If fld.Type = dbText Or fld.Type = dbMemo Then
strSQL = Replace(strcSQL, "%1", fld.Name)
db.Execute strSQL
DBEngine.Idle dbRefreshCache
End If
Next fld

End Sub

--
Brendan Reynolds (MVP)

Oded Dror said:
Hi there,

I'm using Access 2003 and Windows XPPro. SP2
I have a table tblCustomers that I import from another program
the table have about 2,000 records and 20 columns
In my rows I have some time a ? inside the raw
Like CustomerName abc? or Address 123 main st.?

My question how do I create a macro or sub proc to find a ? in my entire
table
And replace with empty char. like " "

Also How do I find Chr(129),Chr(144) and clean this characters ?

How do I call to this Function to do the job on a specific table

Thanks you an advanced

Oded Dror
Email: (e-mail address removed)
Click to expand...
Click to expand...
 
Thanks for the code but I'm getting an error that says:
Compiler Error
Expected: end of statement

How do I fix that ?

Thanks again
Oded Dror
Email: (e-mail address removed)


Brendan Reynolds said:
In answer to the first question, you can use the Replace function in a
series of update queries (one for each field). Here's some code that
automates the task of re-writing the query for each field. Be sure to
replace 'tblTest' with the name of your table, and as always when
performing any kind of bulk update on data, make sure you have a reliable
backup first, just in case.

Public Sub TestSub3()

Const strcSQL As String = _
"UPDATE tblTest SET tblTest.%1 = Replace([%1],""?"",'') WHERE
(([%1] Like ""*[?]*""));"

Dim strSQL As String
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set db = CurrentDb
Set tdf = db.TableDefs("tblTest")
For Each fld In tdf.Fields
If fld.Type = dbText Or fld.Type = dbMemo Then
strSQL = Replace(strcSQL, "%1", fld.Name)
db.Execute strSQL
DBEngine.Idle dbRefreshCache
End If
Next fld

End Sub

--
Brendan Reynolds (MVP)

Oded Dror said:
Hi there,

I'm using Access 2003 and Windows XPPro. SP2
I have a table tblCustomers that I import from another program
the table have about 2,000 records and 20 columns
In my rows I have some time a ? inside the raw
Like CustomerName abc? or Address 123 main st.?

My question how do I create a macro or sub proc to find a ? in my entire
table
And replace with empty char. like " "

Also How do I find Chr(129),Chr(144) and clean this characters ?

How do I call to this Function to do the job on a specific table

Thanks you an advanced

Oded Dror
Email: (e-mail address removed)
Click to expand...
Click to expand...
 
It's probably an issue with the quotes. Is this the line that it's
complaining about?

"UPDATE tblTest SET tblTest.%1 = Replace([%1],""?"",'') WHERE (([%1] Like
""*[?]*""));"

Those are all double quotes, except these ones ...

, '') WHERE

.... those are two single quotes with nothing between them.

If you typed one double quote instead of the two single quotes in that
position - which would certainly be easy to do - I believe that would cause
the error you describe.

--
Brendan Reynolds (MVP)

Ed Dror said:
Thanks for the code but I'm getting an error that says:
Compiler Error
Expected: end of statement

How do I fix that ?

Thanks again
Oded Dror
Email: (e-mail address removed)


Brendan Reynolds said:
In answer to the first question, you can use the Replace function in a
series of update queries (one for each field). Here's some code that
automates the task of re-writing the query for each field. Be sure to
replace 'tblTest' with the name of your table, and as always when
performing any kind of bulk update on data, make sure you have a reliable
backup first, just in case.

Public Sub TestSub3()

Const strcSQL As String = _
"UPDATE tblTest SET tblTest.%1 = Replace([%1],""?"",'') WHERE
(([%1] Like ""*[?]*""));"

Dim strSQL As String
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set db = CurrentDb
Set tdf = db.TableDefs("tblTest")
For Each fld In tdf.Fields
If fld.Type = dbText Or fld.Type = dbMemo Then
strSQL = Replace(strcSQL, "%1", fld.Name)
db.Execute strSQL
DBEngine.Idle dbRefreshCache
End If
Next fld

End Sub

--
Brendan Reynolds (MVP)

Oded Dror said:
Hi there,

I'm using Access 2003 and Windows XPPro. SP2
I have a table tblCustomers that I import from another program
the table have about 2,000 records and 20 columns
In my rows I have some time a ? inside the raw
Like CustomerName abc? or Address 123 main st.?

My question how do I create a macro or sub proc to find a ? in my entire
table
And replace with empty char. like " "

Also How do I find Chr(129),Chr(144) and clean this characters ?

How do I call to this Function to do the job on a specific table

Thanks you an advanced

Oded Dror
Email: (e-mail address removed)
Click to expand...
Click to expand...
Click to expand...
 
Const strcSQL As String = _
"UPDATE tblTest SET tblTest.%1 = Replace([%1],""?"",' ' ) _
WHERE (([%1] Like ""*[?]*""));"

I'm still getting error ( on the WHERE is highlighting)

Thanks,

Oded Dror
Email: (e-mail address removed)


Brendan Reynolds said:
It's probably an issue with the quotes. Is this the line that it's
complaining about?

"UPDATE tblTest SET tblTest.%1 = Replace([%1],""?"",'') WHERE (([%1] Like
""*[?]*""));"

Those are all double quotes, except these ones ...

, '') WHERE

... those are two single quotes with nothing between them.

If you typed one double quote instead of the two single quotes in that
position - which would certainly be easy to do - I believe that would
cause the error you describe.

--
Brendan Reynolds (MVP)

Ed Dror said:
Thanks for the code but I'm getting an error that says:
Compiler Error
Expected: end of statement

How do I fix that ?

Thanks again
Oded Dror
Email: (e-mail address removed)


Brendan Reynolds said:
In answer to the first question, you can use the Replace function in a
series of update queries (one for each field). Here's some code that
automates the task of re-writing the query for each field. Be sure to
replace 'tblTest' with the name of your table, and as always when
performing any kind of bulk update on data, make sure you have a
reliable backup first, just in case.

Public Sub TestSub3()

Const strcSQL As String = _
"UPDATE tblTest SET tblTest.%1 = Replace([%1],""?"",'') WHERE
(([%1] Like ""*[?]*""));"

Dim strSQL As String
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set db = CurrentDb
Set tdf = db.TableDefs("tblTest")
For Each fld In tdf.Fields
If fld.Type = dbText Or fld.Type = dbMemo Then
strSQL = Replace(strcSQL, "%1", fld.Name)
db.Execute strSQL
DBEngine.Idle dbRefreshCache
End If
Next fld

End Sub

--
Brendan Reynolds (MVP)

Hi there,

I'm using Access 2003 and Windows XPPro. SP2
I have a table tblCustomers that I import from another program
the table have about 2,000 records and 20 columns
In my rows I have some time a ? inside the raw
Like CustomerName abc? or Address 123 main st.?

My question how do I create a macro or sub proc to find a ? in my
entire table
And replace with empty char. like " "

Also How do I find Chr(129),Chr(144) and clean this characters ?

How do I call to this Function to do the job on a specific table

Thanks you an advanced

Oded Dror
Email: (e-mail address removed)
Click to expand...
Click to expand...
Click to expand...
 
You may have fallen victim to 'newsreader line wrap' - in my original code,
the SQL was all one line, but your newsreader program may have wrapped it
across two lines. If you want to wrap it like that, try ...

Const strcSQL As String = _
"UPDATE tblTest SET tblTest.%1 = Replace([%1],""?"",' ' ) " & _
"WHERE (([%1] Like ""*[?]*""));"

In this line ...

"UPDATE tblTest SET tblTest.%1 = Replace([%1],""?"",' ' ) " & _

.... be careful to leave a space between the closing parenthesis and the
final quote.

--
Brendan Reynolds (MVP)


Ed Dror said:
Const strcSQL As String = _
"UPDATE tblTest SET tblTest.%1 = Replace([%1],""?"",' ' ) _
WHERE (([%1] Like ""*[?]*""));"

I'm still getting error ( on the WHERE is highlighting)

Thanks,

Oded Dror
Email: (e-mail address removed)


Brendan Reynolds said:
It's probably an issue with the quotes. Is this the line that it's
complaining about?

"UPDATE tblTest SET tblTest.%1 = Replace([%1],""?"",'') WHERE (([%1] Like
""*[?]*""));"

Those are all double quotes, except these ones ...

, '') WHERE

... those are two single quotes with nothing between them.

If you typed one double quote instead of the two single quotes in that
position - which would certainly be easy to do - I believe that would
cause the error you describe.

--
Brendan Reynolds (MVP)

Ed Dror said:
Thanks for the code but I'm getting an error that says:
Compiler Error
Expected: end of statement

How do I fix that ?

Thanks again
Oded Dror
Email: (e-mail address removed)


In answer to the first question, you can use the Replace function in a
series of update queries (one for each field). Here's some code that
automates the task of re-writing the query for each field. Be sure to
replace 'tblTest' with the name of your table, and as always when
performing any kind of bulk update on data, make sure you have a
reliable backup first, just in case.

Public Sub TestSub3()

Const strcSQL As String = _
"UPDATE tblTest SET tblTest.%1 = Replace([%1],""?"",'') WHERE
(([%1] Like ""*[?]*""));"

Dim strSQL As String
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set db = CurrentDb
Set tdf = db.TableDefs("tblTest")
For Each fld In tdf.Fields
If fld.Type = dbText Or fld.Type = dbMemo Then
strSQL = Replace(strcSQL, "%1", fld.Name)
db.Execute strSQL
DBEngine.Idle dbRefreshCache
End If
Next fld

End Sub

--
Brendan Reynolds (MVP)

Hi there,

I'm using Access 2003 and Windows XPPro. SP2
I have a table tblCustomers that I import from another program
the table have about 2,000 records and 20 columns
In my rows I have some time a ? inside the raw
Like CustomerName abc? or Address 123 main st.?

My question how do I create a macro or sub proc to find a ? in my
entire table
And replace with empty char. like " "

Also How do I find Chr(129),Chr(144) and clean this characters ?

How do I call to this Function to do the job on a specific table

Thanks you an advanced

Oded Dror
Email: (e-mail address removed)
Click to expand...
Click to expand...
Click to expand...
 
Back
Top