find/replace characters like quotes

[Clemens]

I have a table with multiple fields.
In more then one field there are character as { } or ""
I want to remove these characters.

How can I remove these characters? I know already use a code to replace text
and it is preffered to add these removal to the code

Thanks

 

[John Spencer]

You did not say how you are doing this. Assuming you are using the replace
function

Replace([SomeField],Chr(34),"") will replace a quotation mark (") with a
zero-length string

You can search for records that have a quotation mark using
Field: SomeField
Criteria: LIKE "*""*"

Or if that is a bit confusing
Criteria: LIKE "*" & Chr(34) & "*"


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Clemens]

Ok a bit more explaination:

I have created a switchboard with a button and build an event procedure.
For 1 field I use the following code to replace text

Set db = CurrentDb
strSQL = "SELECT [Approval Status] FROM

;"
Set rst = [db].OpenRecordset(strSQL, dbOpenDynaset)
With [rst]
If (.EOF = False) Then
.MoveFirst
Do While (.EOF = False)
.Edit
Select Case ![Approval Status]
Case "approved"
![Approval Status] = "yes"
Case "denied"
![Approval Status] = "x"
Case "pending"
![Approval Status] = " "
Case Else
MsgBox ![Approval Status] & vbCrLf & "This is wrong!",
vbExclamation
End Select
.Update
.MoveNext
Loop
End If
.Close
End With
Set rst = Nothing
[db].Close
Set db = Nothing

In 2 other field I have tekst like {"dfadfadfdfgsdg"} and I want to remove
the {" and "} by adding code to the code above.

Thanks for helping

You did not say how you are doing this. Assuming you are using the replace
function

