Variable Criteria in Query

[redpanda]

I hope I am able to word this question in a way that asks what I am trying to figure out...



Using the import external data function in Excel I am trying to run a query and import the results to Excel. My problem is:

The criteria is a date range, however, it changes day to day. The new date range will be entered in excel so is there a way to have the query reference the date range in excel before running?

[June7]

You are running a VBA procedure in Excel? Where is the query - an sql statement executed in VBA code? Post the code for analysis.

[redpanda]

I am far from finished but here is what I have, right now I am trying to run it from Excel but if it is better/easier to run it from Access I will do that.

Sub Test()

Application.DisplayAlerts = False

Dim strSQL As String
Dim Exc As Excel.Application
Dim cn As New Access.Application

Sheets("Today").Select
a = Range("A6")
'table
b = Range("A7")
'start date
c = Range("A8")
'stop date

cn.OpenCurrentDatabase ("C:\Users\Scott\Documents\My Dropbox\Fundies DataBase\East Archives.accdb")
cn.Visible = True


strSQL = "SELECT *FROM [" & a & "],HAVING ((([" & a & "].Date)>#" & b & "# And ([" & a & "].Date)<#" & c & "#));"

DoCmd.RunSQL strSQL

cn.CloseCurrentDatabase

Application.DisplayAlerts = True

End Sub

[June7]

DoCmd.RunSQL works only with SQL action statement (Update, Insert, Delete) not with Select.

This is how I make a connection from Excel to Access data.

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source='C:\Users\Scott\Documents\My Dropbox\Fundies DataBase\East Archives.accdb
"rs.Open "SELECT * FROM [" & a & "] WHERE [Date]>#" & b & "# And [Date]<#" & c & "#;", cn, adOpenStatic, adLockReadOnly

If you want the date criteria to be inclusive use:

"SELECT * FROM [" & a & "] WHERE [Date] Betweeen #" & b & "# And #" & c & "#;"

This creates a virtual recordset over in the Excel but so far data is not actually imported to a worksheet. From here would have to read each record of the recordset and save each field to a cell of worksheet. Not what I think you are looking for.

[redpanda]

Thank you for your reply! I made the changes you suggested but I am getting the error below,

'-2147217805 (80040e73)' Format of the initialization string does not conform to the OLE DB specification.

After querying that database I want to import or copy the results to Excel. Am I on the right track or am I trying to go about this the wrong way? Again, thank you

[June7]

Rats! I am not familiar with that error message. I don't do a lot of programming behind Excel. I have only one workbook doing this and that is where I got the sample code from. It works great.

A complication for you is that you want an Access query automatically filtered by data in the Excel spreadsheet.

If you can get Excel to open the recordset, then feeding data from the recordset to cells of the spreadsheet might be possible with this technique http://support.microsoft.com/kb/246335 I have not used this.

An alternative might be to have Access link to the spreadsheet where the date values are stored, save a query object in Access filtering with those dates, then have Excel import the query.

[redpanda]

I will look into that link later today. What references do you have enabled in Excel? I am wondering if that is why I am getting that error?

[June7]

I have 9 references set, not sure all needed.
Visual Basic For Applications
Microsoft Excel 12.0 Object Library
Microsoft Forms 2.0 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.8 Library
Microsoft ActiveX Data Objects Recordset 2.8 Library
Microsoft Office 12.0 Access database engine Object Library
Microsoft Visual Basic for Applications Extensibility 5.3
Microsoft Office 12.0 Object Library

[redpanda]

I had most of the same ones enabled and the ones I didn't I enabled except, Microsoft Forms 2.0 Object Library & Microsoft ActiveX Data Objects Recordset 2.8 Library which were not available. The code is still getting the same error, however, I had found some other code online that wasn't working for me but I went ahead and tried it after making the reference changes and it works except it is now getting the error,

Run-time error '3131':

Syntax error in FROM clause.

it gets that error when it hits this line of code,

Set rs = db.OpenRecordset("SELECT *FROM [" & a & "],HAVING ((([" & a & "].Date)>#" & c & "# And ([" & a & "].Date)<#" & b & "#));", dbReadOnly)

Here is all the code:

Sub Test_3()

Dim db As Database
Dim rs As Recordset
Dim intColIndex As Integer
Dim myDB As String

Dim FieldName As String
Dim MyCriteria As String
Dim TableName As String

Sheets("Bal-Day Report").Select
a = Range("A6")
'table
b = Range("A7")
'start date
c = Range("A8")
'stop date

myDB = "C:\Users\Scott\Documents\My Dropbox\Fundies DataBase\East Archives.accdb"
Set TargetRange = Range("A1")
Set db = OpenDatabase(myDB)
Set rs = db.OpenRecordset("SELECT *FROM [" & a & "],HAVING ((([" & a & "].Date)>#" & c & "# And ([" & a & "].Date)<#" & b & "#));", dbReadOnly) ' filter records
'Set rs = db.OpenRecordset("SELECT * FROM [" & a & "] WHERE [Date] Betweeen #" & b & "# And #" & c & "#;", dbReadOnly) ' filter records

' write field names
For intColIndex = 0 To rs.Fields.Count - 1
TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name
Next

' write recordset
TargetRange.Offset(1, 0).CopyFromRecordset rs
Set rs = Nothing
db.Close
Set db = Nothing

End Sub

[June7]

The comma doesn't belong in the SQL in front of HAVING, put a space in its place.

I suggested this:
"SELECT * FROM [" & a & "] WHERE [Date]>#" & b & "# And [Date]<#" & c & "#;"
or
"SELECT * FROM [" & a & "] WHERE [Date] Between #" & b & "# And #" & c & "#;",

BTW, I had an extra e in Between (was Betweeen), which you appear to have copy/pasted.

[redpanda]

June7 thank you so much! You have been extremely helpful!

For anyone else reading this, below is the finished code. From excel it runs a query in Access, the query has user defined variables which are input into excel to determine which table and criteria to filter.

Here are the references I have enable in Excel 2007 (I'm not sure which one/s are necessary for the code to work):
Visual Basic For Applications
Microsoft Excel 12.0 Object Library
OLE Automation
Microsoft Office 12.0 Object Library
Microsoft Office 14.0 Access database engine Object Library
Microsoft Visual Basic for Applications Extensibility 5.3
Microsoft ActiveX Data Objects 6.0 Library

Sub Run_QRY()

Dim db As Database
Dim rs As Recordset
Dim intColIndex As Integer
Dim myDB As String

Dim FieldName As String
Dim MyCriteria As String
Dim TableName As String

Application.ScreenUpdating = False

Sheets("Bal-Day Report").Select
a = Range("A6")
'table
b = Range("A7")
'start date
c = Range("A8")
'stop date

myDB = "C:\Users\Scott\Documents\My Dropbox\Fundies DataBase\East Archives.accdb"
Set TargetRange = Sheets("Data").Range("A1")
' where the query results will be dropped

Set db = OpenDatabase(myDB)
Set rs = db.OpenRecordset("SELECT * FROM [" & a & "] WHERE [Date]>#" & b & "# And [Date]<#" & c & "#;", dbReadOnly)
' query database

For intColIndex = 0 To rs.Fields.Count - 1
TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name
Next
' copy column headers

TargetRange.Offset(1, 0).CopyFromRecordset rs
' copy query results

Set rs = Nothing
db.Close
Set db = Nothing

Application.ScreenUpdating = True

End Sub