Credit card number search within a text filed

[mdempsey]

hi, looking for some help. I need to search an entire db for 16 digit credit card numbers, and identify which record it's found in.

The below successfully find a 16 digit number, but only when found on it's own.

is there a way to change this code so that It can search through an entire DB or at least an entire text field and identify the 16 digit string?

Dim SQL As String, qdf As DAO.QueryDef
 
 
Set qdf = CurrentDb.QueryDefs("cardsearch")
 
qdf.SQL = "SELECT Id, case_overview " & _
    "From case_data " & _
"WHERE  LEN(case_overview) = 16 AND case_overview NOT Like '%[0-9]%'"
 
Me.List194.RowSource = "cardsearch"
 
DoCmd.Requery "list194"
 

thanks
Martin

[zmbd]

What I suggest you do is on your open database, create a query that returns your expected results. Once you have that query working by hand, then you can switch to SQL view from the GUI and cut and paste the SQL into the VBE.

From there you can clean up the SQL as needed (sometimes MSA tosses in extras) and modify to allow for variables.

Z

[Rabbit]

Are you looking to extract any 16 digit number from any and all fields? For that you need to loop through every table and every field and use something like a regular expression to extract the matches and put them in another table.

[zmbd]

Rabbit
Normally I'd agree... but it seems to me that OP is attempting to populate a listbox and couldn't that could be done by setting the SQL properly?