Delete everything that is not a number

  • Thread starter Thread starter Jason Walter
  • Start date Start date
J

Jason Walter

I am converting a database from a CSV file from an old Dos program into
Access 2003.

When using the Dos program they needed to add a field to hold an account
number, but did not have that capability so they just added the number into
an existing text field.

Now I need to remove the text from that field for about 1300 records. Most
include some text , then about 9 spaces, then the number which begins with
00. Most begin with 00. I need to keep that part of the number also.

How can I do an update query to make this happen.

Thanks,
Jason
 
Jason,
How about giving us some more help so that we might be better able to help
you.

What version of Access?
Is all the data consistent in format?
Give us a few examples the actual data.
Give us an example of what you would like it to end up as.
You posted that 'most' start with 00. What else will they start with?
Is it always the last x number of characters from the right?
Is there a chance that there will be a 00 elsewhere in the field that is not
part of the number to be changed?
 
I am converting a database from a CSV file from an old Dos program into
Access 2003.

When using the Dos program they needed to add a field to hold an account
number, but did not have that capability so they just added the number into
an existing text field.

Now I need to remove the text from that field for about 1300 records. Most
include some text , then about 9 spaces, then the number which begins with
00. Most begin with 00. I need to keep that part of the number also.

How can I do an update query to make this happen.

Thanks,
Jason
Click to expand...

I'd suggest using a Text field rather than a Number to store this
field, even if it consists of numeric characters - you'll never be
doing math with an account number, and if you want to keep the leading
zeros you *must* use Text.

Assuming that there is always at least one blank between the two
portions of the field, you can update the account number field to

Trim(Mid([textfield], InStr([textfield], " ")))
 
That could work IF...

All of the numbers that have some text in front of them do have at least 1
space, BUT some of the fields do not have any text. These must have been
entered correctly because there is no space, just the account number.

I can sort the column so all of the number only fields are at the top.

Is there a way to apply your update to record 221 and up?



Thanks for the help.

Jason

John Vinson said:
I am converting a database from a CSV file from an old Dos program into
Access 2003.

When using the Dos program they needed to add a field to hold an account
number, but did not have that capability so they just added the number into
an existing text field.

Now I need to remove the text from that field for about 1300 records. Most
include some text , then about 9 spaces, then the number which begins with
00. Most begin with 00. I need to keep that part of the number also.

How can I do an update query to make this happen.

Thanks,
Jason
Click to expand...

I'd suggest using a Text field rather than a Number to store this
field, even if it consists of numeric characters - you'll never be
doing math with an account number, and if you want to keep the leading
zeros you *must* use Text.

Assuming that there is always at least one blank between the two
portions of the field, you can update the account number field to

Trim(Mid([textfield], InStr([textfield], " ")))
Click to expand...
 
I think I figured it out. I ran the update and it worked with almost all of
them. I can manually edit the rest.

Thanks
Jason
John Vinson said:
I am converting a database from a CSV file from an old Dos program into
Access 2003.

When using the Dos program they needed to add a field to hold an account
number, but did not have that capability so they just added the number into
an existing text field.

Now I need to remove the text from that field for about 1300 records. Most
include some text , then about 9 spaces, then the number which begins with
00. Most begin with 00. I need to keep that part of the number also.

How can I do an update query to make this happen.

Thanks,
Jason
Click to expand...

I'd suggest using a Text field rather than a Number to store this
field, even if it consists of numeric characters - you'll never be
doing math with an account number, and if you want to keep the leading
zeros you *must* use Text.

Assuming that there is always at least one blank between the two
portions of the field, you can update the account number field to

Trim(Mid([textfield], InStr([textfield], " ")))
Click to expand...
 
That could work IF...

All of the numbers that have some text in front of them do have at least 1
space, BUT some of the fields do not have any text. These must have been
entered correctly because there is no space, just the account number.
Click to expand...

Just use a criterion of

LIKE "* *"

to select only records which DO have a space.
I can sort the column so all of the number only fields are at the top.
Click to expand...

Update queries run "all at once" and are not dependent on sorting, so
that's irrelevant.
Is there a way to apply your update to record 221 and up?
Click to expand...

No, since Access tables do not have record numbers. There IS no
"record 221"; the table is an unordered "bag" of data.
 
Back
Top