Remove Quotes from Text Strings

[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 "*""*";

 

[Clifford Bass]

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

 

[John Spencer]

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
'====================================================

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

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 "*""*";

 

[tkelley via AccessMonster.com]

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 "*""*";

 

[stephiesunny]

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

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 "*""*";

 

[stephiesunny]

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

 

[tkelley via AccessMonster.com]

My pleasure. Chr(34) is simply the ascii designation for a double-quote. I
like to use it since it pops out and you know exactly what it is right away.
That way I don't have to deal with a slew of "'''"""""""'' to decipher to
understand what I'm trying to do. btw: Chr(39) is a single quote.

You can see them all here, among other places.

http://danshort.com/ASCIImap/

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

I think you could take out the WHERE clause and just use:

[quoted text clipped - 19 lines]

Replace(PaymentNet_Transactions.[Merchant],Chr(34),"")
WHERE PaymentNet_Transaction.[Merchant] LIKE "*""*";

 

[Clifford Bass]

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