Sorry about that. I scratched the idea because the logic doesn't work. I'm trying to get the result via a recordset. I have three different ideas. I'm going to edit my post to show all three. None are fully functional. One is close.
Here they are
Idea 1:
This one works for two, but produces an error on the third even though there is a match for it. "No Current Record" is the error. I get this before the second messagebox (the one with all three numbers). The error occurs on:
MiddleNumberstoMatch = RS5!Numbers_to_match.
Code:
Private Sub Command0_Click()
Dim Number5 As String
Dim Number7 As String
Dim MiddleNumberstoMatch As Integer
Dim MatchCheck As Integer
Dim NumberofMatches As Integer
Dim SQL5 As String
Dim SQL7 As String
Dim RS5 As Recordset
Dim RS7 As Recordset
SQL5 = "SELECT GNumbers.Numbers, Val(Mid(Numbers,2)) AS JustNumbers, Val(LEFT(JustNumbers, len(JustNumbers)-1)) AS Numbers_to_Match FROM GNumbers WHERE Right([Numbers],1)='5';"
SQL7 = "SELECT GNumbers.Numbers, Val(Mid(Numbers,2)) AS JustNumbers, Val(LEFT(JustNumbers, len(JustNumbers)-1)) AS Numbers_to_Match FROM GNumbers WHERE Right([Numbers],1)='7';"
SQL9 = "SELECT GNumbers.Numbers, Val(Mid(Numbers,2)) AS JustNumbers, Val(LEFT(JustNumbers, len(JustNumbers)-1)) AS Numbers_to_Match FROM GNumbers WHERE Right([Numbers],1)='9';"
Set RS5 = CurrentDb.OpenRecordset(SQL5)
Set RS7 = CurrentDb.OpenRecordset(SQL7)
Set RS9 = CurrentDb.OpenRecordset(SQL9)
If Not RS5.EOF Then
RS5.MoveFirst
ResetLabel:
MiddleNumberstoMatch = RS5!Numbers_to_match
NumberofMatches = 1
Number5 = RS5!Numbers
If Not RS7.EOF Then
RS7.MoveFirst
MatchCheck = RS7!Numbers_to_match
If MiddleNumberstoMatch = MatchCheck Then
NumberofMatches = 2
MatchCheck = 0
Number7 = RS7!Numbers
MsgBox "Your Numbers are: " + Number5 + " and " + Number7
If Not RS9.EOF Then
RS9.MoveFirst
MatchCheck = RS9!Numbers_to_match
If MiddleNumberstoMatch = MatchCheck Then
NumberofMatches = 3
MatchCheck = 0
Number9 = RS9!Numbers
MsgBox "Your Numbers are: " + Number5 + " and " + Number7 + " and " + Number9
Else
NumberofMatches = 0
RS5.MoveNext
GoTo ResetLabel
End If
Else
RS5.MoveNext
GoTo ResetLabel
End If
Else
NumberofMatches = 0
RS5.MoveNext
GoTo ResetLabel
End If
Else
NumberofMatches = 0
RS5.MoveNext
GoTo ResetLabel
End If
Else
MsgBox "There are no matching records in the recordset that meet the criteria."
End If
End Sub
Idea 2:
This does nothing. Not sure why. I was going to get it to return the number of matches, then iterate through them and grab the numbers. The starting code doesn't even work.
Code:
Private Sub Command0_Click()
Dim SQL5 As String
Dim RS5 As Recordset
Dim SQLMatch As String
Dim RSMatch As Recordset
SQL5 = "SELECT GNumbers.Numbers, Val(Mid(Numbers,2)) AS JustNumbers, Val(LEFT(JustNumbers, len(JustNumbers)-1)) AS Numbers_to_Match FROM GNumbers WHERE Right([Numbers],1)='5';"
Set RS5 = CurrentDb.OpenRecordset(SQL5)
If Not RS5.EOF Then
RS5.MoveFirst
NextRecord:
RS5.MoveNext
MiddleNumberstoMatch = RS5!Numbers_to_match
SQLMatch = "SELECT GNumbers.Numbers, Val(Mid(Numbers,2)) AS JustNumbers, Val(LEFT(JustNumbers, len(JustNumbers)-1)) AS Numbers_to_Match FROM GNumbers WHERE ((Right([Numbers],1)='5') OR (Right([Numbers],1)='7') OR (Right([Numbers],1)='9') AND Numbers_to_Match=" + MiddleNumberstoMatch + ");"
Set RSMatch = CurrentDb.OpenRecordset(SQLMatch)
RSMatch.MoveLast
RecordCount = RSMatch.RecordCount
If RecordCount = 3 Then
MsgBox "Found 3 Matches!"
Else
MsgBox "Nope"
GoTo NextRecord
End If
Else
MsgBox "There are no numbers available that match the criteria."
End If
End Sub