Passing parameters to queries using VBA

[owengoodhew]

Guys I need your help/Advice...

In my Access Database I have a query (lets say qry1) and in this query
i have 2 fields for start and end date, which is provided by 2 Get
functions.

also i have qry2 based on qry1
then qry3 based on qry2
and finally qry4(using sql in code) based on qry3, and non of these 3
queries have the start and end date fields.

now here is the problem: I am trying to set the criteria in qry4 and
then open a record set on this query(qry4) to use the data that it
pulls out...

strsql = ""
strsql = "SELECT Sum AS AREA_TOTAL " _
& "FROM qry3 " _
& "WHERE ENERGY_AREA like '" & Area & "';"
 
Set MyDB = CurrentDb
 
Set rst = MyDB.OpenRecordset(strsql)
 
 

but when the last line is executed I get this error message:

Runtime Error '3061':
Too few parameters, expected 2.

i also tried doing this:

strsql = ""
strsql = "SELECT Sum AS AREA_TOTAL " _
& "FROM qry3 " _
& "WHERE ENERGY_AREA like '" & Area & "';"
 
Set MyDB = CurrentDb
 
 
MyDB.QueryDefs("qry4").sql = strsql
 
Set rst = MyDB.OpenRecordset("qry4")
 
 

but when the last line is executed it gives me an error message saying
that the query does not exist or the name is not spelled correctly.
(Ps. I have created the query and the criteria does update once the
Select statement is run in the code!)

again the reason for this is that the query has not been populated as
the main query (qry1) needs 'strat date' and 'end date'!

Is there anyway I can pass these 2 parameters to qry4 directly using
code? If there is a way then this will definitly work as i tried
opening the query manually in the Query window and after I input the 2
dates in the input box the query ran successfully!

I would appreciate any help/suggestion guys,

[steve.minnaar]

You could use two functions like:

Public Function StartFn() As Date
StartFn= GlStart
End Function

Public Function EndFn() As Date
EndFn = GlEnd
End Function

Where GlStart and GlEnd are two global date variables.

Your query which needs to filter on dates would then have a criterion:
Between StartFn() And EndFn()

Thick Quinker - I just never learn.

*** Sent via Developersdex http://www.developersdex.com ***