Remove Quotes from Text Strings

  • Thread starter Thread starter stephiesunny
  • Start date Start date
S

stephiesunny

I'm trying to remove quotations from around text (vendor names) in my table.

Ie "Dell" to just say Dell.

This is my update, but it's asking for a parameter value and I dont' know
how else to structure my update query to just remove the quotes. Please help!!

UPDATE PaymentNet_Transactions
SET PaymentNet_Transactions.[Merchant] =
Replace(PaymentNet_Transactions.[Merchant],Chr(34),"")
WHERE PaymentNet_Transaction.[Merchant] LIKE "*""*";
 
Hi Stephie,

Any chance the quotes in the fields are the curly quotes (“ and â€)?
You could try this:

UPDATE PaymentNet_Transactions
SET PaymentNet_Transactions.[Merchant] =
Replace(Replace(Replace(PaymentNet_Transactions.[Merchant],"""",""), "“",
""), """, "")
WHERE PaymentNet_Transaction.[Merchant] LIKE "*""*" or
PaymentNet_Transaction.[Merchant] LIKE "*“*" or
PaymentNet_Transaction.[Merchant] LIKE "*â€*";

Clifford Bass
 
What parameter is it asking for? What you posted should work assuming
that the field is named Merchant and the table is named
PaymentNet_Transactions.

The other possibility is that REPLACE is not being recognized as a
function. It isn't in versions of Access prior to Access 2000 and in
Access 2000 if you haven't applied at least SP2 (if I recall correctly).

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


Clifford said:
Hi Stephie,

A slight change should do the trick:

UPDATE PaymentNet_Transactions
SET PaymentNet_Transactions.[Merchant] =
Replace(PaymentNet_Transactions.[Merchant],"""","")
WHERE PaymentNet_Transaction.[Merchant] LIKE "*""*";

Clifford Bass

stephiesunny said:
I'm trying to remove quotations from around text (vendor names) in my table.

Ie "Dell" to just say Dell.

This is my update, but it's asking for a parameter value and I dont' know
how else to structure my update query to just remove the quotes. Please help!!

UPDATE PaymentNet_Transactions
SET PaymentNet_Transactions.[Merchant] =
Replace(PaymentNet_Transactions.[Merchant],Chr(34),"")
WHERE PaymentNet_Transaction.[Merchant] LIKE "*""*";
Click to expand...
Click to expand...
 
Thank you!! That worked! I'm not entirely sure what the Chr(34) means but
this query updated my data to look how i need it to look.

:) Stephanie

tkelley via AccessMonster.com said:
I think you could take out the WHERE clause and just use:

UPDATE PaymentNet_Transactions
SET PaymentNet_Transactions.[Merchant] =
Replace(PaymentNet_Transactions.[Merchant],Chr(34),"")

If it doesn't find a Chr(34), then it's not going to execute the Replace.

However, if you want to leave it in there, try this:

WHERE PaymentNet_Transaction.[Merchant] LIKE "*" & chr(34) & "*"
I'm trying to remove quotations from around text (vendor names) in my table.

Ie "Dell" to just say Dell.

This is my update, but it's asking for a parameter value and I dont' know
how else to structure my update query to just remove the quotes. Please help!!

UPDATE PaymentNet_Transactions
SET PaymentNet_Transactions.[Merchant] =
Replace(PaymentNet_Transactions.[Merchant],Chr(34),"")
WHERE PaymentNet_Transaction.[Merchant] LIKE "*""*";
Click to expand...
Click to expand...
 
I tried this, because the quotes did look different, but it still didn't
work. I was able to use tkelley's advice and take out the quotes. Thank you
for your help!!

Stephanie
 
HI Stephanie,

Glad to hear you got it to work with Kelley's solution. It still
remains unclear as to why your initial SQL did not work. Oh well.... You are
welcome!

Clifford Bass
 
Back
Top