Update query - 1 table

[uarctos]

I have 1 table with ~217k records, 6 fields (Site_id, Date, parameter,
value, remark, value_orig). Data in the value field are text strings, but are
numbers. They also contain ">" and "<" chars, null values, ZLS, white space
and other unwanted text. I've written an SQL select query to get only
"numeric" values of the strings. I want to take that output and put in in the
value_orig field.

Here's my select SQL:
SELECT Table1.*
FROM Table1
WHERE (((Table1.value)<>"" And (Table1.value) Not Like " *" And
(Table1.value) Not Like ">*" And (Table1.value) Not Like "<*") AND
((IsNumeric([value]))=True) AND ((IsNull([value]))=False))
ORDER BY Table1.value;

Here's my update SQL:
UPDATE Table1
SET value_orig = value
WHERE (((Table1.value)<>"" And (Table1.value) Not Like " *" And
(Table1.value) Not Like ">*" And (Table1.value) Not Like "<*") AND
((IsNumeric([value]))=True) AND ((IsNull([value]))=False));

I had to remove the ORDER BY function to get it to run at all, but it
returns only "blank" records for the vlaue_orig field. I tried making update
queries for each of the individual criteria - no luck.

many thanks in advance

 

[John W. Vinson]

I have 1 table with ~217k records, 6 fields (Site_id, Date, parameter,
value, remark, value_orig). Data in the value field are text strings, but are
numbers. They also contain ">" and "<" chars, null values, ZLS, white space
and other unwanted text. I've written an SQL select query to get only
"numeric" values of the strings. I want to take that output and put in in the
value_orig field.

Here's my select SQL:
SELECT Table1.*
FROM Table1
WHERE (((Table1.value)<>"" And (Table1.value) Not Like " *" And
(Table1.value) Not Like ">*" And (Table1.value) Not Like "<*") AND
((IsNumeric([value]))=True) AND ((IsNull([value]))=False))
ORDER BY Table1.value;

Here's my update SQL:
UPDATE Table1
SET value_orig = value
WHERE (((Table1.value)<>"" And (Table1.value) Not Like " *" And
(Table1.value) Not Like ">*" And (Table1.value) Not Like "<*") AND
((IsNumeric([value]))=True) AND ((IsNull([value]))=False));

I had to remove the ORDER BY function to get it to run at all, but it
returns only "blank" records for the vlaue_orig field. I tried making update
queries for each of the individual criteria - no luck.

many thanks in advance

