Validation within String

[dtay1132]

I have a data set of 2100 records (this time, the data sets are different each time). I need to check whether all 9 characters of a field are numerical. The data is stored in a text field. If there is one or more position that is not numerical, I need to produce a report with that field (I also have a unique ID for each record that I'd display).

I created a query that produces a separate column for each of the 9 positions (POS1, POS2, POS3....you get the idea).
In the query, I can do a manual descending sort to find characters that come after 9. I'd really like to automate this. Any suggestions. Not so good at VB. Thanks

 

[Golom]

Maybe I'm missing something about your requirement but would something like this work without creating separate fields?

Select * From tbl
Where NOT IsNumeric(txtField)

 

[dtay1132]

thanks for the suggestion, but it returned all the records in the set. When I switched it and when for "where ISNumeric (txtField), No records were listed and I know for certain there is one.

 

[PHV]

And something like this ?
WHERE Not (Trim(txtField & '') Like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dtay1132]

I ended up mutliplying each separate text number by 1 to make the value numeric. Then I summed the entire 9 digits. anything that returns #Error has a problem. I got this from another post in the forum.