Validating for invalid characters and informing user of records

[craig1988]

Hi All

I have googled, but to no avail.



I have an import function that asks the user for a file to import. Once imported, the data is queried and appended to further tables. I have no control over the data to be imported. I need to recognise any records that contain " ' " and inform the user somehow.

Any ideas?

[ranman256]

after import, run a select query on this bad item...
select * from table where [field] like "*'*"

if you have >1 bad character, put them in a table, tInvalidChars.
'
~
^


make a form, with a list box on it, lstChars. (the form is unbound)
the list box is connected to tBadChars,
put a button on the form to run this code

Public Sub btnScan_Click()
Dim qdf As QueryDef
Dim sSql As String, sWhere as string
const kQRY = "qsFindBadChars"    'name of the query to search in data table
const Q  =""""
dim i as integer
dim vChr
On Error GoTo skipit
set qdf = currentdb.querydefs(kQRY)
sSql = "Select * from table "
  'scan list box of badchars
for i = 0 to lstChars.listcount - 1
        vChr = lstChars.dataitem(i)
        sWhere = sWhere & "(like " & Q & "*" & vChr & "*" & Q  & ") or "     
next i
sWhere = left(sWhere ,len(swhere)-3)        'remove the last OR
  'save the sql
qdf.SQL = sSQL
qdf.close
  
  'run the sql
docmd.openquery qdf.name
Set qdf = Nothing
End Sub