Replace([SomeField],Chr(34),"") will replace a quotation mark (") with a
zero-length string

You can search for records that have a quotation mark using
Field: SomeField
Criteria: LIKE "*""*"

Or if that is a bit confusing
Criteria: LIKE "*" & Chr(34) & "*"


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have a table with multiple fields.
In more then one field there are character as { } or ""
I want to remove these characters.

How can I remove these characters? I know already use a code to replace
text
and it is preffered to add these removal to the code

Thanks

 

[John Spencer]

If I were doing this I would add an equivalents table
ApprovalValues
Field: CurrentValue
Field: NewValue

Then all you need to do is run an update query that looks like the following

UPDATE Table INNER JOIN ApprovalValues
ON Table.[Approval Status] = ApprovalValues.CurrentValue
SET Table.[Approval Status] = [ApprovalValues].[NewValue]

I am sorry but i don't understand
" In 2 other field I have tekst like {"dfadfadfdfgsdg"} and I want to
remove the {" and "} by adding code to the code above."

If you mean that in FieldA you have the word "And" and you wish to remove
that then you could probably do that with an update query that looks like
the following

UPDATE Table
SET Table.[FieldA] = Trim(Replace(" " &

.[FieldA] & " ", " and "))
WHERE Table.[FieldA] Like "* and *"

Using a query to do this is almost always more efficient than stepping
through a recordset. Plus, in my experience, using an update query seems to
lead to less bloat in the database.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Ok a bit more explaination:

I have created a switchboard with a button and build an event procedure.
For 1 field I use the following code to replace text

Set db = CurrentDb
strSQL = "SELECT [Approval Status] FROM

;"
Set rst = [db].OpenRecordset(strSQL, dbOpenDynaset)
With [rst]
If (.EOF = False) Then
.MoveFirst
Do While (.EOF = False)
.Edit
Select Case ![Approval Status]
Case "approved"
![Approval Status] = "yes"
Case "denied"
![Approval Status] = "x"
Case "pending"
![Approval Status] = " "
Case Else
MsgBox ![Approval Status] & vbCrLf & "This is wrong!",
vbExclamation
End Select
.Update
.MoveNext
Loop
End If
.Close
End With
Set rst = Nothing
[db].Close
Set db = Nothing

In 2 other field I have tekst like {"dfadfadfdfgsdg"} and I want to remove
the {" and "} by adding code to the code above.

Thanks for helping

You did not say how you are doing this. Assuming you are using the
replace
function

Replace([SomeField],Chr(34),"") will replace a quotation mark (") with a
zero-length string

You can search for records that have a quotation mark using
Field: SomeField
Criteria: LIKE "*""*"

Or if that is a bit confusing
Criteria: LIKE "*" & Chr(34) & "*"


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have a table with multiple fields.
In more then one field there are character as { } or ""
I want to remove these characters.

How can I remove these characters? I know already use a code to replace
text
and it is preffered to add these removal to the code

Thanks

 

[Clemens]

Hi John,
Sorry if I made myself not clear enough (english is not my native language)

The first solution to work with an equivalent table is worth to try, thank
for offering a other solution for replacing text.

My real problem (and also the reason for this post) is the following:
In the table I have multiple fields with text in it.
Each cel with text starts with { and is followed by a double closed quote
also {"
The text ends with the double closed quote followed by } also "}
So example:
{"this is a text and the special characters at the beginning and at the end
of this tekst should be removed"}
Which needs to be resulted in following text:
this is a text and the special characters at the beginning and at the end of
this tekst should be removed.

I hope I made myself more clear and you can provide me with a solution

If I were doing this I would add an equivalents table
ApprovalValues
Field: CurrentValue
Field: NewValue

Then all you need to do is run an update query that looks like the following

UPDATE Table INNER JOIN ApprovalValues
ON Table.[Approval Status] = ApprovalValues.CurrentValue
SET Table.[Approval Status] = [ApprovalValues].[NewValue]

I am sorry but i don't understand
" In 2 other field I have tekst like {"dfadfadfdfgsdg"} and I want to
remove the {" and "} by adding code to the code above."

If you mean that in FieldA you have the word "And" and you wish to remove
that then you could probably do that with an update query that looks like
the following

UPDATE Table
SET Table.[FieldA] = Trim(Replace(" " &

.[FieldA] & " ", " and "))
WHERE Table.[FieldA] Like "* and *"

Using a query to do this is almost always more efficient than stepping
through a recordset. Plus, in my experience, using an update query seems to
lead to less bloat in the database.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Ok a bit more explaination:

I have created a switchboard with a button and build an event procedure.
For 1 field I use the following code to replace text

Set db = CurrentDb
strSQL = "SELECT [Approval Status] FROM

;"
Set rst = [db].OpenRecordset(strSQL, dbOpenDynaset)
With [rst]
If (.EOF = False) Then
.MoveFirst
Do While (.EOF = False)
.Edit
Select Case ![Approval Status]
Case "approved"
![Approval Status] = "yes"
Case "denied"
![Approval Status] = "x"
Case "pending"
![Approval Status] = " "
Case Else
MsgBox ![Approval Status] & vbCrLf & "This is wrong!",
vbExclamation
End Select
.Update
.MoveNext
Loop
End If
.Close
End With
Set rst = Nothing
[db].Close
Set db = Nothing

In 2 other field I have tekst like {"dfadfadfdfgsdg"} and I want to remove
the {" and "} by adding code to the code above.

Thanks for helping

You did not say how you are doing this. Assuming you are using the
replace
function

Replace([SomeField],Chr(34),"") will replace a quotation mark (") with a
zero-length string

You can search for records that have a quotation mark using
Field: SomeField
Criteria: LIKE "*""*"

Or if that is a bit confusing
Criteria: LIKE "*" & Chr(34) & "*"


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have a table with multiple fields.
In more then one field there are character as { } or ""
I want to remove these characters.

How can I remove these characters? I know already use a code to replace
text
and it is preffered to add these removal to the code

Thanks

 

[John Spencer]

Again you can use an update query to fix this

UPDATE YourTable
Set YourField = Mid([YourField],3)
WHERE YourField LIKE "(""*"

That should remove the leading ("

To remove the trailing )" use a second query.

UPDATE YourTable
Set YourField = Left([YourField],Len([YourField])-2)
WHERE YourField LIKE "*"")"

You could do this all in one query assuming
UPDATE YourTable
Set YourField = Mid([YourField],3,Len([YourField])-4)
WHERE YourField LIKE """(*"")"


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi John,
Sorry if I made myself not clear enough (english is not my native
language)

The first solution to work with an equivalent table is worth to try, thank
for offering a other solution for replacing text.

My real problem (and also the reason for this post) is the following:
In the table I have multiple fields with text in it.
Each cel with text starts with { and is followed by a double closed quote
also {"
The text ends with the double closed quote followed by } also "}
So example:
{"this is a text and the special characters at the beginning and at the
end
of this tekst should be removed"}
Which needs to be resulted in following text:
this is a text and the special characters at the beginning and at the end
of
this tekst should be removed.

I hope I made myself more clear and you can provide me with a solution

If I were doing this I would add an equivalents table
ApprovalValues
Field: CurrentValue
Field: NewValue

Then all you need to do is run an update query that looks like the
following

UPDATE Table INNER JOIN ApprovalValues
ON Table.[Approval Status] = ApprovalValues.CurrentValue
SET Table.[Approval Status] = [ApprovalValues].[NewValue]

I am sorry but i don't understand
" In 2 other field I have tekst like {"dfadfadfdfgsdg"} and I want to
remove the {" and "} by adding code to the code above."

If you mean that in FieldA you have the word "And" and you wish to remove
that then you could probably do that with an update query that looks like
the following

UPDATE Table
SET Table.[FieldA] = Trim(Replace(" " &

.[FieldA] & " ", " and "))
WHERE Table.[FieldA] Like "* and *"

Using a query to do this is almost always more efficient than stepping
through a recordset. Plus, in my experience, using an update query seems
to
lead to less bloat in the database.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Ok a bit more explaination:

I have created a switchboard with a button and build an event
procedure.
For 1 field I use the following code to replace text

Set db = CurrentDb
strSQL = "SELECT [Approval Status] FROM

;"
Set rst = [db].OpenRecordset(strSQL, dbOpenDynaset)
With [rst]
If (.EOF = False) Then
.MoveFirst
Do While (.EOF = False)
.Edit
Select Case ![Approval Status]
Case "approved"
![Approval Status] = "yes"
Case "denied"
![Approval Status] = "x"
Case "pending"
![Approval Status] = " "
Case Else
MsgBox ![Approval Status] & vbCrLf & "This is wrong!",
vbExclamation
End Select
.Update
.MoveNext
Loop
End If
.Close
End With
Set rst = Nothing
[db].Close
Set db = Nothing

In 2 other field I have tekst like {"dfadfadfdfgsdg"} and I want to
remove
the {" and "} by adding code to the code above.

Thanks for helping
:

You did not say how you are doing this. Assuming you are using the
replace
function

Replace([SomeField],Chr(34),"") will replace a quotation mark (") with
a
zero-length string

You can search for records that have a quotation mark using
Field: SomeField
Criteria: LIKE "*""*"

Or if that is a bit confusing
Criteria: LIKE "*" & Chr(34) & "*"


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have a table with multiple fields.
In more then one field there are character as { } or ""
I want to remove these characters.

How can I remove these characters? I know already use a code to
replace
text
and it is preffered to add these removal to the code

Thanks

 

[Clemens]

Hi John,
Thanks for answering again. I didn't understand your first answer, but now
I'm reading it again, I see you've allready answered the question.

In my 2nd answer I mentioned a button on the switchboard with allready the
VBA code of the replacement of text. Is it possible to add some code to this
to combine both actions. So add code for the replacement of {" and also "} ?

Sorry if I'm a bit tedious

Again you can use an update query to fix this

UPDATE YourTable
Set YourField = Mid([YourField],3)
WHERE YourField LIKE "(""*"

That should remove the leading ("

To remove the trailing )" use a second query.

UPDATE YourTable
Set YourField = Left([YourField],Len([YourField])-2)
WHERE YourField LIKE "*"")"

You could do this all in one query assuming
UPDATE YourTable
Set YourField = Mid([YourField],3,Len([YourField])-4)
WHERE YourField LIKE """(*"")"


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi John,
Sorry if I made myself not clear enough (english is not my native
language)

The first solution to work with an equivalent table is worth to try, thank
for offering a other solution for replacing text.

My real problem (and also the reason for this post) is the following:
In the table I have multiple fields with text in it.
Each cel with text starts with { and is followed by a double closed quote
also {"
The text ends with the double closed quote followed by } also "}
So example:
{"this is a text and the special characters at the beginning and at the
end
of this tekst should be removed"}
Which needs to be resulted in following text:
this is a text and the special characters at the beginning and at the end
of
this tekst should be removed.

I hope I made myself more clear and you can provide me with a solution

If I were doing this I would add an equivalents table
ApprovalValues
Field: CurrentValue
Field: NewValue

Then all you need to do is run an update query that looks like the
following

UPDATE Table INNER JOIN ApprovalValues
ON Table.[Approval Status] = ApprovalValues.CurrentValue
SET Table.[Approval Status] = [ApprovalValues].[NewValue]

I am sorry but i don't understand
" In 2 other field I have tekst like {"dfadfadfdfgsdg"} and I want to
remove the {" and "} by adding code to the code above."

If you mean that in FieldA you have the word "And" and you wish to remove
that then you could probably do that with an update query that looks like
the following

UPDATE Table
SET Table.[FieldA] = Trim(Replace(" " &

.[FieldA] & " ", " and "))
WHERE Table.[FieldA] Like "* and *"

Using a query to do this is almost always more efficient than stepping
through a recordset. Plus, in my experience, using an update query seems
to
lead to less bloat in the database.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Ok a bit more explaination:

I have created a switchboard with a button and build an event
procedure.
For 1 field I use the following code to replace text

Set db = CurrentDb
strSQL = "SELECT [Approval Status] FROM

;"
Set rst = [db].OpenRecordset(strSQL, dbOpenDynaset)
With [rst]
If (.EOF = False) Then
.MoveFirst
Do While (.EOF = False)
.Edit
Select Case ![Approval Status]
Case "approved"
![Approval Status] = "yes"
Case "denied"
![Approval Status] = "x"
Case "pending"
![Approval Status] = " "
Case Else
MsgBox ![Approval Status] & vbCrLf & "This is wrong!",
vbExclamation
End Select
.Update
.MoveNext
Loop
End If
.Close
End With
Set rst = Nothing
[db].Close
Set db = Nothing

In 2 other field I have tekst like {"dfadfadfdfgsdg"} and I want to
remove
the {" and "} by adding code to the code above.

Thanks for helping
:

You did not say how you are doing this. Assuming you are using the
replace
function

Replace([SomeField],Chr(34),"") will replace a quotation mark (") with
a
zero-length string

You can search for records that have a quotation mark using
Field: SomeField
Criteria: LIKE "*""*"

Or if that is a bit confusing
Criteria: LIKE "*" & Chr(34) & "*"


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have a table with multiple fields.
In more then one field there are character as { } or ""
I want to remove these characters.

How can I remove these characters? I know already use a code to
replace
text
and it is preffered to add these removal to the code

Thanks

 

[RJF]

Hopefully, you can help me out.

I'm creating a query that will find and replace the "," characters with ;
(double quote comma double quote)

I can't seem to get the syntax correct. This is what I have now:

UPDATE ERSCUST

SET Field1 = Replace([Field1]," & Chr(34) & Chr(44) & Chr(34) & ",";")

WHERE Field1 Like "*" & Chr(34) & Chr(44) & Chr(34) & "*";

Can anyone tell me what I'm doing wrong?

Thank you in advance.

--
RJF

You did not say how you are doing this. Assuming you are using the replace
function

Replace([SomeField],Chr(34),"") will replace a quotation mark (") with a
zero-length string

You can search for records that have a quotation mark using
Field: SomeField
Criteria: LIKE "*""*"

Or if that is a bit confusing
Criteria: LIKE "*" & Chr(34) & "*"


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have a table with multiple fields.
In more then one field there are character as { } or ""
I want to remove these characters.

How can I remove these characters? I know already use a code to replace
text
and it is preffered to add these removal to the code

Thanks

 

[Douglas J. Steele]

I don't think you need the quotes in the 2nd parameter of the Replace
function. However, you do need quotes around the search string in the Where
clause:

UPDATE ERSCUST
SET Field1 = Replace([Field1],Chr(34) & Chr(44) & Chr(34),";")
WHERE Field1 Like "'*" & Chr(34) & Chr(44) & Chr(34) & "*'"

Exagerated for clarity, the last line is

WHERE Field1 Like " ' * " & Chr(34) & Chr(44) & Chr(34) & " * ' "

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hopefully, you can help me out.

I'm creating a query that will find and replace the "," characters with ;
(double quote comma double quote)

I can't seem to get the syntax correct. This is what I have now:

UPDATE ERSCUST

SET Field1 = Replace([Field1]," & Chr(34) & Chr(44) & Chr(34) & ",";")

WHERE Field1 Like "*" & Chr(34) & Chr(44) & Chr(34) & "*";

Can anyone tell me what I'm doing wrong?

Thank you in advance.

--
RJF

You did not say how you are doing this. Assuming you are using the
replace
function

Replace([SomeField],Chr(34),"") will replace a quotation mark (") with a
zero-length string

You can search for records that have a quotation mark using
Field: SomeField
Criteria: LIKE "*""*"

Or if that is a bit confusing
Criteria: LIKE "*" & Chr(34) & "*"


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have a table with multiple fields.
In more then one field there are character as { } or ""
I want to remove these characters.

How can I remove these characters? I know already use a code to replace
text
and it is preffered to add these removal to the code

Thanks

 

[RJF]

Thank you for responding so quickly, Douglas.

I copied and pasted you code in my query. When I run the query now, I get
the message that it's going to update 0 records. However, I know there are
records with "," in them. Here's a sample of what's in the field that I'm
trying to update:

"1314861","KIP Copy Job","mailroom","\\mailroom"

I want "1314861;KIP Copy Job;mailroom;\\mailroom"

Any idea what I'm going wrong?

Thank you again.

--
RJF

I don't think you need the quotes in the 2nd parameter of the Replace
function. However, you do need quotes around the search string in the Where
clause:

UPDATE ERSCUST
SET Field1 = Replace([Field1],Chr(34) & Chr(44) & Chr(34),";")
WHERE Field1 Like "'*" & Chr(34) & Chr(44) & Chr(34) & "*'"

Exagerated for clarity, the last line is

WHERE Field1 Like " ' * " & Chr(34) & Chr(44) & Chr(34) & " * ' "

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hopefully, you can help me out.

I'm creating a query that will find and replace the "," characters with ;
(double quote comma double quote)

I can't seem to get the syntax correct. This is what I have now:

UPDATE ERSCUST

SET Field1 = Replace([Field1]," & Chr(34) & Chr(44) & Chr(34) & ",";")

WHERE Field1 Like "*" & Chr(34) & Chr(44) & Chr(34) & "*";

Can anyone tell me what I'm doing wrong?

Thank you in advance.

--
RJF

You did not say how you are doing this. Assuming you are using the
replace
function

Replace([SomeField],Chr(34),"") will replace a quotation mark (") with a
zero-length string

You can search for records that have a quotation mark using
Field: SomeField
Criteria: LIKE "*""*"

Or if that is a bit confusing
Criteria: LIKE "*" & Chr(34) & "*"


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have a table with multiple fields.
In more then one field there are character as { } or ""
I want to remove these characters.

How can I remove these characters? I know already use a code to replace
text
and it is preffered to add these removal to the code

Thanks

 

[RJF]

Hi Douglas,

I changed your code to this and it seemed to do the trick:

UPDATE ERSCUST

SET Field1 = Replace([Field1], Chr(34) & Chr(34) & Chr(44) & Chr(34) &
Chr(34),";")

WHERE Field1 Like "'* Chr(34) & Chr(34) & Chr(44) & Chr(34) & Chr(34) *'" ;

The only thing different than yours is, I took out the single quotes in the
Where clause.

Thank you so much for your help. You're awesome as always!

--
RJF

Thank you for responding so quickly, Douglas.

I copied and pasted you code in my query. When I run the query now, I get
the message that it's going to update 0 records. However, I know there are
records with "," in them. Here's a sample of what's in the field that I'm
trying to update:

"1314861","KIP Copy Job","mailroom","\\mailroom"

I want "1314861;KIP Copy Job;mailroom;\\mailroom"

Any idea what I'm going wrong?

Thank you again.

--
RJF

I don't think you need the quotes in the 2nd parameter of the Replace
function. However, you do need quotes around the search string in the Where
clause:

UPDATE ERSCUST
SET Field1 = Replace([Field1],Chr(34) & Chr(44) & Chr(34),";")
WHERE Field1 Like "'*" & Chr(34) & Chr(44) & Chr(34) & "*'"

Exagerated for clarity, the last line is

WHERE Field1 Like " ' * " & Chr(34) & Chr(44) & Chr(34) & " * ' "

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hopefully, you can help me out.

I'm creating a query that will find and replace the "," characters with ;
(double quote comma double quote)

I can't seem to get the syntax correct. This is what I have now:

UPDATE ERSCUST

SET Field1 = Replace([Field1]," & Chr(34) & Chr(44) & Chr(34) & ",";")

WHERE Field1 Like "*" & Chr(34) & Chr(44) & Chr(34) & "*";

Can anyone tell me what I'm doing wrong?

Thank you in advance.

--
RJF


:

You did not say how you are doing this. Assuming you are using the
replace
function

Replace([SomeField],Chr(34),"") will replace a quotation mark (") with a
zero-length string

You can search for records that have a quotation mark using
Field: SomeField
Criteria: LIKE "*""*"

Or if that is a bit confusing
Criteria: LIKE "*" & Chr(34) & "*"


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have a table with multiple fields.
In more then one field there are character as { } or ""
I want to remove these characters.

How can I remove these characters? I know already use a code to replace
text
and it is preffered to add these removal to the code

Thanks

 

[RJF]

Sorry Douglas.

This is actually what I changed the code to:

UPDATE ERSCUST
SET Field1 = Replace([Field1],Chr(34) & Chr(44) & Chr(34),";")
WHERE Field1 Like "*" & Chr(34) & Chr(44) & Chr(34) & "*";

I copied the wrong code in the last post.

Again, thank you so much for your help.

--
RJF

Hi Douglas,

I changed your code to this and it seemed to do the trick:

UPDATE ERSCUST

SET Field1 = Replace([Field1], Chr(34) & Chr(34) & Chr(44) & Chr(34) &
Chr(34),";")

WHERE Field1 Like "'* Chr(34) & Chr(34) & Chr(44) & Chr(34) & Chr(34) *'" ;

The only thing different than yours is, I took out the single quotes in the
Where clause.

Thank you so much for your help. You're awesome as always!

--
RJF

Thank you for responding so quickly, Douglas.

I copied and pasted you code in my query. When I run the query now, I get
the message that it's going to update 0 records. However, I know there are
records with "," in them. Here's a sample of what's in the field that I'm
trying to update:

"1314861","KIP Copy Job","mailroom","\\mailroom"

I want "1314861;KIP Copy Job;mailroom;\\mailroom"

Any idea what I'm going wrong?

Thank you again.

--
RJF

I don't think you need the quotes in the 2nd parameter of the Replace
function. However, you do need quotes around the search string in the Where
clause:

UPDATE ERSCUST
SET Field1 = Replace([Field1],Chr(34) & Chr(44) & Chr(34),";")
WHERE Field1 Like "'*" & Chr(34) & Chr(44) & Chr(34) & "*'"

Exagerated for clarity, the last line is

WHERE Field1 Like " ' * " & Chr(34) & Chr(44) & Chr(34) & " * ' "

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hopefully, you can help me out.

I'm creating a query that will find and replace the "," characters with ;
(double quote comma double quote)

I can't seem to get the syntax correct. This is what I have now:

UPDATE ERSCUST

SET Field1 = Replace([Field1]," & Chr(34) & Chr(44) & Chr(34) & ",";")

WHERE Field1 Like "*" & Chr(34) & Chr(44) & Chr(34) & "*";

Can anyone tell me what I'm doing wrong?

Thank you in advance.

--
RJF


:

You did not say how you are doing this. Assuming you are using the
replace
function

Replace([SomeField],Chr(34),"") will replace a quotation mark (") with a
zero-length string

You can search for records that have a quotation mark using
Field: SomeField
Criteria: LIKE "*""*"

Or if that is a bit confusing
Criteria: LIKE "*" & Chr(34) & "*"


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have a table with multiple fields.
In more then one field there are character as { } or ""
I want to remove these characters.

How can I remove these characters? I know already use a code to replace
text
and it is preffered to add these removal to the code

Thanks