Parameters in QryDef in Acc2000

[Gerry Abbott]

Hi all
I'm converting a db from Acc97 to 2000. Everything works, except assigning
of a qdf object containing parameters, to a recordset.

The db, recordset, and qrydef objects are all referenced as DAO objects, and
the reference to dao 3.6 is present and working.
The code breaks at the last statement (below) with an 'invalid arguament'
error 3001.

Note that the parameters are both dates.
Also note that this works perfectly well in ACC97 format.
Also that it compiled without any indication of errors, or lost references.
me.start and me.end are taken from controls on an imput form. They are
formatted as short dates.

Thanks
Gerry Abbott

Code extract
-------------------------------------------------------------------
Dim qdfAdj As DAO.QueryDef
Dim rsAdj As DAO.Recordset
Dim mySql As String
Dim db As DAO.Database

Set db = CurrentDb
Set qdfAdj = db.QueryDefs("qryAccountsAdjSummary")

qdfAdj.Parameters("parameter1") = Me.Start
qdfAdj.Parameters("parameter2") = Me.End
Set rsAdj = qdfAdj.OpenRecordset

"qryAccountsAdjSummary"
SELECT tblStockItems.ProdId, tblMovements.mQuantity
FROM tblStockItems INNER JOIN tblMovements ON tblStockItems.stockId =
tblMovements.stockId
WHERE (((tblMovements.LocId)=sysconst(3)) AND ((tblMovements.sDate) Between
[parameter1] And [parameter2]))
ORDER BY tblStockItems.ProdId;
---------------------------------------------------------------------

[Gerry Abbott]

Hi all again.
I solved this problem, but still don't know why there is a different
behaviour in Acc97/2000.
Anothe filter in the query drew its parameter value from a function
Sysconst(3). I replaced this with [P3], another parameter,
and using the parameters property of the QDF, I assigned this parameter with
value returned by the function.

so
..........tblMovements.LocId)=sysconst(3)) ......
becomes
..........tblMovements.LocId= [Parameter3]..........

and

qdfAdj.Parameters("parameter1") = Me.Start
qdfAdj.Parameters("parameter2") = Me.End
Set rsAdj = qdfAdj.OpenRecordset

becomes

qdfAdj.Parameters("parameter1") = Me.Start
qdfAdj.Parameters("parameter2") = Me.End
qdfAdj.Parameters("parameter3")=sysconst(3)
Set rsAdj = qdfAdj.OpenRecordset

I also tried using =val(sysconst(3)) to get a value number arguament, but it
dident help !

Still does not explain why it works in one and not the other version!!


"Gerry Abbott" <pl****@ask.ie> wrote in message
news:Wz*****************@news.indigo.ie...

Hi all
I'm converting a db from Acc97 to 2000. Everything works, except assigning
of a qdf object containing parameters, to a recordset.

The db, recordset, and qrydef objects are all referenced as DAO objects, and the reference to dao 3.6 is present and working.
The code breaks at the last statement (below) with an 'invalid arguament'
error 3001.

Note that the parameters are both dates.
Also note that this works perfectly well in ACC97 format.
Also that it compiled without any indication of errors, or lost references. me.start and me.end are taken from controls on an imput form. They are
formatted as short dates.

Thanks
Gerry Abbott

Code extract
-------------------------------------------------------------------
Dim qdfAdj As DAO.QueryDef
Dim rsAdj As DAO.Recordset
Dim mySql As String
Dim db As DAO.Database

Set db = CurrentDb
Set qdfAdj = db.QueryDefs("qryAccountsAdjSummary")

qdfAdj.Parameters("parameter1") = Me.Start
qdfAdj.Parameters("parameter2") = Me.End
Set rsAdj = qdfAdj.OpenRecordset

"qryAccountsAdjSummary"
SELECT tblStockItems.ProdId, tblMovements.mQuantity
FROM tblStockItems INNER JOIN tblMovements ON tblStockItems.stockId =
tblMovements.stockId
WHERE (((tblMovements.LocId)=sysconst(3)) AND ((tblMovements.sDate) Between [parameter1] And [parameter2]))
ORDER BY tblStockItems.ProdId;
---------------------------------------------------------------------