Query matching 3 sequential numbers

[JAYLO]

I have 2 tables of invoice information where the invoice number is anywhere from 5 to 11 numbers. I am trying to match invoices where any 3 consecutive numbers are the same.
Ex: Invoice # 41598 and Invoice # 98915979 would match since 159 is consecutive in both.
The position of the match within the number doesn't matter.
Thanks!

 

[james_lankford]

is invoice a text field ?

if so then

select *
from your_table
where invoice like '*159*'

 

[JAYLO]

is invoice a text field ?

if so then

select *
from your_table
where invoice like '*159*'

Thanks, that would work except the 3 consecutive numbers are not always 159. They vary considerably.

 

[dogdays]

Using VBA code this should work

Option Compare Database
Option Explicit

Private Sub cmdSearch_Click()
    Dim strReturn As String
    
    strReturn = fnSearchInvoices("41598")
    strReturn = fnSearchInvoices("8888888")
End Sub

Private Function fnSearchInvoices(strInvoiceNumber As String) As String
    Dim iIndex As Integer
    Dim invRecSet As DAO.Recordset
    Dim strQuery As String
        
    ' default return
    fnSearchInvoices = ""
    If Len(strInvoiceNumber) < 3 Then
        Exit Function
    End If
    
    For iIndex = 1 To Len(strInvoiceNumber) - 2
        strQuery = "SELECT * FROM tblYourTableName "
        strQuery = strQuery & "WHERE invoiceNumber LIKE '*"
        strQuery = strQuery & Mid(strInvoiceNumber, iIndex, 3) & "*'"
        Set invRecSet = DBEngine(0)(0).OpenRecordset(strQuery)
        If invRecSet.RecordCount <> 0 Then
            fnSearchInvoices = invRecSet.Fields("invoiceNumber")
            Exit Function
        End If
    Next iIndex
End Function

you would want to add code to exclude selecting the row that your strInvoiceNumber came from.
Am empty return string indicates no match.

Jack

 

[dogdays]

Out of curiosity how did you solve your problem?

Jack