By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,863 Members | 853 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,863 IT Pros & Developers. It's quick & easy.

search table and remove ,

P: n/a
I'd like a query that searches through a table (Table1) and looks for a
comma in just one particular field (Field1), and removes just that comma if
found...

Anyone help with this one?

Ta
Lap
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
UPDATE Table1 SET Table1.Field1 = Replace(Nz([Table1].[Field1],""),",","");
--
Wayne Morgan
"Lapchien" <cc****@nospam.eclipse.co.uk> wrote in message
news:10***************@ananke.eclipse.net.uk...
I'd like a query that searches through a table (Table1) and looks for a
comma in just one particular field (Field1), and removes just that comma if
found...

Nov 12 '05 #2

P: n/a
Thanks Wayne - can you explain what is happening?

Ta
"Wayne Morgan" <co***************************@hotmail.com> wrote in message
news:Yw*****************@newssvr33.news.prodigy.co m...
UPDATE Table1 SET Table1.Field1 = Replace(Nz([Table1].[Field1],""),",","");

--
Wayne Morgan
"Lapchien" <cc****@nospam.eclipse.co.uk> wrote in message
news:10***************@ananke.eclipse.net.uk...
I'd like a query that searches through a table (Table1) and looks for a
comma in just one particular field (Field1), and removes just that comma if found...


Nov 12 '05 #3

P: n/a
Certainly, you should use anything without understanding it. What I've written is the SQL
behind a query. This type of query is called an Update Query. If you open a new query in
design mode then go to SQL view (View|SQL View) you can paste this in there. I used Table1
and Field1 as you had mentioned, adjust these if your table and field names are actually
different.

What this will do is use the VBA function Replace to replace a comma (,) with an empty
string ("") where ever it may find it in the data of Field1. The Nz is to send an empty
string to the function if the value of the field is Null. Replace will return an error is
it is given a Null value.

An Update Query updates the data in a recordset to whatever parameters you set. You can
replace one character with another, as we have here, or you can increase all the prices in
your price list by 10%. You can use parameters on the query, just as you can with a normal
Select Query, to limit which records will be acted on.

--
Wayne Morgan
"Lapchien" <cc****@nospam.eclipse.co.uk> wrote in message
news:10***************@ananke.eclipse.net.uk...
Thanks Wayne - can you explain what is happening?

Ta
"Wayne Morgan" <co***************************@hotmail.com> wrote in message
news:Yw*****************@newssvr33.news.prodigy.co m...
UPDATE Table1 SET Table1.Field1 =

Replace(Nz([Table1].[Field1],""),",","");

Nov 12 '05 #4

P: n/a
Thanks!
--
Thanks,
Chris
cc****@NOSPAMeclipse.co.uk

"Wayne Morgan" <co***************************@hotmail.com> wrote in message
news:Xg*****************@newssvr31.news.prodigy.co m...
Certainly, you should use anything without understanding it. What I've written is the SQL behind a query. This type of query is called an Update Query. If you open a new query in design mode then go to SQL view (View|SQL View) you can paste this in there. I used Table1 and Field1 as you had mentioned, adjust these if your table and field names are actually different.

What this will do is use the VBA function Replace to replace a comma (,) with an empty string ("") where ever it may find it in the data of Field1. The Nz is to send an empty string to the function if the value of the field is Null. Replace will return an error is it is given a Null value.

An Update Query updates the data in a recordset to whatever parameters you set. You can replace one character with another, as we have here, or you can increase all the prices in your price list by 10%. You can use parameters on the query, just as you can with a normal Select Query, to limit which records will be acted on.

--
Wayne Morgan
"Lapchien" <cc****@nospam.eclipse.co.uk> wrote in message
news:10***************@ananke.eclipse.net.uk...
Thanks Wayne - can you explain what is happening?

Ta
"Wayne Morgan" <co***************************@hotmail.com> wrote in message news:Yw*****************@newssvr33.news.prodigy.co m...
UPDATE Table1 SET Table1.Field1 =

Replace(Nz([Table1].[Field1],""),",","");


Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.