Nowhere in your query are you constructing a new value stripped of special
characters. All you're doing here is filtering the recordset to return only
those records where the (badly named, it's a reserved word) Value field is
already "clean".

In addition, the only criterion you need to do this is the IsNumeric function
call - if the field is NULL, a zero length string, or contains text, IsNumeric
will be False.

I gather that what you want is to convert " >3512" to 3512, "" to NULL,
" 1143" to 1143 and so on? If so you'll need some VBA code. If so, try
updating value_orig to:

Val(Replace(Replace(Replace([Value], " ", ""), ">", ""), "<", ""))

assuming that value_orig is a Number field; if it's also text, leave off the
Val( and the last ).

John W. Vinson [MVP]

 

[uarctos]

John,
The value_orig field is text. Is this what you're saying to do?

UPDATE Table1 SET Table1.value_orig = Replace(Replace(Replace([Value],"
",""),">",""),"<","");

thanks,

I have 1 table with ~217k records, 6 fields (Site_id, Date, parameter,
value, remark, value_orig). Data in the value field are text strings, but are
numbers. They also contain ">" and "<" chars, null values, ZLS, white space
and other unwanted text. I've written an SQL select query to get only
"numeric" values of the strings. I want to take that output and put in in the
value_orig field.

Here's my select SQL:
SELECT Table1.*
FROM Table1
WHERE (((Table1.value)<>"" And (Table1.value) Not Like " *" And
(Table1.value) Not Like ">*" And (Table1.value) Not Like "<*") AND
((IsNumeric([value]))=True) AND ((IsNull([value]))=False))
ORDER BY Table1.value;

Here's my update SQL:
UPDATE Table1
SET value_orig = value
WHERE (((Table1.value)<>"" And (Table1.value) Not Like " *" And
(Table1.value) Not Like ">*" And (Table1.value) Not Like "<*") AND
((IsNumeric([value]))=True) AND ((IsNull([value]))=False));

I had to remove the ORDER BY function to get it to run at all, but it
returns only "blank" records for the vlaue_orig field. I tried making update
queries for each of the individual criteria - no luck.

many thanks in advance

Nowhere in your query are you constructing a new value stripped of special
characters. All you're doing here is filtering the recordset to return only
those records where the (badly named, it's a reserved word) Value field is
already "clean".

In addition, the only criterion you need to do this is the IsNumeric function
call - if the field is NULL, a zero length string, or contains text, IsNumeric
will be False.

I gather that what you want is to convert " >3512" to 3512, "" to NULL,
" 1143" to 1143 and so on? If so you'll need some VBA code. If so, try
updating value_orig to:

Val(Replace(Replace(Replace([Value], " ", ""), ">", ""), "<", ""))

assuming that value_orig is a Number field; if it's also text, leave off the
Val( and the last ).

John W. Vinson [MVP]

 

[John W. Vinson]

John,
The value_orig field is text. Is this what you're saying to do?

UPDATE Table1 SET Table1.value_orig = Replace(Replace(Replace([Value],"
",""),">",""),"<","");

Yep.

Back up your database first just to be safe.

John W. Vinson [MVP]

 

[uarctos]

When I run this SQL:
UPDATE Table1 SET Table1.value_orig = Replace(Replace(Replace([Value],"
",""),">",""),"<","");

- using "View", the output is only the value_orig field with no content for
all records.

John,
The value_orig field is text. Is this what you're saying to do?

UPDATE Table1 SET Table1.value_orig = Replace(Replace(Replace([Value],"
",""),">",""),"<","");

Yep.

Back up your database first just to be safe.

John W. Vinson [MVP]

 

[Douglas J. Steele]

Viewing an Update query really doesn't show you anything useful. You have to
run the query and look at what's in the table afterwards.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

When I run this SQL:
UPDATE Table1 SET Table1.value_orig = Replace(Replace(Replace([Value],"
",""),">",""),"<","");

- using "View", the output is only the value_orig field with no content
for
all records.

John,
The value_orig field is text. Is this what you're saying to do?

UPDATE Table1 SET Table1.value_orig = Replace(Replace(Replace([Value],"
",""),">",""),"<","");

Yep.

Back up your database first just to be safe.

John W. Vinson [MVP]

 

[uarctos]

Thanks - when i "run" it ... it fails to update 150902 records due to a type
conversion fail ure, 0 records due to key violations, 0 records due to lock
violations, 0 records due to validation rule violations. I baile dout of it &
am looking at primary key assignments & permissions.

Viewing an Update query really doesn't show you anything useful. You have to
run the query and look at what's in the table afterwards.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

When I run this SQL:
UPDATE Table1 SET Table1.value_orig = Replace(Replace(Replace([Value],"
",""),">",""),"<","");

- using "View", the output is only the value_orig field with no content
for
all records.

On Thu, 19 Jul 2007 11:10:03 -0700, uarctos

John,
The value_orig field is text. Is this what you're saying to do?

UPDATE Table1 SET Table1.value_orig = Replace(Replace(Replace([Value],"
",""),">",""),"<","");

Yep.

Back up your database first just to be safe.

John W. Vinson [MVP]

 

[John Spencer]

IF your code was as posted, I suspect you have a problem with the field name
Value not being Value_Orig in the third line below

UPDATE Table1
SET Table1.value_orig =
Replace(Replace(Replace([Value_Orig]," ",""),">",""),"<","")
WHERE [Value_Orig] Is Not Null

Is that the actual SQL you are trying to run or did you post generic sample
code? Also I would look out for the code above generating a zero-length
string, so if you are really paranoid you might Change the WHERE clause to


WHERE Len(Replace(Replace(Replace([Value_Orig] & "" ,"
",""),">",""),"<","")) > 0


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

Thanks - when i "run" it ... it fails to update 150902 records due to a
type
conversion fail ure, 0 records due to key violations, 0 records due to
lock
violations, 0 records due to validation rule violations. I baile dout of
it &
am looking at primary key assignments & permissions.

Viewing an Update query really doesn't show you anything useful. You have
to
run the query and look at what's in the table afterwards.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

When I run this SQL:
UPDATE Table1 SET Table1.value_orig = Replace(Replace(Replace([Value],"
",""),">",""),"<","");

- using "View", the output is only the value_orig field with no content
for
all records.

:

On Thu, 19 Jul 2007 11:10:03 -0700, uarctos

John,
The value_orig field is text. Is this what you're saying to do?

UPDATE Table1 SET Table1.value_orig =
Replace(Replace(Replace([Value],"
",""),">",""),"<","");

Yep.

Back up your database first just to be safe.

John W. Vinson [MVP]

 

[uarctos]

I went back & changed permissions, but got the same errors. I decided to run
it anyway. After all those error messages it ran successfully. I have the
"numeric" value in the value_orig field. I made a cursory glance thru it &
it looks correct.
Many thanks for your help.

PS - the table for this db was created from 80 xls files. if you're
interested in the VB that was used for that let me know. When I find the
person that named a field "value" I'm going to explain to them the concept of
reserved words.

Thanks - when i "run" it ... it fails to update 150902 records due to a type
conversion fail ure, 0 records due to key violations, 0 records due to lock
violations, 0 records due to validation rule violations. I baile dout of it &
am looking at primary key assignments & permissions.

Viewing an Update query really doesn't show you anything useful. You have to
run the query and look at what's in the table afterwards.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

When I run this SQL:
UPDATE Table1 SET Table1.value_orig = Replace(Replace(Replace([Value],"
",""),">",""),"<","");

- using "View", the output is only the value_orig field with no content
for
all records.

:

On Thu, 19 Jul 2007 11:10:03 -0700, uarctos

John,
The value_orig field is text. Is this what you're saying to do?

UPDATE Table1 SET Table1.value_orig = Replace(Replace(Replace([Value],"
",""),">",""),"<","");

Yep.

Back up your database first just to be safe.

John W. Vinson [MVP]

 

[uarctos]

Just wanted to say thanks.

I went back & changed permissions, but got the same errors. I decided to run
it anyway. After all those error messages it ran successfully. I have the
"numeric" value in the value_orig field. I made a cursory glance thru it &
it looks correct.
Many thanks for your help.

PS - the table for this db was created from 80 xls files. if you're
interested in the VB that was used for that let me know. When I find the
person that named a field "value" I'm going to explain to them the concept of
reserved words.

Thanks - when i "run" it ... it fails to update 150902 records due to a type
conversion fail ure, 0 records due to key violations, 0 records due to lock
violations, 0 records due to validation rule violations. I baile dout of it &
am looking at primary key assignments & permissions.

Viewing an Update query really doesn't show you anything useful. You have to
run the query and look at what's in the table afterwards.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


When I run this SQL:
UPDATE Table1 SET Table1.value_orig = Replace(Replace(Replace([Value],"
",""),">",""),"<","");

- using "View", the output is only the value_orig field with no content
for
all records.

:

On Thu, 19 Jul 2007 11:10:03 -0700, uarctos

John,
The value_orig field is text. Is this what you're saying to do?

UPDATE Table1 SET Table1.value_orig = Replace(Replace(Replace([Value],"
",""),">",""),"<","");

Yep.

Back up your database first just to be safe.

John W. Vinson [MVP]