Run a Query on External data(ODBC) criteria based on input of a ce

T

TONYR

I have an access database that I would like to put a DATE into a cell and
have it auto run a query with that date criteria. I have done this with the
query wizard and manualy but would like to automate the process by just
typing the date and pressing the enter any one who could lead me in the
right direction or who has done this before I would appreciate the help
 
A

Arvi Laanemets

Hi

As an example here is a part of the procedure, I used for such a task (with
ODBC for VisualFox tables, but only query syntax is affected by driver used)

Public Sub RefreshQueryes()
month= ActiveSheet.Range("D1").Value
year= ActiveSheet.Range("D2").Value
'
Set qtQtrResults = Worksheets("SheetName1").QueryTables(1)
Sheets("SheetName1").Activate
' a cell in existing query result table is selected
ActiveSheet.Range("C2").Select
With qtQtrResults
.CommandType = xlCmdSql
.CommandText = _
"SELECT a.field1, a.field2, b.field3, b.date, a.field5, a.field6
FROM table1 a, table2 b WHERE b.field7=a.field2 AND a.field1<>'' AND
a.field5<'xxx' AND Year(b.date)=" & year & " AND Month(b.date)=" & month & "
ORDER BY a.field1, a.field2"
.Refresh
End With
'
.....
End Sub
 
Top