Date as a Parameter in MSQuery(SOLVED)

[lenze]

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

 

[dk]

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]

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]

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]

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]

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]

Lenze,

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

Dan

 

[Mark W.]

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]

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]

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.