need to delete the first 4 numbers of each record

  • Thread starter Thread starter Jimmy
  • Start date Start date
J

Jimmy

I have 45000 records. In one field about 80% of those records are in a
format: 4numbers space and a word (any word). What I need is to delete
those 4 first numbers in every record that begins with 4 numbers. Is it
possible? Can someone help?
Thank You
Jimmy
 
You could use the IsNumeric() function to determine whether the first 4
characters in your field evaluate to a number, as in the following untested
example:

IIf(IsNumeric(Left([MyField], 4)), Mid([MyField, 6), [MyField])

Insert the above expression into the Field: row of a Select Query to test
it out. If you like it, then use the expression in the Update to: row of
an Update Query.
 
If it is a text field and the records that do not have the
numbers have blanks in the the first four positions you
could use the RIGHT function to ignore the first four
positions.

If text and does not meet above. Create a file that has
first four charcters and then set up a flag field to use
in a joined update where you only update the fields with
numbers in first four fields. A little brute force but
sometime brute over brains is in the end quicker.
 
Back
Top