Finding certain characters in all fields

[DAmoss]

Hi,

I'm want to be be to able to search through all my access records / fields and find any illegal characters that I pre-define ie. [/'*,-] etc. These should then be written out to a text file which would contain the record numbers which contain these illegal characters.

Can anyone show me how to go about writing a procedure to perform this character checking?

Thanx in advance.

 

[JerryKlmns]

DAmoss

First you need a recordset that holds the names of the tables you have to check. If you already do know them use a table, or else query the MSysObjects (hidden-system) table where the field "Name" does not start with "MSys" and the field "Type" equals 1 (=local) or 6 (=linked). Loop the records. In each loop open a new recordset and loop the its records. Inside the 2nd loop, loop the fields of the record. At this 3rd loop, check the type of the field (to avoid non text fields) and check if it is null. Do your check for the illegal characters and if found, send the data to a sub that writes table name, PK of the record and the field name in a text file.

Do your effort on the logic and if you get stuck, TT will be happy to help you more, on this.

 

[Ed2020]

You'll need to modify this to perform a wildcard comparison, but other than that this should do what you want:

Public Function FindVal(ByVal varSearchVal As Variant) As String
    Dim ThisDB As DAO.Database
    Dim TDef As DAO.TableDef
    Dim rstSearchMe As DAO.Recordset
    Dim MyField As DAO.Field
    Dim lngRecordNumber As Long
    Dim strResults As String

    Set ThisDB = CurrentDb

    For Each TDef In ThisDB.TableDefs
        If Left$(TDef.Name, 4) <> "MSys" Then
            lngRecordNumber = 1
            Set rstSearchMe = ThisDB.OpenRecordset(TDef.Name, dbOpenSnapshot)
            
            With rstSearchMe
                .MoveFirst
                Do While Not .EOF
                    For Each MyField In rstSearchMe.Fields
                        If rstSearchMe(MyField.Name).Value = varSearchVal Then
                            strResults = strResults & "Value found in table " & TDef.Name & " in field " & MyField.Name & " at record number " & lngRecordNumber & "." & vbCrLf
                        End If
                    Next MyField
                    .MoveNext
                    lngRecordNumber = lngRecordNumber + 1
                Loop
            End With
        End If
    Next TDef
    
    FindVal = strResults
End Function

Please do not feed the trolls.....

 

[DAmoss]

Cheers Ed2020,

It looks the biz ... but I haven't managed to get it to work as yet, could you show me an example of how to actually use the FindVal function.

I tried:

strErrorChar = FindVal([-])

Is this right?

Cheers

 

[JerryKlmns]

DAmoss

Maybe

strErrorChar = FindVal("lalala-lilalo")

 

[Ed2020]

DAmoss,

Yes, you're calling it correctly.

At the moment the code is only looking for exact matches. You may want to change it to perform a wildcard search:

If rstSearchMe(MyField.Name).Value = "*" & varSearchVal & "*" Then...

Ed Metcalfe.

Please do not feed the trolls.....

 

[TheAceMan1]

How are ya DAmoss . . .

You say you want to scan all your access records for dirty characters and pump up a file with those record numbers.

What will you do with the file?

See Ya! . . . . . .

Be sure to see thread181-473997

 

[TheAceMan1]

DAmoss . . .

