Modifying existing VB in Query - Exclude Alpha Characters

[wally2321]

Any help would be great. I have a Query that I need to change that is a result of a new requirement. The query is called MATERIAL - MAKE - KAUF (PDM). This query is a make query, creating the table KAUF - MAKE PDM. In the query I am using the design view, because I am a novice, and find this to be easier. In the first field (which is using the MATERIAL NUMBER field from the source table), I have the following code:


Expr1: IIf(Len([MATERIAL NUMBER])=10,"00" & [MATERIAL NUMBER],IIf(Len([MATERIAL NUMBER])=11,"0" & [MATERIAL NUMBER],[MATERIAL NUMBER]))


This in essence takes any digits that equals 10 and adds 2 digits to the beginning, digits that equals 11 ...adds 1 zero to the beginning, and anything that equals 12 digits stays the same. Only the numbers that are 10,11, or 12 digits are loaded into the new table. I would like to add a new piece of logic, which states that values that begin with an alpha character should not be included. For example if the following is the MATERIAL NUMBER in the source field: Z100011111

Even though it has 10 digits, it should not be included in the new table because it begins with an alpha character. How can I add this new logic to the above expression.

Any help would be greatly appreciated.

 

[PaulF]

create another Expression Field and use a combination of the IsNumeric and Left functions and set its Criteria Row to True

Exp2:IsNumeric(Left([Material Number],1))

PaulF

 

[MichaelRed]

Expr1: IIF(IsNumeric([Material_Number], Right("00" & [Material_Number], 12))
MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over