Data Validation: check for special characters

[mabrande]

In vba I need to check a specific field (ID) for special characters used in that field. The characters to check for are:



' " ! * # & ^ % $ " : ; \ | < > ? @ { } [ ]

The code I have is:

strSQL = "SELECT ......... "

Set rs = db.OpenRecordset(strSQL)

rs.MoveFirst

Do While Not rs.EOF
rstTest.AddNew
rstTest("ID").value = rs![ID]
rstTest.Update
rs.MoveNext
Loop

I need to know what I should put after "SELECT

[TG_W]

It might sound a little lazy, but if I run SQL in VBA, I just build the query in 'Query Design', switch to 'SQL View', then copy over to my code.

It looks like it is going to be a long string of code from all of those characters. There might be a better way to check for special characters (and I feel like there is but can't think of it off the top of my head), but if you want to run SQL in VBA, that's a quick and dirty way to do it.

[Dal Jeanis]

1) Are you trying to eliminate all of those special characters from the field? I wouldnt try to use SQL to find those records, because some of those characters will really screw up an SQL statement. Just cycle through all the records.

   Dim rs As Recordset
   Dim db As Database
   Dim strSQL As String
   Dim strTemp As String
   Set db = CurrentDB
   strSQL = "SELECT [ID] FROM MyTable"
   Set rs = db.OpenRecordset(strSQL)
   rs.MoveFirst
   Do While Not rs.EOF
      strTemp = rs![ID]
      strTemp = Replace(strTemp,"'","")
      strTemp = Replace(strTemp,"""","")
      strTemp = Replace(strTemp,"!","")
      strTemp = Replace(strTemp,"*","")
  ... and so on for each of your unwanted characters ...
      rs![ID] = strTemp
      rs.Update
      rs.MoveNext
   Loop

2) ID is a terrible name for a field. That's not an autokey field created by Access, is it?

[mabrande]

Hi, thanks for your reply. No, I am not trying to eliminate these characters. The idea is to find all records which have such a character in the ID field (I will change that name for sure) (+ also other fields) and copy these record id's into a new table. This new table will then contain all records which have unwanted characters in certain fields.

[mabrande]

It might sound a little lazy, but if I run SQL in VBA, I just build the query in 'Query Design', switch to 'SQL View', then copy over to my code.

It looks like it is going to be a long string of code from all of those characters. There might be a better way to check for special characters (and I feel like there is but can't think of it off the top of my head), but if you want to run SQL in VBA, that's a quick and dirty way to do it.

Yes, that is what I normally do as well. I am also very lazy. But try this one in design and copy to vba and see what happens. It won't work since you use * and ' and [ and ] anf " which vba handles differently.

[Dal Jeanis]

Then do the same method to pass through the database but use INSTR to test for the existence of the characters. If any of them exist, then copy the record "insert" into the other table.

IF IIF(NZ(Instr(1,[ID],"'"),0) > 0) OR _
   IIF(NZ(Instr(1,[ID],""""),0) > 0) OR _
   IIF(NZ(Instr(1,[ID],"*"),0) > 0) OR _
   etc
   IIF(NZ(Instr(1,[ID],"["),0) > 0) OR _
   IIF(NZ(Instr(1,[ID],"]"),0) > 0) Then
  ... copy the record
End IF

[mabrande]

I have this but gives a compile error:

strSQL = "SELECT TEST.[CLIENT ID] FROM TEST;"

Set rs = db.OpenRecordset(strSQL)

rs.MoveFirst
Do While Not rs.EOF
strTemp = rs![CLIENT ID]
iIf(Nz(InStr(1, rs![CLIENT ID], "'"), 0) > 0) or _
iif(Nz(InStr(1, rs![CLIENT ID], """"), 0) > 0) Or _
Iif(Nz(InStr(1, rs![CLIENT ID], "*"), 0) > 0) Or _
Iif(Nz(InStr(1, rs![CLIENT ID], "["), 0) > 0) Or _
Iif(Nz(InStr(1, rs![CLIENT ID], "]"), 0) > 0) then
rstAllIssues("Value").value = rs![CLIENT ID]
End If
rs.Update
rs.MoveNext
Loop
End If

[Dal Jeanis]

Sorry, you need one real "IF" in front of all those IIF's. You'll also need one IIF for each of your relevant characters, once you get this working.

[mabrande]

Sorry, you need one real "IF" in front of all those IIF's. You'll also need one IIF for each of your relevant characters, once you get this working.

Hi Dal,

If I change the first 'IIF' into 'IF' I receive the following error: 'Argument not optional'

Thinking about this: would it be possible to put all these strange characters into a new table and then create something in vba to check for them. From a maintainability point of view this would be even better. Any thoughts about that?

[Dal Jeanis]

IIF is a a function. IF is the test itself.

IF IIF(NZ(Instr(1,[ID],"'"),0) > 0) OR _
   IIF(NZ(Instr(1,[ID],""""),0) > 0) OR _
   IIF(NZ(Instr(1,[ID],"*"),0) > 0) OR _
   etc
   IIF(NZ(Instr(1,[ID],"["),0) > 0) OR _
   IIF(NZ(Instr(1,[ID],"]"),0) > 0) Then
  ... copy the record
End IF

[ssanfu]

All of those IF's drive me to drink. Don't need the IIF's. But the comparisons get really long.
So I came up with a different method.

But first, I would recommend not using spaces in object names.

And be careful of reserved words.... "Value" is a reserved word and shouldn't be used as object names.
Here is a list of reserved words: http://allenbrowne.com/AppIssueBadWord.html


Here is the code....

Public Sub xxx()
   Dim db As DAO.Database
   Dim rs As DAO.Recordset
   Dim rstAllIssues As DAO.Recordset

   Dim strSQL As String
   Dim strTemp As String
   Dim strLen As Integer
   Dim iChar As Integer
   Dim CopyRec As Boolean
   Dim i As Integer


   Set db = CurrentDb
   CopyRec = False
   strSQL = "SELECT TEST.[CLIENT_ID] FROM TEST;"


   Set rs = db.OpenRecordset(strSQL)
   If rs.BOF And rs.EOF Then
      'no records
   Else
      Set rstAllIssues = db.OpenRecordset("AllIssues")

      rs.MoveLast
      rs.MoveFirst
      Do While Not rs.EOF
         strLen = 0
         strTemp = ""
         CopyRec = False

         strTemp = Trim(rs![CLIENT_ID])
         strLen = Len(strTemp)
         If strLen > 0 Then
            iChar = 0
            For i = 1 To strLen
               iChar = Asc(Mid(strTemp, i, 1))
               ' google "ASCII chart"
               If (iChar > 31 And iChar < 48) Or (iChar > 57 And iChar < 65) Or (iChar > 122 And iChar < 128) Then
                  CopyRec = True
                  Exit For
               End If
            Next i

            If CopyRec Then
               rstAllIssues.AddNew
               rstAllIssues("Value") = rs![CLIENT_ID]
               'other fields
               'other fields
               rstAllIssues.Update
               CopyRec = False

               '               Debug.Print "copy this record - " & strTemp
            End If  'CopyRec
         End If  ' strLen > 0

         rs.MoveNext
      Loop
   End If

   rstAllIssues.Close
   rs.Close
   Set rstAllIssues = Nothing
   Set rs = Nothing
   Set db = Nothing
End Sub

[mabrande]

Thank you so much for all your help.