Accessnewbie,
I don't know what your xls file looks like, but I mocked up a sheet that contains a DrawDate with 11 choice fields. I am not an Excel person.
You can adjust the code to allow for as many fields as you need.
I have a routine that imports the your choices data from Excel to an Access table.
Also I'm using the tblPick3Info info to show winning numbers by Drawdate .
The basic logic of the routine is:
-read the list of your choices
-for each Date, assemble your choices to create a list
-the list completes query SQL to compare your choice info with winning number info
-any matches result in a print out to immediate window showing the Draw ID, Drawdate and winning number.
Here is the mock up data and results:
PickId |
PickDate |
WinningNum |
1 |
14-Dec-16 |
341 |
2 |
20-Dec-17 |
298 |
3 |
21-Dec-16 |
075 |
4 |
29-Dec-16 |
111 |
5 |
09-Jan-17 |
273 |
6 |
12-Jan-17 |
102 |
7 |
18-Jan-17 |
049 |
The routine to compare the choices and winning numbers by Date
Code:
'---------------------------------------------------------------------------------------
' Procedure : PickSetUp
' Author : mellon
' Date : 10-Feb-2017
' Purpose :
'---------------------------------------------------------------------------------------
'
Sub PickSetUp()
' uses this ImportXLSheetsAsTables to get pick3data from excel
'''need to include a reference to Excel !!!!!!!!!!!!
'This is a demo to bring my custom selections from Excel
'and to use those selections to see if I have any matching (winning numbers)
'that are recorded in tblPick3Info
'the table with excel data is tbl_MyPick3Selections
'it has DrawDate, followed by a variable number of selections
'the selctions/choices fields are named F2....F11 (in this demo)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rres As DAO.Recordset
Dim i As Integer
Dim InList As String
Dim qrySQL As String
10 On Error GoTo PickSetUp_Error
20 Debug.Print "Started"
30 Set db = CurrentDb
40 Set rs = db.OpenRecordset("tbl_MyPick3Selections", dbOpenSnapshot)
50 Do While Not rs.EOF
60 For i = 2 To rs.Fields.Count - 1
70 If Not IsNull(rs.Fields(i)) Then
80 InList = InList & "'" & rs.Fields(i) & "',"
90 Else
100 End If
110 Next i
120 InList = Mid(InList, 1, Len(InList) - 1)
130 InList = "(" & InList & ")"
140 qrySQL = "SELECT * From tblPick3Info " _
& " WHERE PickDate = " & rs!Drawdate & " and WinningNum In " & InList
'Debug.Print qrySQL
150 Set rres = db.OpenRecordset(qrySQL, dbOpenSnapshot)
160 Do While Not rres.EOF
170 Debug.Print rres!PickID; rres!PickDate; rres!WinningNum
180 rres.MoveNext
190 Loop
200
210 InList = ""
220 rs.MoveNext
230 Loop
240 Debug.Print "Finished "
250 On Error GoTo 0
260 Exit Sub
PickSetUp_Error:
270 MsgBox "Error " & err.number & " in line " & Erl & " (" & err.Description & ") in procedure PickSetUp of Module ImportExcelSheets"
End Sub
The Result in the immediate window:
Code:
Started
4 29-Dec-2016 111
6 12-Jan-2017 102
Finished
This is the code I used to bring the xls data into Access. I simply adjusted some old existing code. As I said I'm not an Excel person
Code:
'---------------------------------------------------------------------------------------
' Procedure : ImportXLSheetsAsTables
' Author : jed
' Date : 1/16/2009
' Purpose : To import all worksheets in a specified workbook into
' individual tables in Access.
' Tables get names: Tbl_ + name of the worksheet
'
' Modification Jan 16 2009 --
' --- in Transferspreadsheet note the exclamation mark !!!
'
' NOTE: Must have a reference to the Microsoft Excel Object Library
'---------------------------------------------------------------------------------------
'
Sub ImportXLSheetsAsTables()
Dim appexcel As Excel.Application
Dim wb As Excel.Workbook
Dim sh As Excel.Worksheet
Dim strValue As String
10 On Error GoTo ImportXLSheetsAsTables_Error
20 Set appexcel = CreateObject("Excel.Application")
30 Set wb = appexcel.Workbooks.Open("C:\users\mellon\documents\MyPick3Choices.xlsx") '"C:\test\jillian_2006-03-16.xls")
40 For Each sh In wb.Sheets
50 Debug.Print sh.name
' DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tbl_" & sh.name, "C:\test\Jillian_2006-03-16.xls", True, sh.name & "!"
60 DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tbl_MYPick3Selections", "C:\users\mellon\documents\MyPick3Choices.xlsx"
70 Next
80 wb.Close
90 appexcel.Quit
100 On Error GoTo 0
110 Exit Sub
ImportXLSheetsAsTables_Error:
120 MsgBox "Error " & err.number & " in line " & Erl & " (" & err.Description & ") in procedure ImportXLSheetsAsTables of Module ImportExcelSheets"
End Sub
This was my choices data
Code:
Drawdate |
F2 |
F3 |
F4 |
F5 |
F6 |
F7 |
F8 |
F9 |
F10 |
F11 |
42732 |
012 |
023 |
036 |
123 |
127 |
126 |
345 |
456 |
678 |
789 |
42733 |
234 |
123 |
111 |
876 |
309 |
|
|
|
|
|
42747 |
002 |
003 |
004 |
100 |
104 |
102 |
222 |
333 |
456 |
111 |