I took a different approach to this using SQL with dynamic criteria (setting criteria for each field to ([fieldName] [blue]LIKE "*[/'*,-]*"[/blue]). Since the SQL is returning a filtered recordset, what to return for the file became an issue as far as the [blue]record numbers[/blue] you asked for.

So what I've done is have it return the [blue]first field[/blue] of each record in error which is usually the primarykey and excellent index to hunt down the records. So any table where the primarykey is not first, should be made so. The format of the returned string is as follows:

TableName1
PK for record1
pk for record2
'
'

TableName1
PK for record1
pk for record2
'
'
Now the code:

[blue]Public Function RecErrs() As String
   Dim db As DAO.Database, rst As DAO.Recordset
   Dim tdf As TableDef, fld As Field
   Dim SQL As String, Fltr As String, Cri As String
   Dim NL As String, DQ As String, Pack As String
   
   Set db = CurrentDb
   DQ = """"
   NL = vbNewLine
   Fltr = "*[/'*,-]*"
   
   For Each tdf In db.TableDefs
      If Left(tdf.Name, 4) <> "Msys" Then
         Cri = ""
         
         For Each fld In tdf.Fields
            If Cri <> "" Then
               Cri = Cri & " OR ([" & fld.Name & "] Like " & _
                                 DQ & Fltr & DQ & ")"
            Else
               Cri = "Where ([" & fld.Name & "] Like " & _
                                 DQ & Fltr & DQ & ")"
            End If
         Next
         
         SQL = "SELECT * FROM " & tdf.Name & " " & Cri
         Set rst = db.OpenRecordset(SQL, dbReadOnly)
         
         If Not rst.BOF Then
            If Pack <> "" Then
               Pack = Pack & NL & tdf.Name & NL
            Else
               Pack = tdf.Name & NL
            End If
            
            Do
               Pack = Pack & "   " & rst.Fields(0) & NL
               rst.MoveNext
            Loop Until rst.EOF
         End If
         
         Set rst = Nothing
      End If
   Next
   
   RecErrs = Pack
   Set db = Nothing
   
End Function[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997

 

[DAmoss]

AceMan1

To answer your question 'What will you do with the file?' requires a bit more than a quick explanation, but here goes.

One of my duties in work is to receive various project returns (.csv files) which contain beneficiary details. These details have to be uploaded and processed by our local Government Office, I need to strip out any 'dirty' characters before this uploading takes place or there software won't accept them.

These quarterley returns can sometimes contain upto 15 thousand records, manually finding these 'dirty' characters is extremely time consuming to say the least.

Hence it would be easier to have my database locate them all and list where they are against a given persons details. I also have to get the projects to do the same alterations at there end as the returns are cumulative, otherwise I would be processing the same errors again and again.

I hope that answers your question?

 

[DAmoss]

Hi guys,

I can see from the various answers posted that the functions are searching through all the tables contained within my database, this is not quite what I asked, I actually meant a given table ie. 'Beneficiaries'.

Ed2020, your solution did not actually find my illegal characters when tested.

TheAceMan1, your solution did not complete due to the criteria string (Cri) being too long when stepping through, I do have a lot of fields (some with long names) to check.

Thank you for your efforts, I will keep on searching for a solution to my problem.

 

[JerryKlmns]

DAmoss

...this is not quite what I asked...

Take a look here faq181-2886 at #10.

You starting post said

...search through all my access records / fields...

So I assumed that you were reffering to all your tables in your database. Ed took the joy of codeing that, from you [don't take it wrong Ed, you were trying to help him, it's ok by me]. Still, there is no sigle line of code from you!

So, now that we have some more details,

Public Function FindVal(ByVal varSearchVal As String) As String
    Dim rstSearchMe As DAO.Recordset
    Dim MyField As DAO.Field
    Dim strResults As String

    Set rstSearchMe = CurrentDb.OpenRecordset("[b]YourTableName[/b]", dbOpenSnapshot)
    With rstSearchMe
        .MoveFirst
        Do While Not .EOF
            For Each MyField In .Fields
                If InStr(1, MyField.Value, varSearchVal) > 0 Then
                    strResults = strResults & _
                                .Fields("[b]YourPKFieldNameHere[/b]") & "|" & _
                                 MyField.Name & "|" & _
                                 MyField.Value & vbCrLf
                End If
            Next MyField
            .MoveNext
        Loop
        .Close
    End With
    Set rstSearchMe = Nothing
    FindVal = strResults
End Function

If there 's something you would ask, feel free

 

[DAmoss]

Hi JerryKlmns,

I accept I wasn't specific in saying one table only, granted, but I do take exception to the uncalled for snipe in your reply.

Still, there is no sigle line of code from you!

There was/is no code from me (as yet) because I had absolutely no idea on how to go about writing such a routine, hence the original question. Before I even started to have a go myself I was presented with 2 possible coded solutions, both greatfully accepted by the way (thank you Ed2020 & TheAceMan1)

I have been analysing these routines and stepping through them to get a feel for what they actually do, makes good sense to me anyway. Only then could I even attempt to alter/rewrite the supplied code to make it work myself.

I hope that clears up our little difference of opinion, thank you for having another look at it yourself and supplying a new solution. I will let you know how I get on shortly.

DAMoss

 

[JerryKlmns]

DAMoss

I saw no "alter/rewrite" from you and might got a "wrong" feeling about "quite what I asked". If you need some more time to understand all the above, it is ok. If you have any questions do post and I 'll still try to help. No difference of opinion, now. Give it a go and enjoy VBA.

 

[abaldwin]

How about a different approach.

Using VBA

Open the CSV file.

Read each line into a variable

Do a replace statement.

Write to a new file called "Clean" or something.

Then import, upload, or do whatever else you like.


Andy Baldwin

"Testing is the most overlooked programming language on the books!"

Ask a great question, get a great answer. Ask a vague question, get a vague answer.
Find out how to get great answers FAQ219-2884.