Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 493
  • Last Modified:

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
0
rhk6446
Asked:
rhk6446
  • 2
  • 2
1 Solution
 
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
 
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

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now