trim first letter to leave number

[Billp]

I have a list of numbers
G898.....G999 letter followed by 3 digits
G1000 ...........G1200 letter followed by 4 digits

How do I trim /remove the letter to leave the numbers?
The letter can be any alpha.
Is it possible to do this in a simple update query?



Next question

I want to filter out G### and G#### where the G can be any letter
I currently have Like "G###" Or Like "G####" which gives me the list - I
feel it is limiting for other possibilities

Id there a wild card for a letter?

Thank you for your consideration on this question

 

[Jeff Boyce]

If you are saying you want to see what's left after removing the leftmost
character (and if you are saying there is ALWAYS only one), you could use
something like the following in a query ... no need to "update" the field:

NewValue: Mid([YourField],2)

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Spencer]

If you want to filter out (not see) any record where the field starts
with a letter followed by three or four number characters then try the
following.
LIKE "[!A-z]###" or Like "[!A-z]####"

If you want to match A to z then remove the ! (negation) operator.
LIKE "[A-z]###" or Like "[A-z]####"
returns all records that start with a letter followed by 3 or 4 numbers


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Billp]

Thanks John and Jeff,

Much appreciated I can now filter the table to show the G### etc.

Next step is to update the end result to remove the letter and leave ### or
#### this is so I can move the result to a number field form the original
text that it was.

Very much appreciate the assisatnce.
Best Regards

If you want to filter out (not see) any record where the field starts
with a letter followed by three or four number characters then try the
following.
LIKE "[!A-z]###" or Like "[!A-z]####"

If you want to match A to z then remove the ! (negation) operator.
LIKE "[A-z]###" or Like "[A-z]####"
returns all records that start with a letter followed by 3 or 4 numbers


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I have a list of numbers
G898.....G999 letter followed by 3 digits
G1000 ...........G1200 letter followed by 4 digits

How do I trim /remove the letter to leave the numbers?
The letter can be any alpha.
Is it possible to do this in a simple update query?



Next question

I want to filter out G### and G#### where the G can be any letter
I currently have Like "G###" Or Like "G####" which gives me the list - I
feel it is limiting for other possibilities

Id there a wild card for a letter?

Thank you for your consideration on this question

 

[John W. Vinson]

Next step is to update the end result to remove the letter and leave ### or
#### this is so I can move the result to a number field form the original
text that it was.

Jeff told you how.

To reiterate, the expression

Mid([fieldname], 2)

will contain the text string "999" if fieldname contains "G999".

If you want to Update a number field in the table to 999, you can take it one
step further: create an Update query updating the field to

Val(Mid([fieldname], 2))

The Mid substring function will extract a text string "999", and the Val()
function will turn that string into a number.