Dynamic Access Parameters via Excel

[kogersdad]

Hello,
I am trying to create a code that will run an Access 2007 query from within an Excel 2007 spreadsheet and have the spreadsheet supply the dynamic parameters that the Access query needs. I have it working perfectly (see code below) just as long as the parameter is a specific number or a specific date, i.e. not a range (for example >123 or >=123 and <=456). I really need it to work with a range. In the code below, if I supply a fixed number such as 123 in the Excel Spreadsheet in cell L2, the code works fine. If I replace the contents in cell L2 with >123, it crashes with a "Run-time error '3464: Data type mismatch in criteria expression" error message. How can I get it to work with a range of data??? Please Help!!

Code begins here.....
Sub RunCNC_Restock()
'Step 1: Declare your variables
Dim MyDatabase As DAO.Database
Dim MyQueryDef As DAO.QueryDef
Dim MyRecordset As DAO.Recordset
Dim i As Integer
'Step 2: Identify the database and query
Set MyDatabase = DBEngine.OpenDatabase("C:\Documents and Settings\USER\My Documents\DATABASE.accdb")
Set MyQueryDef = MyDatabase.QueryDefs("query name")
'Step 3: Define the Parameters
With MyQueryDef
.Parameters("[enter invoice]") = Range("L2").Value
End With
'Step 4: Open the query
Set MyRecordset = MyQueryDef.OpenRecordset
'Step 5: Clear previous contents
Sheets("C & C restock").Select
ActiveSheet.Range("U20:Z27").ClearContents
'Step 6: Copy the recordset to Excel
ActiveSheet.Range("U21").CopyFromRecordset MyRecordset
'Step 7: Add column heading names to the spreadsheet
For i = 1 To MyRecordset.Fields.Count
ActiveSheet.Cells(20, i + 20).Value = MyRecordset.Fields(i - 1).Name
Next i
'MsgBox "Your Query has been Run"
End Sub

[tommyt61]

Sounds like your query is lacking a where statement . if your query is just looking for all records and you are entering >233 in Cell L2 it is not going to work. Maybe i am not understanding the issue .

look at sample query with where ststement is looking for quantities larger than 233

SELECT Quote.Company_Name, Quote.Material, Quote.Quantities
FROM `C:\RFQ.mdb`.Quote Quote
WHERE (Quote.Quantities>'233')

[tommyt61]

nevermind my reply and possible solution ... after i read your post again i see where i misunderstood the issue.

[tommyt61]

This might be an option. configure a query with where >[enter invoice start] and <[enter invoice end]
in the code below there is a new parameter for cell M2

Sub RunCNC_Restock()
'Step 1: Declare your variables
Dim MyDatabase As DAO.Database
Dim MyQueryDef As DAO.QueryDef
Dim MyRecordset As DAO.Recordset
Dim i As Integer
'Step 2: Identify the database and query
Set MyDatabase = DBEngine.OpenDatabase("C:\Documents and Settings\USER\My Documents\DATABASE.accdb")
Set MyQueryDef = MyDatabase.QueryDefs("query name")
'Step 3: Define the Parameters
With MyQueryDef
.Parameters("[enter invoice start]") = Range("L2").Value
.Parameters("[enter invoice end]") = Range("M2").Value
End With
'Step 4: Open the query
Set MyRecordset = MyQueryDef.OpenRecordset
'Step 5: Clear previous contents
Sheets("C & C restock").Select
ActiveSheet.Range("U20:Z27").ClearContents
'Step 6: Copy the recordset to Excel
ActiveSheet.Range("U21").CopyFromRecordset MyRecordset
'Step 7: Add column heading names to the spreadsheet
For i = 1 To MyRecordset.Fields.Count
ActiveSheet.Cells(20, i + 20).Value = MyRecordset.Fields(i - 1).Name
Next i
'MsgBox "Your Query has been Run"
End Sub