Drop first character in string

Question

Is it possible in a query to drop the first character in a string, based on its value?

I have a customer table that for the most part has numeric customer numbers.  There are a number of customers who's customer number starts with an "S".

For a given set of customers I would like to add thier customer numbers to arive at a hash total, but can not do so as is.

Example:

Current                                        Desired Query Result

Customer#1   13579                13579

Customer#2   24681                 24681

Customer#3   S9865                 9865

Total/Hash      Will not add       48125

Can this be done in a query using an expression or function?

Answer 1

Is it possible in a query to drop the first character in a string, based on its value...

You should be able to extract the numbers with something like,

SELECT IIF(ASC(LEFT([custno],1))>64,MID([custno],2,99),[custno]) FROM tblCustomers

Answer 2

you could use something like:

Expr1: IIf(UCase(Left([customer#],1))="S",Right([customer#],Len([customer#]-1)),[customer#])

in the field box of the query, and of course sub in the actual field name for "customer#"