I have a worksheet that I need to update on a daily basis
with data from an external Oracle database. I am using
the following query which gets yesterday's data (the Oracle
data is always for the previous day).

SELECT Sum(recno) FROM mailboxes
WHERE (trunc(logdate)=trunc(sysdate-1))

This sums the values in the rec_no column for all rows
that were logged yesterday. The thing is that on a Monday,
I need to get the data for both Saturday and Sunday so
would need to be able to pass the date as a parameter
rather than having to manually change the query.

How does one do this?

Many thanks,
Seamus

If I were passing to SQL Server then I would pass it in as a string
something like;

SELECT SUM(fld_hours) AS SumHours
FROM tbl_techtime
WHERE (fld_time_started = CONVERT(DATETIME, ' & mydate & ', 102))

SELECT SUM(fld_hours) AS SumHours
FROM tbl_techtime
WHERE (fld_time_started = CONVERT(DATETIME, ' & mystartdate & ', 102)
AND fld_time_started <= CONVERT(DATETIME, ' & myenddate & ', 102))

SELECT SUM(fld_hours) AS SumHours
FROM tbl_techtime
WHERE (fld_time_started BETWEEN CONVERT(DATETIME, ' & mystartdate & ',
102) AND CONVERT(DATETIME, ' & myenddate & ', 102))

No idea really with oracle.

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

"Seamus Conlon" wrote:
|I have a worksheet that I need to update on a daily basis
| with data from an external Oracle database. I am using
| the following query which gets yesterday's data (the Oracle
| data is always for the previous day).
|
| SELECT Sum(recno) FROM mailboxes
| WHERE (trunc(logdate)=trunc(sysdate-1))
|
| This sums the values in the rec_no column for all rows
| that were logged yesterday. The thing is that on a Monday,
| I need to get the data for both Saturday and Sunday so
| would need to be able to pass the date as a parameter
| rather than having to manually change the query.
|
| How does one do this?
|
| Many thanks,
| Seamus
|
|

How are you executing the query (SQL statement queried to a recordset in
Excel VBA, other?)?



"Seamus Conlon" wrote:

I have a worksheet that I need to update on a daily basis
with data from an external Oracle database. I am using
the following query which gets yesterday's data (the Oracle
data is always for the previous day).

SELECT Sum(recno) FROM mailboxes
WHERE (trunc(logdate)=trunc(sysdate-1))

This sums the values in the rec_no column for all rows
that were logged yesterday. The thing is that on a Monday,
I need to get the data for both Saturday and Sunday so
would need to be able to pass the date as a parameter
rather than having to manually change the query.

How does one do this?

Many thanks,
Seamus

"Bill Pfister" wrote in message
...
How are you executing the query (SQL statement queried to a recordset in
Excel VBA, other?)?

To insert the query in the worksheet I initially used the
New Database Query option under Import External Data.
To refresh it I have a macro that uses something like:

Selection.QueryTable.Refresh

Thanks,
Seamus

Using VBA, you can craft your SQL statement to contain whatever parameters
you need. In the following example, I'm pointing to a local Access database,
you would only have to modify the details (driver, etc.) in the connect
string to point to Oracle or another DBMS.


Sub AccessQueryData1()
Dim strFilename As String
Dim strSQL As String
Dim strConn As String
Dim strSeparator As String

strSeparator = Chr(13) & "" & Chr(10)

strFilename = "D:\Data\Test.mdb"
strConn = "ODBC;DSN=0_ptd_test1;DBQ=" & strFilename &
";DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"


strSQL = "SELECT Category.Name" & strSeparator & _
"FROM `" & strFilename & "`.Category Category " & strSeparator
& _
"WHERE Category.Name < 'Administrative' "


' Focus on only the parameter
With ActiveSheet.QueryTables.Add(Connection:=strConn,
Destination:=Range("C6"))
.CommandText = Array(strSQL)
End With

' All the other parameters
With ActiveSheet.QueryTables.Add(Connection:=strConn,
Destination:=Range("C6"))
.CommandText = Array(strSQL)
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
Range("E11").Select
End Sub


"Seamus Conlon" wrote:

"Bill Pfister" wrote in message
...
How are you executing the query (SQL statement queried to a recordset in
Excel VBA, other?)?

To insert the query in the worksheet I initially used the
New Database Query option under Import External Data.
To refresh it I have a macro that uses something like:

Selection.QueryTable.Refresh

Thanks,
Seamus

Bill, I modified your code slightly to suit and when I run
it I get an error "Invalid procedure call or argument" at
the first 'With ActiveSheet.QueryTables.Add' line.

I got the connection string from the existing query
so I expect it is correct.

Any ideas on what is wrong?

Seamus

"Bill Pfister" wrote in message
...
Using VBA, you can craft your SQL statement to contain whatever parameters
you need. In the following example, I'm pointing to a local Access
database,
you would only have to modify the details (driver, etc.) in the connect
string to point to Oracle or another DBMS.


Sub AccessQueryData1()
Dim strFilename As String
Dim strSQL As String
Dim strConn As String
Dim strSeparator As String

strSeparator = Chr(13) & "" & Chr(10)

strFilename = "D:\Data\Test.mdb"
strConn = "ODBC;DSN=0_ptd_test1;DBQ=" & strFilename &
";DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"


strSQL = "SELECT Category.Name" & strSeparator & _
"FROM `" & strFilename & "`.Category Category " & strSeparator
& _
"WHERE Category.Name < 'Administrative' "


' Focus on only the parameter
With ActiveSheet.QueryTables.Add(Connection:=strConn,
Destination:=Range("C6"))
.CommandText = Array(strSQL)
End With

' All the other parameters
With ActiveSheet.QueryTables.Add(Connection:=strConn,
Destination:=Range("C6"))
.CommandText = Array(strSQL)
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
Range("E11").Select
End Sub

Seamus, would you mind dumping the connection string to the debug window and
posting it? Try using the macro recording to nail down the exact syntax. Do
you have an ODBC connection for your database? In my test case, I actually
created an ODBC connection (in the Control Panel / Administrative Tools /
Data Sources tool) before running recording the action. That may have an
effect.

Bill


"Seamus Conlon" wrote:

Bill, I modified your code slightly to suit and when I run
it I get an error "Invalid procedure call or argument" at
the first 'With ActiveSheet.QueryTables.Add' line.

I got the connection string from the existing query
so I expect it is correct.

Any ideas on what is wrong?

Seamus

"Bill Pfister" wrote in message
...
Using VBA, you can craft your SQL statement to contain whatever parameters
you need. In the following example, I'm pointing to a local Access
database,
you would only have to modify the details (driver, etc.) in the connect
string to point to Oracle or another DBMS.


Sub AccessQueryData1()
Dim strFilename As String
Dim strSQL As String
Dim strConn As String
Dim strSeparator As String

strSeparator = Chr(13) & "" & Chr(10)

strFilename = "D:\Data\Test.mdb"
strConn = "ODBC;DSN=0_ptd_test1;DBQ=" & strFilename &
";DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"


strSQL = "SELECT Category.Name" & strSeparator & _
"FROM `" & strFilename & "`.Category Category " & strSeparator
& _
"WHERE Category.Name < 'Administrative' "


' Focus on only the parameter
With ActiveSheet.QueryTables.Add(Connection:=strConn,
Destination:=Range("C6"))
.CommandText = Array(strSQL)
End With

' All the other parameters
With ActiveSheet.QueryTables.Add(Connection:=strConn,
Destination:=Range("C6"))
.CommandText = Array(strSQL)
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
Range("E11").Select
End Sub