Repairing table using update query

  • Thread starter Thread starter LCalaway
  • Start date Start date
L

LCalaway

Hello All.
I have a a table with hundreds of records with the wrong data entered one of
the fields. The field should be blank for most of those records and contain
a number for others. I have repaired the records that will end up with a
number, but cannot repair the fields that contain an entry but need to be
blank. What is the correct update format to make a field blank under these
circumstances.
Thank you.
LCalaway
 
1. Create a new query.

2. Switch it to SQL View (View menu, from query design).

3. Paste this in:
UPDATE [Table1] SET [Field1] = Null WHERE IsNumeric([Field1]) = False;

4. Change "Table1" to your table name, and "Field1" to your field name (x
2).

5. Run the query.

This should leave you with fields that only have values Access can interpret
as numbers. You can then open the table in design view, and change the data
type of the field from Text to Number.
 
That works. Thank you very much.
LCalaway
Allen Browne said:
1. Create a new query.

2. Switch it to SQL View (View menu, from query design).

3. Paste this in:
UPDATE [Table1] SET [Field1] = Null WHERE IsNumeric([Field1]) = False;

4. Change "Table1" to your table name, and "Field1" to your field name (x
2).

5. Run the query.

This should leave you with fields that only have values Access can interpret
as numbers. You can then open the table in design view, and change the data
type of the field from Text to Number.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

LCalaway said:
Hello All.
I have a a table with hundreds of records with the wrong data entered one
of
the fields. The field should be blank for most of those records and
contain
a number for others. I have repaired the records that will end up with a
number, but cannot repair the fields that contain an entry but need to be
blank. What is the correct update format to make a field blank under
these
circumstances.
Thank you.
LCalaway
Click to expand...
Click to expand...
 
Back
Top