Run Access parameter query from code?

Hello Experts
I want to run a function that executes a paramter query and returns the answer.
I think I'm close but I'm stuck on my poor understaning of querydefs
Here is my code. Help! Please?

Public Function PreviousRptDt(AsOfDt As Date)
Dim db As Database
Dim qdf1 As QueryDef
Dim StrSQL As String
Dim rs1 As DAO.Recordset

Set db = CurrentDb()

StrSQL = "PARAMETERS [Forms]![frmMain]![cboAsOfDate] DateTime; "
StrSQL = StrSQL & "SELECT Max(dbo_tblSPL.ReportDate) AS MaxOfReportDate "
StrSQL = StrSQL & "FROM dbo_tblSPL "
StrSQL = StrSQL & "WHERE (((dbo_tblSPL.ReportDate)<[Forms]![frmMain]![cboAsOfDate]));"

Set qdf1 = db.QueryDefs(StrSQL)
DoCmd.RunSQL StrSQL

Set rs1 = CurrentDb.OpenRecordset(StrSQL)
rs1.MoveFirst
PreviousRptDt = rs1!MaxofReportDate
rs1.Close
rhk6446Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
StrSQL = "SELECT Max(dbo_tblSPL.ReportDate) AS MaxOfReportDate "
StrSQL = StrSQL & "FROM dbo_tblSPL "
StrSQL = StrSQL & "WHERE dbo_tblSPL.ReportDate < #" & [Forms]![frmMain]![cboAsOfDate] & "#"

Set rs1 = CurrentDb.OpenRecordset(StrSQL)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
StanyslawCommented:
Use QueryDef, set parameters, then execute Query (for insert/update) or Use recordset for select

Set qdf1 = db.QueryDefs(StrSQL)

qdf1.Parameters("[Forms]![frmMain]![cboAsOfDate]")=value
set rs1=qdf1.OpenRecordSet()

and work with rs1
0
rhk6446Author Commented:
Hi Stanyslaw,
I think you a re very close to the solution, but my code returns the error:
"Item not found in this collection" on line
Set qdf1 = db.QueryDefs(StrSQL)

any ideas?
0
StanyslawCommented:
Ops,
the example is for parametrized saved query - instead of strSQL there should be query name.

If query should be created from strSQL -
Set qdf1 = CreateQueryDef ("", StrSQL)

Empty first parameter causes creating for temporary query.
0
rhk6446Author Commented:
Public Function PreviousRptDt(AsofDt As Date) As Date
Dim db As Database
Dim qdf1 As QueryDef
Dim StrSQL As String
Dim rs1 As DAO.Recordset

Set db = CurrentDb()

StrSQL = "PARAMETERS AsOfDt DateTime; "
StrSQL = StrSQL & "SELECT Max(dbo_tblSPL.ReportDate) AS MaxOfReportDate "
StrSQL = StrSQL & "FROM dbo_tblSPL "
StrSQL = StrSQL & "WHERE (((dbo_tblSPL.ReportDate)<AsOfDt));"

Set qdf1 = db.CreateQueryDef("", StrSQL)
qdf1.Parameters("AsOfDt") = AsofDt
Set rs1 = qdf1.OpenRecordset()

If rs1.RecordCount > 0 Then
    PreviousRptDt = rs1!MaxofReportDate
End If
rs1.Close
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.