Finding alpha characters in list of numbers

[unknownandy]

i'm trying to select (validate) a list of StockCodes that should be 8 digit numbers e.g 00400100 that are stored in a text field.

what i want is to find the records, for a report, that contain NOT numbers. i.e. the text field contains alpha characters examples: 00 400 100 or space00400100 or 00400100space

hope that your able to help. my access/vba knowledge is limited but growing

 

[yellow]

IsNumeric may be able to help you. This will return a True or False value. For example:

IsNumeric("12345")

Would return true

IsNumeric("12 345")

Would return false.

 

[unknownandy]

Many thanks the IsNumeric works a treat for those StockCodes that contain a space or indeed anyother non-numeric characters. How ever its doesn't spot those StockCodes with spaces at the begining or end of an eight numeric digit block.

It's a bit difficult to spot these as the application generating the data just stores them as text but will diffirentiate between the preceeding or trailing spaced codes.

Have been looking at a lenght type function to maybe get round/over this bit.

but cheers for the tip
Andy

 

[Jack Cowley]

You can use the Trim() function to remove leading and trailing spaces from the string and then you can use IsNumeric to check the balance of the characters in the string...

hth,
Jack

 

[glynch]

Is it true that you want to identify and print to the report the stock codes that have traing or leading zeros? If so I would run each value through a series of tests and send it to the report if it fails any of them.

First do IsNumeric, that will catch all of those with alphas or internal spaces, then do Len(Trim(myStockCode)), and any stock code that returns a value less than 8 gets sent to the report.