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

 

[=ODIN=]

I am failry certain that your issue is that when you have just a number in the cell, access is able to understand that it is a number an fix your criteria automatically. When you have >123 in the cell. You are passing access a string so, it is like you typing ">123" (including the quotes) into the criteria box of you query within access -- which will give oyu a type mismatch error. I would explor opening your recordset using sql. you can figure out the sql necessary by using the query desinger in access and then clicking the view button to see it's sql equivalent.

 

[xenou]

The less than, greater than is resolved at the query level. The parameter can only be a value (date, number, or text). If you need it to be flexible enough to handle different kinds of comparison use a greater than and less then in your query:

WHERE [Field] >= [Param1] AND [Field] <= [Param2]

You can use this as an equals by using the same param value in both parameters:

WHERE [Field] >= 42 AND [Field] <= 42

Or you can use a range of values instead:

WHERE [Field] >= 0 AND [Field] <= 100

And for one-sided tests (just a greater than, for instance) use a boundary set arbitrarily high:

WHERE [Field] >= 0 AND [Field] <= 999999

Ultimately you can also create SQL queries on the fly if you need even more flexibility.

ξ

 

[Kogersdad]

Ok, you were exactly right! When I changed my paramter at the query level like you suggested and then entered both paramters in the excel spreadsheet, it worked PERFECTLY!!
I cant tell you enough how much I appreciate you and this forum!
Tim