I have a saved query (a view) from which I would like to navigate through in its entirety as a recordset. This query is built off of other saved queries. The saved query is called "qryCTI".
There are a few queries to which it is dependent upon, but the one that it is directly tied to is "qryTimeBill". qryTimeBill requires a parameter which is passed through by objects in the opened main form called frmMenu.
The WHERE clause of "qryTimeBill" runs a nested IIF conditional to check for a parameter for qryMaster.Billing_Date. The condition is a nested IIF statement to search a date field (billing_date) to see whether to check for null values or a date range:
IIf(IsNull([Forms]![frmMenu]![txtBillEnd]),
([qryMaster].[BILLING_DATE]) Is Null,
(([qryMaster].[BILLING_DATE]) Between
IIf(IsNull([Forms]![frmMenu]![txtBillStart]),
[Forms]![frmMenu]![txtBillEnd],
[Forms]![frmMenu]![txtBillStart])
And [Forms]![frmMenu]![txtBillEnd]))
In this case (for billing), I am only concerned with dealing with those that are null.
I've tried three ways of opening the recordset but with each attempt, I continue to get a "Too few parameters" error message when executing the code. If I open up the saved query, the correct data is shown (while the main form remains opened), i.e. the parameters were successfully picked up. I'm thinking it has to do with some involving the dependecy of my query.
The code below shows what I was attempting to do. My alternatives are not working correctly (shown in the last three lines).
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb()
Dim sqlCTI As String
sqlCTI = "SELECT * FROM qryCTI"
Dim qd As QueryDef
Set qd = db.QueryDefs!qryCTI
Set rs = qd.OpenRecordset
' Set rs = db.OpenRecordset(sqlCTI)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb()
' Dim sqlCTI As String
' sqlCTI = "SELECT * FROM qryCTI"
'Dim qd As QueryDef
'Set qd = db.QueryDefs!qryCTI
'Set rs = qd.OpenRecordset
'Set rs = db.OpenRecordset(sqlCTI)
Set rs = db.OpenRecordset("qryCTI")
Please advise as to how I would best handle this problem.
Thanks in advance!
There are a few queries to which it is dependent upon, but the one that it is directly tied to is "qryTimeBill". qryTimeBill requires a parameter which is passed through by objects in the opened main form called frmMenu.
The WHERE clause of "qryTimeBill" runs a nested IIF conditional to check for a parameter for qryMaster.Billing_Date. The condition is a nested IIF statement to search a date field (billing_date) to see whether to check for null values or a date range:
IIf(IsNull([Forms]![frmMenu]![txtBillEnd]),
([qryMaster].[BILLING_DATE]) Is Null,
(([qryMaster].[BILLING_DATE]) Between
IIf(IsNull([Forms]![frmMenu]![txtBillStart]),
[Forms]![frmMenu]![txtBillEnd],
[Forms]![frmMenu]![txtBillStart])
And [Forms]![frmMenu]![txtBillEnd]))
In this case (for billing), I am only concerned with dealing with those that are null.
I've tried three ways of opening the recordset but with each attempt, I continue to get a "Too few parameters" error message when executing the code. If I open up the saved query, the correct data is shown (while the main form remains opened), i.e. the parameters were successfully picked up. I'm thinking it has to do with some involving the dependecy of my query.
The code below shows what I was attempting to do. My alternatives are not working correctly (shown in the last three lines).
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb()
Dim sqlCTI As String
sqlCTI = "SELECT * FROM qryCTI"
Dim qd As QueryDef
Set qd = db.QueryDefs!qryCTI
Set rs = qd.OpenRecordset
' Set rs = db.OpenRecordset(sqlCTI)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb()
' Dim sqlCTI As String
' sqlCTI = "SELECT * FROM qryCTI"
'Dim qd As QueryDef
'Set qd = db.QueryDefs!qryCTI
'Set rs = qd.OpenRecordset
'Set rs = db.OpenRecordset(sqlCTI)
Set rs = db.OpenRecordset("qryCTI")
Please advise as to how I would best handle this problem.
Thanks in advance!
