Delete everything that is not a number

[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

 

[Fredg]

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?

 

[John Vinson]

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

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], " ")))

 

[Jason Walter]

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

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

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], " ")))

 

[Jason Walter]

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

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

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], " ")))

 

[John Vinson]

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.

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.

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?

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