Querying data from Oracle based on a Date(s) in Cell H1 (or H1 & H2).

[Brott]

This is a long read, thanks for you time in advance.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Before I begin here is some background. I currently have an Excel file with the ODBC connection and using my own SQL. Doing it this way would require the super user to go into each sheet then to the SQL each time to change the dates. I know there is a way to allow them to simply change the dates in cells H1 (or H1 and H2) then refresh data and whoolaa it would be updated.<o></o>
<o></o>
My goal if possible via this board and all the knowledge available would be to have a spreadsheet allowing a super user to change the date in Cells H1 and H1 & H2 on sheet Tst1 and have the data returned to sheets (Tst2, Tst3) based on my own SQL statements. <o></o>
<o></o>
Here are some parameters that I think you might need to know. <o></o>
(And as you may guess I do not have much VBA experience so if at all possible use my naming convention shown below. and feel free to write for a first grader.)<o></o>
<o></o>
User Name (not actual): "UID"<o></o>
Server (not actual): "DEV"<o></o>
Password (not actual): "PWD"<o></o>
File Name: Excel_ODBC.xls<o></o>
Sheet Name: "Tst2"<o></o>
"Tst3"<o></o>
Output Cell: "A1" for both sheets.<o></o>
Oracle table name: UAB <o></o>
Current Sql scripts (one for each sheet): <o></o>
Sql for sheet "Tst2"<o></o>
SELECT A.UAB_USER_ID_AUTHOR "Clerk", <o></o>
SUM(A.UAB_TOTAL_AMT)"Total Amount",<o></o>
COUNT(*)"Total Number of PAs"<o></o>
FROMUAB A<o></o>
WHERE TRUNC (A.UAB_DATE_CREATED)= TO_DATE ('02/26/2008','MM/DD/YYYY') <o></o>
GROUPBY A.UAB_USER_ID_AUTHOR;<o></o>
Sql for sheet "Tst3"<o></o>
SELECT A.UAB_USER_ID_AUTHOR "Clerk", <o></o>
SUM(A.UAB_TOTAL_AMT)"Total Amount",<o></o>
COUNT(*)"Total Number of PAs"<o></o>
FROMUAB A<o></o>
WHERE TRUNC (A.UAB_DATE_CREATED)>= TO_DATE ('01/01/2008','MM/DD/YYYY')AND<o></o>
TRUNC (A.UAB_DATE_CREATED)<= TO_DATE ('01/31/2008','MM/DD/YYYY') <o></o>
GROUPBY A.UAB_USER_ID_AUTHOR;<o></o>
<o></o>
At this point that is all I can think of. If it would be best served having this in two separate files for each SQL statement that would not be that bad but I would need to know how to handle a single date then the two date ranges. I do appreciate your assistance in helping me with this as I am sure this is very common task. Whewww the end.

 

[Brott]

I have done some research on the board and found many examples but none that utilize a cell to be apart of the query (sql). This must be a somewhat difficult one.

Again thanks!

 

[kyrgan]

Brott, I use the following code to query a DB with the dates being passed as parameters. since the dates are in named cells "StartPayPeriod = Sheets("Menu").Cells(2, 13) ", I assume you could call the dates from any Excel file. My pain was formating the SQL to pass the dates correctly.

I hope this is helpful.

Sub QueryHourlyDataBase()
'
' This maco queries the Delphi database using the POSVendor provided DelphiODBCDRIVER.DLL file.
' The macro runs a SQL type query and returns the raw data to the Import Sheet.
' We use the cell format on the menu sheet to define the date format for the SQL query.

Dim StartDate As Variant, EndDate As Variant, StartTime As Variant, EndTime As Variant
Dim StartPayPeriod As Variant 
Dim EndPayPeriod As Variant, DateValue
Dim ID1 As Variant, ID2 As Variant, ID3 As Variant

