Update qry to eliminate alpha characters

[Lois]

Hello,
I need to delete all alpha characters at the beginning of a field. The field
alpha information varies, from 1 alpha character up to 3 alpha charactersand
the numeric digits can vary as well. Can you give me some pointers?

A12345678
AB12345
ABC12

Thanks
Lois

 

[Ken Snell \(MVP\)]

Create this update query:

UPDATE YourTableName
SET YourFieldName = Mid(YourFieldName, 2)
WHERE YourFieldName Like "[A-Z]*";

Then run the query multiple times until it updates 0 records.

 

[Lois]

Hi Ken, i am not that savvy with access, so please be patient. Do i manually
enter the 'where' statement in the criteria?

Create this update query:

UPDATE YourTableName
SET YourFieldName = Mid(YourFieldName, 2)
WHERE YourFieldName Like "[A-Z]*";

Then run the query multiple times until it updates 0 records.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Hello,
I need to delete all alpha characters at the beginning of a field. The
field
alpha information varies, from 1 alpha character up to 3 alpha
charactersand
the numeric digits can vary as well. Can you give me some pointers?

A12345678
AB12345
ABC12

Thanks
Lois

 

[Ken Snell \(MVP\)]

To create this query in the "grid" design view:

Add your table to the grid.

Add the field to the grid.

Change the query type to Update.

Put this expression in the Where: box under the field name:
Like "[A-Z]*"

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Hi Ken, i am not that savvy with access, so please be patient. Do i
manually
enter the 'where' statement in the criteria?

Create this update query:

UPDATE YourTableName
SET YourFieldName = Mid(YourFieldName, 2)
WHERE YourFieldName Like "[A-Z]*";

Then run the query multiple times until it updates 0 records.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Hello,
I need to delete all alpha characters at the beginning of a field. The
field
alpha information varies, from 1 alpha character up to 3 alpha
charactersand
the numeric digits can vary as well. Can you give me some pointers?

A12345678
AB12345
ABC12

Thanks
Lois

 

[Lois]

the mid,[field name], 2 update. is now causing me problems. after the first
run, the first alpha is deleted, but the second run, it is deleting the
number where the alpha has already been deleted.

To create this query in the "grid" design view:

Add your table to the grid.

Add the field to the grid.

Change the query type to Update.

Put this expression in the Where: box under the field name:
Like "[A-Z]*"

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Hi Ken, i am not that savvy with access, so please be patient. Do i
manually
enter the 'where' statement in the criteria?

Create this update query:

UPDATE YourTableName
SET YourFieldName = Mid(YourFieldName, 2)
WHERE YourFieldName Like "[A-Z]*";

Then run the query multiple times until it updates 0 records.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Hello,
I need to delete all alpha characters at the beginning of a field. The
field
alpha information varies, from 1 alpha character up to 3 alpha
charactersand
the numeric digits can vary as well. Can you give me some pointers?

A12345678
AB12345
ABC12

Thanks
Lois

 

[Ken Snell \(MVP\)]

Open your query in design view. Then click on Query View icon button on
toolbar, and select SQL View. Copy all the text that you see in that window,
and paste it into your reply to this post. Let us see what the SQL statement
is that you're using.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

the mid,[field name], 2 update. is now causing me problems. after the
first
run, the first alpha is deleted, but the second run, it is deleting the
number where the alpha has already been deleted.

To create this query in the "grid" design view:

Add your table to the grid.

Add the field to the grid.

Change the query type to Update.

Put this expression in the Where: box under the field name:
Like "[A-Z]*"

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Hi Ken, i am not that savvy with access, so please be patient. Do i
manually
enter the 'where' statement in the criteria?

:

Create this update query:

UPDATE YourTableName
SET YourFieldName = Mid(YourFieldName, 2)
WHERE YourFieldName Like "[A-Z]*";

Then run the query multiple times until it updates 0 records.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Hello,
I need to delete all alpha characters at the beginning of a field.
The
field
alpha information varies, from 1 alpha character up to 3 alpha
charactersand
the numeric digits can vary as well. Can you give me some pointers?

A12345678
AB12345
ABC12

Thanks
Lois

 

[Lois]

UPDATE Table1 SET Table1.[group number] = Mid([group number],2)
WHERE ((("where: [group number]") Like "[a-z]*"));

Thanks for taking the time to help me Ken!

Open your query in design view. Then click on Query View icon button on
toolbar, and select SQL View. Copy all the text that you see in that window,
and paste it into your reply to this post. Let us see what the SQL statement
is that you're using.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

the mid,[field name], 2 update. is now causing me problems. after the
first
run, the first alpha is deleted, but the second run, it is deleting the
number where the alpha has already been deleted.

To create this query in the "grid" design view:

Add your table to the grid.

Add the field to the grid.

Change the query type to Update.

Put this expression in the Where: box under the field name:
Like "[A-Z]*"

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



Hi Ken, i am not that savvy with access, so please be patient. Do i
manually
enter the 'where' statement in the criteria?

:

Create this update query:

UPDATE YourTableName
SET YourFieldName = Mid(YourFieldName, 2)
WHERE YourFieldName Like "[A-Z]*";

Then run the query multiple times until it updates 0 records.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Hello,
I need to delete all alpha characters at the beginning of a field.
The
field
alpha information varies, from 1 alpha character up to 3 alpha
charactersand
the numeric digits can vary as well. Can you give me some pointers?

A12345678
AB12345
ABC12

Thanks
Lois

 

[John W. Vinson]

UPDATE Table1 SET Table1.[group number] = Mid([group number],2)
WHERE ((("where: [group number]") Like "[a-z]*"));

Thanks for taking the time to help me Ken!

It's been a couple of hours, and Ken may have gone for the evening - I hope he
won't object if I chime in. You're including an extra Where in a quoted
string.

The text string

where: [group number]

IS in fact

LIKE "[a-z]*"

but that's not what you want!!! It's simpler:

UPDATE Table1 SET Table1.[group number] = Mid([group number],2)
WHERE ((([group number]) Like "[a-z]*"));

Hope you made a backup before running the query a second time because the
changes are irreversible!

 

[Lois]

PERFECT!!! Thank you so much! it works. I was actually testing this on dummy
data, so no worries!

You have saved me so much time and trouble.

UPDATE Table1 SET Table1.[group number] = Mid([group number],2)
WHERE ((("where: [group number]") Like "[a-z]*"));

Thanks for taking the time to help me Ken!

It's been a couple of hours, and Ken may have gone for the evening - I hope he
won't object if I chime in. You're including an extra Where in a quoted
string.

The text string

where: [group number]

IS in fact

LIKE "[a-z]*"

but that's not what you want!!! It's simpler:

UPDATE Table1 SET Table1.[group number] = Mid([group number],2)
WHERE ((([group number]) Like "[a-z]*"));

Hope you made a backup before running the query a second time because the
changes are irreversible!

 

[Ken Snell \(MVP\)]

It's been a couple of hours, and Ken may have gone for the evening - I
hope he
won't object if I chime in.

No objection at all, John. Thanks.