Fastest way to return every record where "IL" is in any field?

[keun]

I have a database with a bunch of records. When the entry people were entering stuff, they put IL in any field where an entry in the original source document was illegible. I need to go through the database and find every instance of IL.

I know there must be a fast way to do this other than making a query the old fashioned way (Design View with each Field dragged down and a bunch of ORs)

 

[ByteMyzer]

Sorry to say, this is pretty much your only option. However, that's not to say that the query needs to be created manually. The following code can automatically create this query based on a specified table name:

Public Sub Build_IL_Query(ByVal sTableName As String)
    
    Dim sSQL As String
    Dim fld As DAO.Field
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    
    sSQL = "SELECT T1.* FROM [" & sTableName & "] AS T1 WHERE "
    
    Set db = CurrentDb
    For Each fld In db.TableDefs(sTableName).Fields
        If fld.Type = dbText Then
            sSQL = sSQL & "T1.[" & fld.Name & "] = 'IL' OR "
        End If
    Next
    
    sSQL = Left(sSQL, Len(sSQL) - 4)
    Set qdf = db.CreateQueryDef("qry_" & sTableName, sSQL)
    qdf.Close
    Set qdf = Nothing
    
    db.QueryDefs.Refresh
    db.Close
    Set db = Nothing
    
End Sub

 

[keun]

Thanks for that. I ended up doing it manually, but with a bunch of copying and pasting, it only took a few minutes.