Date as a Parameter in MSQuery(SOLVED)

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
I want to query an oracle database and would like to use a parameter to return all records with an overdue inspection date (ie, Due Date is less than Today(). It need to be volatile as others need to run the query on a weekly basis. I can't seem to get it to function using either a prompt or a cell reference.
TIA
This message was edited by lenze on 2002-09-16 06:58
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

dk

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
On 2002-09-12 13:26, lenze wrote:
I want to query an oracle database and would like to use a parameter to return all records with an overdue inspection date (ie, Due Date is less than Today(). It need to be volatile as others need to run the query on a weekly basis. I can't seem to get it to function using either a prompt or a cell reference.
TIA

Lenze,

You can put parameters into an external data query but it involves you editing the SQL in MS Query. Open the query in MS Query and click the SQL button.

The syntax for putting a parameter in is :-

SELECT Fields FROM Database WHERE(Paramater Operator?)


SELECT tblPayments.PaymentDate, tblPayments.PaymentReference
FROM `M:AccountsPDStest pds`.tblPayments tblPayments
WHERE (tblPayments.PaymentDate>?)


Each time you refresh the query it asks for a date and returns records based on that.


_________________<font face="Impact">Hope this helps,
Dan</font>
This message was edited by dk on 2002-09-13 01:02
 

ChrisUK

Well-known Member
Joined
Sep 3, 2002
Messages
675
You can pass a parameter from a cell in the worksheet if you use VBA. Here's an example

Dim SQLStatement
Dim ConnectString
Dim MyODBCConnection

MyODBCConnection = "UsersDB"

' Open connection
' You need to set the ADO reference up in the Tools/Refence menu (Microsoft Actice X data objects Library)
Set ConnectDB = New ADODB.Connection
ConnectString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=" & MyODBCConnection
ConnectDB.Open ConnectString

' Open recordset from database table
Set rsNames = New ADODB.Recordset
rsNames.CursorLocation = adUseClient

' Use client cursor to enable AbsolutePosition property
SQLStatement = "SELECT * from testtable where NameField ='" & Cells(1, 3).Value & "'"

rsNames.Open SQLStatement, ConnectDB, adOpenStatic, adLockReadOnly, adCmdText

' Setup a counter to run down the spreadsheet rows
LineCount = 1

' Loop through returned records
Do While Not rsNames.EOF
Worksheets("Sheet1").Cells(LineCount, 1).Value = rsNames.Fields("NameField")
Worksheets("Sheet1").Cells(LineCount, 2).Value = rsNames.Fields("DetailsField")
LineCount = LineCount + 1
rsNames.MoveNext
Loop

Set ConnectDB = Nothing
Set rsNames = Nothing

Hope this helps
 

WillR

Well-known Member
Joined
Feb 18, 2002
Messages
1,143
I have a sheet with an MSQuery in it that uses something similar to what you seem to want. I have two parameter fields created in my msquery called [From] and [To]
In the criteria box in msquery the field i typed >=[From] (referring to "fr_date" in my sql table - likewise, the other parameter id <=[To]
Both refer to cells in my XL workbook which could return the value of a formula such as =today()
And I just tested it and it works fine...
Don't think there's any need for a more complex approach really....
 

WillR

Well-known Member
Joined
Feb 18, 2002
Messages
1,143

ADVERTISEMENT

On 2002-09-12 13:26, lenze wrote:
I want to query an oracle database and would like to use a parameter to return all records with an overdue inspection date (ie, Due Date is less than Today(). It need to be volatile as others need to run the query on a weekly basis. I can't seem to get it to function using either a prompt or a cell reference.
TIA

Probably i wasn't too helpful with my last reply on reading it through.

In the MSquery editor criteria pane, select your Due date field as the criteria field
On the value: line underneath type <=[Today]
Today now becomes a Parameter Field.
If you press refresh, it should prompt you for a date (remember to set your data type of parameter field by selecting View / Parameters from menu to that in your database i.e. DateTime)....you should be able too specify the date & run the query in MSquery now - return the data to excel.

To reference this to a cell in your work book do the following. right click on the returned data...and selet Parameters... you should get a box with Today in and three options prompt for value... use the following value... OR Get the value from the following cell.... select option 3 and click on the cell in the workbook that will i guess contain the formula =Today()....
And that works for me querying a SQL database where i want the user to input a 'From' date, a 'To' Date & an 'Account number' to get Stats for a period for One account....

Hope this helps and was better explained...
:)
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
Thanks for the replies, but I'm still having problems. I can hardcode a specific criteria in either SQL or as a criteria in MSQuery, such as<#9/1/02# and it works fine. The minute, however, I change to a parameter, either in SQL (Tbl.field<?) or in MSQuery (<[Enter Date]) it doesn't work. I get an error message of "invalid month" or "literal does not match format string" I guess I have some kind of formatting problem betweem MSQuery and Oracle. Any ideas?
This message was edited by lenze on 2002-09-13 08:30
 

dk

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942

ADVERTISEMENT

Lenze,

Post the SQL and then me or someone else who has an Oracle DB can try it.

Dan
 

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
What happens when you enter your date as...

1-Sep-02
This message was edited by Mark W. on 2002-09-14 11:06
 

Chris Davison

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,790
Lenze...

I had this problem also this week

"type mismatch" was the error message when I tried to bring back info where the transaction date was >= (today()-7)

ie

SELECT * from agltransact WHERE trans_date>=$J$5

with $J$5 housing the fucntion =TODAY()-7

(it's SQL via an off-the-shelf-customiseable add-in that interrogates our accountign system database, so I'll keep you in mind if the VBA experts who wrote the add-in get back to me with a solution)

"Agresso Excelerator", if anyone else uses this....

Chris
:)
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
Thanks for all the input guys. Mark W. hit it with his suggestion. When I entered the date in the parameter prompt as 1-Sep-02 it worked fine. I still couldn't get it to accept a cell reference until I changed the reference cell from =TODAY() to =TEXT(TODAY(),"dd mmm yy"). Works perfect. Thanks all.
 

Forum statistics

Threads
1,144,050
Messages
5,722,234
Members
422,417
Latest member
Johhny

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top