Query for Characters Not in "IN statement"

[crhodus]

I have a table with close to 1 million records. Somewhere in this table are a handful of records that have characters we consider "illegal". I'm trying to write a query that will display these records.

The "Legal" characters are as follows:

0 1 2 3 4 5 6 7 8 9 - /​

Here is a query that I've been playing around with.

SELECT * FROM tblMonthly WHERE
(Instr(1,tblMonthly.RecID,(NOT IN ("0","1","2","3","4","5","6","7","8","9","-","/")), 0));

I'm receiving the following message:
"Syntax error (missing operator) in query expression '(Instr(1,tblMonthly.RecID,(NOT IN ("0","1","2","3","4","5","6","7","8","9","-","/")), 0))'.

Is what I'm trying to do possible, or am I waisting my time?

Thanks,
Crhodus

 

[vodafrog]

This can be achieved by using a function inside your SQL statement, see below: -

SELECT LegalChar([textid]) AS Expr1, *
FROM Table2
WHERE (((LegalChar([textid]))=False));


I created this function in a module it will run for each record it processe, the module code is as follows :-

Public Function LegalChar(tmpstring As String) As Boolean
Dim legal As Variant, i As Integer, j As String

LegalChar = False
x = 1

legal = Array("0", "1", "2", "3", "4", "5", "6", "7", "8", "9", "-", "/")


For i = 0 To UBound(legal)

Do Until x > Len(tmpstring)
j = Mid(tmpstring, x, 1)
If j = legal(i) Then
LegalChar = True
Exit For
Else
LegalChar = False
Exit For
End If

x = x + 1
Loop

Next

If LegalChar = False Then
Exit Function
End If


End Function

The result of this function will either be true or false depending what it finds, you can then use this result to narrow down the records you wish to view.

Please bear in mind that this will slow down the overall query, especially when processing the number of records you have.

 

[DALeffler]

Looks like a job for Select Case:

Function fCharCheck(sPassed As String) As Boolean

fCharCheck = False

While Len(sPassed)
Select Case Left(sPassed, 1)
Case Is < "-", Is > "9", "."
fCharCheck = True 'character not allowed, show record
Exit Function
End Select

sPassed = Mid(sPassed, 2)

Wend

End Function

 

[crhodus]

Hi VodaFrog,

I tried implementing your recomendation, but was unable to successfully run it. It returned all valid and invalid records as false. I was able to use the code you shared and modify it so that I worked. It varies somewhat from the original code you submitted. It may not be the best way to implement, but it seems to work. Thought I'd share it with everyone in case it can help someone else out along the way. Thanks for your help!!

Crhodus

QUERY(Query returns all bad records)

Select * from (SELECT LegalChar(filler) AS Expr1, *
FROM tblMonthlyDatabase
ORDER BY filler) where Expr1 <> -1;

FUNCTION

Public Function LegalChar(tmpstring As String) As Boolean

Dim StringLength As Integer, x As Integer, I As Integer, Y As Integer, NumWrong As Integer, NextChar As Integer, Legal As Variant, ThisItem As String

StringLength = Len(tmpstring)
x = 1
Y = 1
NumWrong = 0
NextChar = 1

LegalChar = True

Legal = Array("0", "1", "2", "3", "4", "5", "6", "7", "8", "9", "-", "/")

For Y = 1 To StringLength
For I = 0 To UBound(Legal)

ThisItem = Mid(tmpstring, x, 1)
NumPasses = 0
NumWrong = 0

Do Until NumPasses > UBound(Legal)
If ThisItem <> Legal(NumPasses) Then
NumWrong = NumWrong + 1
Else
Exit For
End If
NumPasses = NumPasses + 1
Loop

If NumWrong >= (UBound(Legal) + 1) Then
LegalChar = False
Exit Function
End If

Next

x = x + 1

Next

End Function