Querying Data From Oracle Based On A Date(s) In Cell H1 (or H1 & H2).
Mar 19, 2008
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.
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.
Here are some parameters that I think you might need to know.
(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.)
User Name (not actual): "UID"
Server (not actual): "DEV"
Password (not actual): "PWD"
File Name: Excel_ODBC.xls
Sheet Name: "Tst2"
"Tst3"
Output Cell: "A1" for both sheets.
Oracle table name: UAB .................
I am trying to set up a query to search a date range within a table in one sheet based upon a more narrow date range in a second sheet AND return a simple count, median currency value or value based upon a ratio (see attachment).
Note: the Sale Price, List Price and Supply values will be based upon the filtered date range (each quarter) and return a value based upon that range. The Sale Price and List Price also need to be a median within each quarter value so I can chart the data.
Help.pdf
Here is what I need to return:
Num Sales - simple count from each quarter
Num Listings - simple count from each quarter
Sales Price - median value within each queried date range
List Price - median value within each queried date range
Supply - numeric value based upon absorption rate, supply = listings each quarter / absorption rate (absorption rate = sales each quarter / months each quarter)
I'm generating a report on an oracle based software which then I export to Excel. Unfortunately the dates come out very messy. I've included an .xls file with the dates. They are untouched. As you can see, some are left aligned some right aligned. Nevertheless, all dates are of the format dd/mm/yyyy with zeros (0) automatically omitted - this is how they're shown on the Oracle report, I've changed nothing.
I want all the dates on column B to have the same format as the date on cell D3. Of course, the format must remain dd/mm/yyyy.
Please feel free to ask me questions if any of this is confusing.
I am attempting to use MSQuery to extract records from an Oracle transaction table by passing user defined Date/Time field parameters.
So if the date range for the query is for Fiscal Period 10 of this year, my parameters would be: [StartDate] = #2002/10/1# [EndDate] = #2002/11/1#
Daily transaction activity is in the early morning hours ending at 6:00AM so my Criteria is: Between [StartDate] + .25 and [EndDate] + .25 Note: The +.25 represents 1/4day = 6hrs (6:00AM)
If I use the hardcoded Start/End Dates, my query works fine, but if I try and pass the dates as parameters I get "ORA-00932 inconsistent datatypes" error. And I can't seem to pass the Date/Time combination successfully....
If anyone has encountered a solution for how to pass a Date/Time Paremeter into MSQuery I would love to hear how you did it. I'm starting to pull my few remaining hairs out!
[ This Message was edited by: Tuner on 2002-11-07 12:23 ]
[ This Message was edited by: Tuner on 2002-11-07 12:28 ]
[ This Message was edited by: Tuner on 2002-11-12 12:51 ]
I have a list of stores (A1:AX) and corresponding customer affinity numbers (B1:BX). Each time a customer visits a store with her affinity car, a record is generated. So the data is quite simple - two columns.
The tricky part (as always) is the output. I would like to divide the customers into three groups:those who visit only 1-3 stores those who visit 4-7 stores those who visit more than 8 stores
I would like to create a way to view a list of stores in column A and see how many of its customers are in each of the three groups listed above. Does that make sense? Output might be:
I Have a workbook which consists of 50 worksheets and i use Edit query window to pull the data from oracle on all these sheets. We run this once in a month. I have to go to every sheet and execute this code which is time consuming and i have to change the date value everytime. macro that can execute this process at once and we should give the date only once.I use OLEDB driver to connect to oracle.
Attached is the Command line code.
Select protocol, patient, Page, (date_indexed) Indexed,(first_entry_date) FirstPass,( second_entry_date) SecondPass,trim( modified_date) modified, trim(CRF_ERROR_COMMENT) Comments from JJA38377_96_crf where date_indexed>='1-May-11' and date_indexed
I'm having some issues importing external data from an Oracle 9i database. Here is what is happening:
I am initially able to import data from the table I want to into Excel. I do this through "Data -> Import External Data -> New Database Query" where I have my .ORA data source. I'm able to log in using my user and password and import the table. My problems come after I import this first set of data.
If I try to "Edit Query…" I get an error box that says "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
If I try to "Refresh Data" I get two error boxes. The first is just: "[Microsoft][ODBC driver for Oracle][Oracle]" and the next says: "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed". It then prompts me for the User name and password of the DB I'm connecting and after I enter the information, I get the same two error messages.
Also, if I attempt to establish another connection following the "Data -> Import External Data…" steps I run into a different problem. Upon entering the information for the User Name and password prompt I get two error messages. One says "!" and the next box says "Couldn't read this file". Once I click "OK" in those boxes, the Microsoft Query window automatically opens with nothing in it.
I can't seem to figure out why I can initially pull down data and then cannot succeed afterwards. I'm unable to connect to the DB I have through Excel until I completely closed all Microsoft Excel instances.
I also use this data source in Microsoft Access without any problems.
I am trying to build a summary report that pulls from two different worksheets within the same workbook. Here's the context of my problem:
Worksheet 2: Column A has a list of dates. The corresponding information for each date is within the row. Sometimes, there are repeated dates with different corresponding information.
Worksheet 3:This is my summary sheet. This report needs to update daily and only pull data related to "today's" date. My question is if i have three rows with the same date but different data, how do i tell my summary sheet to display all three rows for that date. So far, I can only get it to pull the data from the last row with that date.
In the attached spreadsheet I need a formula to extract the value from column C that matches the date in Column E where the date in column E is equal to or falls between the dates in Columns A and B.file now attached.
I have used several IF and AND functions in the past in order to check to see if 2 values = true and return a third.
(for example =IF(AND(A1>10,B1<20),C1,"")
However I can't seem to figure out the senario below.
I have 2 coulmns, Column A has dates and Column B has a numeric value
I need to check to see if column A falls between 2 dates and returns the sum of column B
For example if have 100 rows and 10 of those rows have dates that fall between 06/01/09 and 07/01/09 then add up column b for those 10 rows and return the value.
I've been racking my brain for hours over the past few days trying to compile a spreadsheet for work. Currently the spreadsheet covers all the aspects of a auto loan process. It is used as a tool for auditors to check the work of others. The spreadsheet simply uses "P" to designate "Pass" or "F" to designate "Fail" on whichever section.
How do I reference the P's and the F's in the spreadsheet? I've had trouble getting excel to query any text in cells. Also, each loan application is started on the next line, so I need to make sure these formulas repeat as a new line is started - how would I go about doing this? Currently - Cell G3-CG3 contain the "P's" or "F's" - the formula I had used was {=IF(G3<>"P","FAIL","PASS")} - which does work, on a single cell. However, if I try to add the entire range in {=IF(G3:CG3<>"P","FAIL","PASS")} the cell returns #VALUE. Obviously, I'm inexperienced with excel in this fashion, and I need to know what formula I should be using.
I'm struggling to find a way to index data in an array that meets certain matching criteria. I am looking for an employee's rate on a given day by searching a database that lists the dates that an employee's rate was changed. I was hoping to solve it with a crafty index and match array formula but have been unable to find something that works so far. I have attached a simplified example of what I am trying to do.
I am trying to retrieve the "option" values and "inner text" from two dropdown lists on a website. Using the code below I can retrieve the data from the first list but the second dropdown is not populated until a value is selected in the first list.
Firstly, is it possible to specify a particular "select" element by using its "name" or "id" and if so how? Secondly, will I be able to populate the second list by looping through each of the values from the first list and retrieve the second list values each time?
I have been searching all day so far and cobbling together bits of code that I have found that do some but not all of the process I require.
[Code] .....
Below is a small section of the results so far. The second dropdown list will give the models based on the value selected in the first dropdown.
inner text value Alfa Romeo 53 Aston Martin 69 Audi 16 Bentley 87 BMW 17 Chevrolet 77 Chrysler 54 Citroen 18 Dacia 92 Ferrari 95 Fiat 19 Ford 20 Honda 22
I created a basic excel weekly budget and would like to know how much money I have as of todays date. on the top row I have a date range from Sunday to Saturday, so it looks like this:
09-15 16-22 23-29
with the month manually put in above it.
then below I have income and expenses with a Overall below that, so basically what I want to is see the Overall value based on todays date, not sure how to do this with the weekly range and automatic current date(which is =TODAY() as far as I know) I have attached a photo as a reference.
I have a code which sends some keys to oracle and performs some actions. What I want is, I need to check a screen in Oracle and then continue or stop, based on the screen. So, I need a msgbox with a yes or no. Is it possible that a msgbox with yes/no be popped up on the screen or oracle? I understand that it can happen on excel screen.
I need to connect to an Oracle 10g database using vba. Google has loads of snipets of code but I don't seem to be able to get it to quite work correctly.
This is what I have:
Code: Sub ADOExcelSQLServer() Dim Cn As ADODB.Connection Dim Server_Name As String Dim Database_Name As String Dim User_ID As String Dim Password As String Dim SQLStr As String
[code]....
When the connection attempts to open (red code) I get the following error:
I have a report that I import from an Oracle database.. My problem is "Sometimes" Column "L" is there and sometimes it isn't. It is a blank column that the system uses as some sort of place holder & I need to test if its there, then delete. See example below ...
I am currently querying data from an Oracle database through MS Access and then passing it to Excel for the user to work. Not all users have MS Access so I would like to automate the data pull from Excel
I am running Excel 2007 and connecting to an Oracle DB.
I have created a VBA macro to connect to an oracle DB and retrieve data then place it in a table. The code works, but it uses DSN entries which are specific to my computer. I want to be able to distribute this Excel spreadsheet to others in my company and have them be able to click one button and update the data. I figure the most logical way is to connect to the DB using an IP address that should work for anyone on the intranet.
How do I modify the connection info below to have it connect via IP?
I tried "Data Source = 10.1.1.10orcl" and "Data Source = 10.1.1.10", both of which VBA dislikes.
Here is what I have now:
Code: Sub Create() Dim Servername As Range Set Servername = ActiveWorkbook.Sheets("Summary").Range("B16")
Set StartDate = ActiveWorkbook.Sheets("Summary").Range("B3")
One of my engaging tasks at work is to disable/enable general ledger accounts (accounting speak) in our Oracle ERP application using one of their forms.
In this case it would be a list of gl accounts and to the left of the gl account would be a check box that I can click.
Checked means enabled. Blank would mean disabled.
If I have a list of currently enabled gl accounts that are to be disabled (unchecked) then I have to do so one by one. I can click with the mouse on the checkbox or I can using the keyboard use the space bar followed by the down arrow key and repeat. As fun as that sounds sometimes I'm faced with dozens or hundreds at a time.
Therefore my question is can I automate this using VBA? or any other tools out there.
Using Excel Office 2007, Oracle ERP (9.5.8) I think - it's old
I am working on an Inventory Spreadsheet. I have need it to date time stamp that willpopulate a different cell /field when text are entered in a different cell/field and will not change all the previous dates entered on the spreadsheetevery time I re-open the spreadsheet. I formula I am using is =IF(E3<>"",IF(D15="",NOW(),D15),"")and it changes each time I open thesheet to do the inventory so I do not know the date of the last inventory. Iwant to capture the actual date that the inventory was completed in thesecond/different cell?
I work for a UK charity and have a list of funders in an Excel 2007 spreadsheet.
One of the columns refers to the date on which a new application for funding can be made to that particular funder.
In many cases new applications for funding can't be made for 1 or more years since the last application - sometimes as many as 5 years later. How to get a cell to refer to the date that it contains.
For example, say I have in cell A1 "The Acme Funding Organisation" and in cell B1 (i.e. the "Reapply when?" column) a date of 01/04/2013 (British date format, i.e. 1 April 2013) then what I want Excel to do is to look at the date in cell B1 and if that date has been reached to highlight the cell red. That way I'll know that the reapply date has been reached & that a new application can be made.
In Column A each cell will contain a date (differnet from other cells in that column) when inspection was last done.
Column B is when the weekly inspection is due. Column C is when bi-weekly inspection is due. Column D is when monthly inspection is due. Column E is when 6 monthly inspection is due.
I need a formula to change the colour of cells B, C, D & E when each inspection is due depending on the date entered in A
I am hoping its possible that the cell colour can stay for 2 days after the due date and then return back to blank after the second day.
For example if cell A1 has a date of January 1 2013 then on January 8 2013 cell B1 turns red then on January 10 2013 the cell returns back to normal.
A1 B1 C1 D1 E1
Inspection Date Weeekly Due Bi-WeeklyDue Monthly Due 6 Monthly Due
January 1 2013 Change red Jan 8 & return blank Jan 10 Change red Jan 15 & return blank Jan 17 Change red Feb 1 & return blank Feb 3 Change red June 1 & return blank June 3
I have the the following spreadsheet: [URL] My formula in Columns A to E is the following:
=IF(ISNA(VLOOKUP(Query1_2[@Branch],Master!A:J,10,FALSE)),"Branch Not Open",VLOOKUP(Query1_2[@Branch],Master!A:J,10,FALSE))
I would like to do the following in Column A: If the date in Column C is greater than today, then Column A must also say "Branch Not Open". I have tried the following:
=IF(ISNA(VLOOKUP(Query1_2[@Branch],Master!A:J,10,FALSE)),"Branch Not Open",VLOOKUP(Query1_2[@Branch],Master!A:J,10,FALSE)),IF(C:C<=TODAY,"Branch Not Open, IF(AND(C:C=>TODAY,VLOOKUP(Query1_2[@Branch],Master!A:I,9,FALSE)))
I have two columns. In column B is the date of "last check". I column A is the date of "next check". I would like to have cell A2 in yellow color 334 days after the date entered in cell A3 and than in red color 365 days after the date entered in cell A3. Same thing for cell B2 related to date entered in cell B3. Yellow color in cells announces that check will expire within 30 days and red color that check has been expired.
I am trying to return a TRUE or FALSE based on a date in a cell.
for example:
if cell A1 = 07/11/2009
I want A2 to show TRUE or FALSE if A1 is 14 days or more behind todays date.
I have tried stuff like:
=IF(A1=TODAY()-14,"True","False")
but it just always says false. EDIT: I have just noticed that if I change the date in A1 to exactly 14 days behind todays, it returns TRUE. So, it does work. Do I need to add a GREATER THAN in there?
