how to make a queyr to delete comma in a text field in access 2010

hi

i have a lot of text field that have comma like " or , and they are not in the same place in every field  like that example:
green "
gr"een
"green
green,

and i want to make a query to delete every comma in the field how can i make it?
bill201Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
For a Comma ...   ,    ?

Example ... make a BACKUP of your data before running this:

UPDATE Table1 SET
WHERE (((Table1.FIELD1)=Replace([FIELD1],",","")));


mx
0
Rey Obrero (Capricorn1)Commented:
" is not a comma
, is a comma


to delete them, use an update  query

update table
set [field]=replace(replace([field],chr(34),""),",","")

chr(34) is "
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Are you talking about a Comma or a Single quote ?

If single quote ... then


UPDATE Table1 SET
WHERE (((Table1.FIELD1)=Replace([FIELD1], Chr(39)  ,"")));

mx
0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
If double quote then


UPDATE Table1 SET
WHERE (((Table1.FIELD1)=Replace([FIELD1], Chr(34)  ,"")));
0
Rey Obrero (Capricorn1)Commented:
to include a where clause in the update query

" is not a comma
, is a comma

chr(34) is "

to delete them, use an update  query

update table
set [field]=replace(replace([field],chr(34),""),",","")
where instr([field],chr(34))>0 or instr([Field],",")>0







0
bill201Author Commented:
is dont work me
i get a message that it's update but actually i don't see any update
i speak about this kind of characters:

commas and  Quotation mark and Apostrophe

and i have at least two character in in every field
0
Rey Obrero (Capricorn1)Commented:
what doesn't work...


try this

update table
set [field]=replace(replace(replace([field],chr(34),""),",",""),chr(39),"")



post the query you are using,

better upload a copy of the db
0
bill201Author Commented:
it's don't work so i'm uploading this database but i hope that you will can open it because it have hebrew  characters

the name of this field is "subject"
calendar.zip
0
Rey Obrero (Capricorn1)Commented:
ok, can't get the correct ascii value of the character, you might be able to do so at your end

run this codes

Sub gChar()
Dim rs As DAO.Recordset, j As Integer, s As String
Set rs = CurrentDb.OpenRecordset("select top 1 subject from calendar where startdate='1/1/2011'")
s = rs(0)
Debug.Print s
For j = 1 To Len(s)
    Debug.Print Asc(Mid(s, j, 1))
    
Next
rs.Close
End Sub

Open in new window



look in the immediate window and copy and paste here what was printed



0
bill201Author Commented:
¿i get this numbers


 216
 228
 32
 225
 232
 225
 250
 32
 250
 249
 242
 216
 224

0
bill201Author Commented:
it's now in my time zone 4:00 am so i most to go sleep for a while (if not why wife will kill me :)
a big thanks for you and be in touch tomorrow
0
Rey Obrero (Capricorn1)Commented:
ok try this, (create copy of the table first)

update calendar
set [subject]=replace([subject], chr(216),"")


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rey Obrero (Capricorn1)Commented:
also run this modify codes

Sub gChar()
Dim rs As DAO.Recordset, j As Integer, s As String
Set rs = CurrentDb.OpenRecordset("select top 1 subject from calendar where startdate='1/1/2011'")

do until rs.eof
s = rs(0)

For j = 1 To Len(s)
    Debug.Print Asc(Mid(s, j, 1))
    
Next

rs.movenext
debug.print "xxxxxxx"
loop
rs.Close
End Sub

Open in new window



copy and post back the result here
0
Rey Obrero (Capricorn1)Commented:


oops, use this codes


Sub gChar()
Dim rs As DAO.Recordset, j As Integer, s As String
Set rs = CurrentDb.OpenRecordset("select top 2 subject from calendar where startdate='1/1/2011'")

do until rs.eof
s = rs(0)

For j = 1 To Len(s)
    Debug.Print Asc(Mid(s, j, 1))
    
Next

rs.movenext
debug.print "xxxxxxx"
loop
rs.Close
End Sub

Open in new window



0
bill201Author Commented:
235
 228
 32
 225
 232
 225
 250
 32
 250
 249
 242
 224
xxxxxxx
0
bill201Author Commented:
235
 228
 32
 225
 232
 225
 250
 32
 250
 249
 242
 224
xxxxxxx
 244
 248
 249
 250
 32
 229
 224
 248
 224
xxxxxxx
0
Rey Obrero (Capricorn1)Commented:
see this link and try to find the dec value of the character

http://ascii-table.com/windows-codepage.php?1255
0
Rey Obrero (Capricorn1)Commented:
see this link too


http://www.ascii.ca/cp1255.htm

http://www.ascii-codes.com/cp862.html

try this query

update calendar
set [subject]=replace(replace([subject], chr(148),""),chr(146),"")

0
bill201Author Commented:
i suceed with making copy and paste this charcter ¿
and then i make like this
update calendar
set [subject]=replace(subject,"¿","")
thanks very very much
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.