Still Need help

[brett]

I think your last suggestion is what I'm looking for; I'm
trying to make the code change the criteria of the query
for one of the query fields. I attempted what you said
and the qdf.parameters line fails with a 3265 error ("Item
Not Found In This Collection"). Am I still doing
something wrong??? Please see the code below and I am
using Access 2000 (not 97).

Dim Year As Integer

Function SetYear()
Set dbs = CurrentDb
Year = InputBox("Enter the year:")
Set qdf = dbs.QueryDefs("qryFileNetPercentUptime:
quarter 1")
qdf.Parameters("[beginning date]") = Year
End Function

-----Original Message-----

DoCmd.OpenQuery "qryFileNetPercentUptime: quarter 1",
acViewDesign
Query![qryFileNetPercentUptime: quarter 1]! [Beginning
Date].Criteria = Year

If you just want to open a query datasheet, you don't have to put up the
input box first: if the query is created with the parameter then the DoCmd
object will put up its own input box to get the value.

An alternative, if you want more control, is to change the parameter in the
query to something that the expression engine can see, such as a control on
an open form:

....WHERE Year(ContractDate) = Forms!"frmPickAYear"! txtYearNumber

Finally if you are after the recordset itself, you can use the QueryDef
object:

Set qdf = QueryDefs("qryFileNetPercentUptime")
qdf.Parameters("[Beginning Date]") = 1998

Set rs = qdf.OpenRecordset(dbSnapshot, dbForwardOnly)
' etc etc


Hope that helps



Tim F

 

[Geoff]

In query design view, have you opened the Query
menu, selected Parameters and completed the
Parameters dialog (Access 2000)? You need to
do this to insert a Parameters clause.

To check whether you have or have not inserted
a parameters clause, in query design view, open
the View menu and select SQL view. You will see
your query as an SQL statement, which should
begin with the word Parameters.

I haven't seen your earlier posts, so hope this
addresses your issue.

Regards
Geoff