StartPayPeriod = Sheets("Menu").Cells(2, 13)  'StartDate & " " & StartTime ' "2006-07-16 03:00:00" '
EndPayPeriod = Sheets("Menu").Cells(4, 13) + 1 'EndDate & " " & EndTime '"2006-08-01 03:00:00" '

    With Sheets("Import").QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DSN=Delphi;" _
        ), Array( _
        "Transport=TCP;" _
        ), Array( _
        "Server=delphidb@192.168.3.1;" _
        ), Array( _
        "Port=16000;Timeout=10000;Database=Delphi;UserName=Delphiadmin;" _
        ), Array( _
        "PassWord=Password;" _
        ), Array( _
        "BlockReadSize=64000;" _
        ), Array( _
        "Compression=0;" _
        ), Array( _
        "Driver=C:\WINDOWS\system32\DelphiODBCDRIVER.DLL" _
        )), Destination:=Sheets("Import").Range("A1"))
        .CommandText = Array( _
        "SELECT Employee.StoreID, Employee.EmployeeID, Employee.InternalID, Employee.LastName, Employee.FirstName, Employee.SocialSecurity, EmpShifts.Department ,Sum(EmpShifts.RegHours) , Sum(EmpShifts.OTHours1) , Sum(EmpShifts.Tips) " _
        , _
        "FROM Employee, EmpShifts " _
        , _
        "WHERE (Employee.EmployeeID = EmpShifts.EmployeeID) " _
        , _
        "AND ( EmpShifts.""Date"" >= TIMESTAMP '" & StartPayPeriod & "' ) " _
        , _
        "AND ( EmpShifts.""Date"" < TIMESTAMP '" & EndPayPeriod & "' ) " _
        , _
        "GROUP BY Employee.StoreID, Employee.EmployeeID, Employee.InternalID, Employee.LastName, Employee.FirstName, Employee.SocialSecurity, Empshifts.Department " _
        , _
        "ORDER BY Employee.StoreID, Employee.LastName, Employee.FirstName")
                
        .Name = "Hourly Query To Delphi Database"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = False
        .Refresh BackgroundQuery:=False
    End With
    
End Sub

 

[Colby]

I frequently have a need to pass a date from Excel to a SQL query of an Oracle DB. Here is what I do:

The SQL:
Between to_Date(?,'mm/dd/yyyy') And to_Date(?,'mm/dd/yyyy'))

Query Parameters:
i.) Start Due Date
ii.) End Due Date

MS Query by default writes names the parameters as Parameter1 and Parameter2, which then get converted to ? as shown above. To set this in MS Query:

1.) Add your field that you want to set the criteria to.
2.) Enter [] where you would normally enter Like '01/01/2008'. This creates a parameter either entered by a user or pulled from Excel.
3.) When you hit the exit door to return data to Excel, it will prompt you for the parameters cells and if you want to always get the data from here on a refresh.
4.) The parameters cells must be in this format =TEXT(ActualDateCellHere, 'mm/dd/yyyy') or it will not work.

Altertnatively you can right click the sheet where the results are populated and select "Parameters".

Hope that helps. Let me know if you have any questions.

 

[Brott]

I am sure if I had a good back ground on this your input would probable make a lot of since. However when tring to mesh my requirements and this I get a little lost.
I do appreciate your time. I will keep searching this topic.

 

[Brott]

After reviewing my last recommendation and other posts I am still not putting things together. Are there any other Excel Gurus that have another suggestion that would be help me get past my knowledge block.
Thanks I do appreciate what has been posted thus far.

 

[kyrgan]

After reviewing my last recommendation and other posts I am still not putting things together. Are there any other Excel Gurus that have another suggestion that would be help me get past my knowledge block.
Thanks I do appreciate what has been posted thus far.

Brott, can you tell us what exactly is not working?
Can you provide code examples please?
K.

 

[Brott]

I would be happy to provide what is not working if I could get started.
To put it as simple as possible. I tried to use what was suggested or used for the (delphiaODBC) however when I copied and pasted that into a module and started to change the script to suit my connections. Again not being that familiar at all with scripting an ODBC connections then throw in the need to use a CELL to change a date allowing my SQL to run off that date.
I hope this gives you a better picture of where I am sitting with this. Thanks

 

[kyrgan]

I would be happy to provide what is not working if I could get started.
To put it as simple as possible. I tried to use what was suggested or used for the (delphiaODBC) however when I copied and pasted that into a module and started to change the script to suit my connections. Again not being that familiar at all with scripting an ODBC connections then throw in the need to use a CELL to change a date allowing my SQL to run off that date.
I hope this gives you a better picture of where I am sitting with this. Thanks

Brott, It was my understanding that you were trying to query an Oracle DB. The example I provided was how I use an ODBC connection to connect to a Delphi DB using a provided DLL. I think you are going to have to break this project into little pieces and work from there. First, get a working ODBC connection to your Oracle DB. You can start here:

http://www.orafaq.com/faqodbc.htm

Thanks
K.

 

[Brott]

Thanks K, I will get look at that. I appreciate providing me with the link/tools to build a knowledge base to work from.