Passing form values into a query through a VBA Query.

[dowlingm815]

When attempting to pass date parameters through a form into VBA code, the query contains no records. however, if the values are placed in the query, values are returned, for instance, plugging in

Between #01/1/2010# And #3/3/2010# 
 

instead of the

Between [Forms]![F_PR_Status]![txb_Start_Date] And [Forms]![F_PR_Status]![txb_End_Date])
 

I would appreciate any guidance.

The following values are being passed through a screen during an event procedure into Access VBA code:

[Forms]![F_PR_Status]![txb_Start_Date]
[Forms]![F_PR_Status]![txb_End_Date]
 

The code that calls this routine is as follows:

Option Compare Database
 
Dim rst As DAO.Recordset
Dim rstTemp As Recordset
Dim rstSummary As Recordset
Dim rstWorkflowLength As Recordset
 
 
Dim i As Long
Dim dbs As DAO.Database
Dim strSQL As String
 
Dim strFirstRec As String
 
Private Sub Create_Report_Click()
On Error GoTo Err_Hndlr
 
Call Q_PR_date
 
 
Create_Report_Click_Exit:
  Exit Sub
 
 
Err_Hndlr:
    MsgBox "[" & Err.Number & "]:  " & Err.Description, vbInformation, "Create_Report_Click_Exit()"
End Sub
Private Sub Q_PR_date()
On Error GoTo Err_Hndlr
 
'**********************************************
Dim dbs As Database
Dim strSQL As String
Dim strQueryName As String
Dim qryDef As QueryDef
 
 Debug.Print Forms!F_PR_Status!txb_Start_Date; " "; Forms!F_PR_Status!txb_End_Date
 
    If (Eval("[Forms]![F_PR_Status]![txb_Start_Date] Is Null")) Then
        MsgBox "Please select a Starting Date    ", vbInformation, "Required Date"
        StartingBoardDate.SetFocus
        Exit Sub
    End If
 
    If (Eval("[Forms]![F_PR_Status]![txb_End_Date] Is Null")) Then
        MsgBox "Please select an Ending Date     ", vbInformation, "Required Date"
        EndingBoardDate.SetFocus
        Exit Sub
    End If
 
 
'set variable values
Set dbs = CurrentDb
strQueryName = "sql_PR_date"
 
'Delete old query first - we want fresh data!
dbs.QueryDefs.Delete strQueryName
 
'Notice below how we inserted the variable as a parameter value - Visual Basic will evaluate strMonth and insert the value for us.
 
 
strSQL = "SELECT T_PRApprovalHistory.PR_ID, " & _
                "T_PRApprovalHistory.PR_Date, " & _
                "tblWorkflowApprovalStep.Workflow_Step_Order, " & _
                "T_PRApprovalHistory.Workflow_Step_Name, " & _
                "T_PRApprovalHistory.Workflow_Step_Date, " & _
                "Count(T_PRApprovalHistory.Workflow_Step_Date) AS CountOfWorkflow_Step_Date " & _
        "FROM T_PRApprovalHistory " & _
                "INNER JOIN tblWorkflowApprovalStep ON " & _
                "T_PRApprovalHistory.Workflow_Step_Name = tblWorkflowApprovalStep.Workflow_Step_Name " & _
        "GROUP BY T_PRApprovalHistory.PR_ID, " & _
                "T_PRApprovalHistory.PR_Date, " & _
                "tblWorkflowApprovalStep.Workflow_Step_Order, " & _
                "T_PRApprovalHistory.Workflow_Step_Name, " & _
                "T_PRApprovalHistory.Workflow_Step_Date " & _
        "HAVING (((T_PRApprovalHistory.PR_Date) " & _
                "Between [Forms]![F_PR_Status]![txb_Start_Date] And [Forms]![F_PR_Status]![txb_End_Date])) " & _
        "ORDER BY T_PRApprovalHistory.PR_ID, " & _
                "tblWorkflowApprovalStep.Workflow_Step_Order, " & _
                "T_PRApprovalHistory.Workflow_Step_Date;"
 
'Create query definition
Set qryDef = dbs.CreateQueryDef(strQueryName, strSQL)
 
 
Q_PR_date_Exit:
  Exit Sub
 
 
Err_Hndlr:
    MsgBox "[" & Err.Number & "]:  " & Err.Description, vbInformation, "Q_PR_date()"
End Sub
 

[NeoPa]

I cannot see why the date literals posted would not work just as well as the date control references (Literal DateTimes and Their Delimiters (#) may help). However, I can't understand what you're actually asking either, so I'm not sure how to help. What relevance has that great big pile of code got to the situation? No line # references to clarify for a reader where it may fit in.

[ADezii]

Try modifying the HAVING Clause, namely:

"HAVING (((T_PRApprovalHistory.PR_Date) " & _
                "Between #" & [Forms]![F_PR_Status]![txb_Start_Date]" & "# And #" & _
                [Forms]![F_PR_Status]![txb_End_Date] & #)) " & _

[NeoPa]

That should work reliably anywhere in the USA. From the linked article though, portable code is achieved by formatting the values explicitly as "m/d/yyyy", assuming of course, that the requirement is to use literals in place of the values already available.

[dowlingm815]

Receiving a syntax error on # added..

            ("SELECT T_PRApprovalHistory.PR_ID, " & _
            "T_PRApprovalHistory.PR_Date, " & _
            "tblWorkflowApprovalStep.Workflow_Step_Order, " & _
            "T_PRApprovalHistory.Workflow_Step_Name, " & _
            "T_PRApprovalHistory.Workflow_Step_Date, " & _
            "Count(T_PRApprovalHistory.Workflow_Step_Date) AS CountOfWorkflow_Step_Date " & _
            "FROM T_PRApprovalHistory INNER JOIN tblWorkflowApprovalStep ON " & _
            "T_PRApprovalHistory.Workflow_Step_Name = tblWorkflowApprovalStep.Workflow_Step_Name " & _
            "GROUP BY T_PRApprovalHistory.PR_ID, T_PRApprovalHistory.PR_Date, tblWorkflowApprovalStep.Workflow_Step_Order, " & _
            "T_PRApprovalHistory.Workflow_Step_Name, T_PRApprovalHistory.Workflow_Step_Date " & _
            "HAVING (((T_PRApprovalHistory.PR_Date) " & _
                "Between #" & [Forms]![F_PR_Status]![txb_Start_Date]" & "# And #"[Forms]![F_PR_Status]![txb_End_Date] & #)) " & _
            "ORDER BY T_PRApprovalHistory.PR_ID, tblWorkflowApprovalStep.Workflow_Step_Order, T_PRApprovalHistory.Workflow_Step_Date;")

[dowlingm815]

The code needs to go as follows:

Private Sub Insert_Temp_Table()
 
On Error GoTo Err_Hndlr
'Delete temporary table
DoCmd.RunSQL "DROP TABLE tblTemp;"
 
'Create temporary table
CurrentDb.Execute ("CREATE TABLE tblTemp(PR_Id integer, PR_Date DATETIME, Workflow_Step_Order smallint, Workflow_Step_Name VARCHAR(50), Workflow_Step_Date DATETIME, CountOfWorkflow_Step_Date smallint)")
 
'Bind rstTemp to the temporary table
Set rstTemp = CurrentDb.OpenRecordset("tblTemp")
 
 
'Grab the data and put it in rst
 
 
 
Set rst = CurrentDb.OpenRecordset _
            ("SELECT T_PRApprovalHistory.PR_ID, " & _
            "T_PRApprovalHistory.PR_Date, " & _
            "tblWorkflowApprovalStep.Workflow_Step_Order, " & _
            "T_PRApprovalHistory.Workflow_Step_Name, " & _
            "T_PRApprovalHistory.Workflow_Step_Date, " & _
            "Count(T_PRApprovalHistory.Workflow_Step_Date) AS CountOfWorkflow_Step_Date " & _
            "FROM T_PRApprovalHistory INNER JOIN tblWorkflowApprovalStep ON " & _
            "T_PRApprovalHistory.Workflow_Step_Name = tblWorkflowApprovalStep.Workflow_Step_Name " & _
            "GROUP BY T_PRApprovalHistory.PR_ID, T_PRApprovalHistory.PR_Date, tblWorkflowApprovalStep.Workflow_Step_Order, " & _
            "T_PRApprovalHistory.Workflow_Step_Name, T_PRApprovalHistory.Workflow_Step_Date " & _
            "HAVING (((T_PRApprovalHistory.PR_Date) Between [Forms]![F_PR_Status]![txb_Start_Date] And [Forms]![F_PR_Status]![txb_End_Date])) " & _
            "ORDER BY T_PRApprovalHistory.PR_ID, tblWorkflowApprovalStep.Workflow_Step_Order, T_PRApprovalHistory.Workflow_Step_Date;")

[ADezii]

Your HAVING Clause is NOT what I suggested.

[NeoPa]

You seem to have ignored my comment too. Maybe you're just not interested in portable code ;)

[dowlingm815]

So sorry guys, i was in the ER last week with a concussion, just getting back to this...will try later this morning. i really do appreciate your guidance.

[dowlingm815]

when the # were added to the code an error was generated:

HAVING (((T_PRApprovalHistory.PR_Date) " & _ 
                "Between #" & [Forms]![F_PR_Status]![txb_Start_Date]" & "# And #" & _ 
                [Forms]![F_PR_Status]![txb_End_Date] & #)) " & _ 
 

[ADezii]

What exactly was the Error displayed?

[dowlingm815]

gosh, that was last early week, can't remember, but the original code, the first placed in this forum is working correctly.

Now, it needs to go into a more complex procedure. can this sql statement go into a openrecord statement as shown in the code below. however, it is generating a "data type conversion" error. my guess it is the openrecode statement, any suggestions?

Dim dbs As Database
Dim strSQL As String
Dim strQueryName As String
 
 
'set variable values
Set dbs = CurrentDb
strQueryName = "sql_PR_date"
 
'Delete old query first - we want fresh data!
'dbs.QueryDefs.Delete strQueryName
 
strSQL = "SELECT T_PRApprovalHistory.PR_ID, " & _
                "T_PRApprovalHistory.PR_Date, " & _
                "tblWorkflowApprovalStep.Workflow_Step_Order, " & _
                "T_PRApprovalHistory.Workflow_Step_Name, " & _
                "T_PRApprovalHistory.Workflow_Step_Date, " & _
                "Count(T_PRApprovalHistory.Workflow_Step_Date) AS CountOfWorkflow_Step_Date " & _
        "FROM T_PRApprovalHistory " & _
                "INNER JOIN tblWorkflowApprovalStep ON " & _
                "T_PRApprovalHistory.Workflow_Step_Name = tblWorkflowApprovalStep.Workflow_Step_Name " & _
        "GROUP BY T_PRApprovalHistory.PR_ID, " & _
                "T_PRApprovalHistory.PR_Date, " & _
                "tblWorkflowApprovalStep.Workflow_Step_Order, " & _
                "T_PRApprovalHistory.Workflow_Step_Name, " & _
                "T_PRApprovalHistory.Workflow_Step_Date " & _
        "HAVING (((T_PRApprovalHistory.PR_Date) " & _
                "Between [Forms]![F_PR_Status]![txb_Start_Date] And [Forms]![F_PR_Status]![txb_End_Date])) " & _
        "ORDER BY T_PRApprovalHistory.PR_ID, " & _
                "tblWorkflowApprovalStep.Workflow_Step_Order, " & _
                "T_PRApprovalHistory.Workflow_Step_Date;"
 
 
Set rst = CurrentDb.OpenRecordset(strQueryName, strSQL)
 
 

[ADezii]

You are passing a Query Name and an SQL Statement to the OpenRecordset() Method. This is not how it works - check the Help System for OpenRecordset.

[dowlingm815]

when define recordset as follows, it generates "Too few parameters. Expected 2."

 
 
Set rst = CurrentDb.OpenRecordset("SELECT T_PRApprovalHistory.PR_ID, " & _
                "T_PRApprovalHistory.PR_Date, " & _
                "tblWorkflowApprovalStep.Workflow_Step_Order, " & _
                "T_PRApprovalHistory.Workflow_Step_Name, " & _
                "T_PRApprovalHistory.Workflow_Step_Date, " & _
                "Count(T_PRApprovalHistory.Workflow_Step_Date) AS CountOfWorkflow_Step_Date " & _
        "FROM T_PRApprovalHistory " & _
                "INNER JOIN tblWorkflowApprovalStep ON " & _
                "T_PRApprovalHistory.Workflow_Step_Name = tblWorkflowApprovalStep.Workflow_Step_Name " & _
        "GROUP BY T_PRApprovalHistory.PR_ID, " & _
                "T_PRApprovalHistory.PR_Date, " & _
                "tblWorkflowApprovalStep.Workflow_Step_Order, " & _
                "T_PRApprovalHistory.Workflow_Step_Name, " & _
                "T_PRApprovalHistory.Workflow_Step_Date " & _
        "HAVING (((T_PRApprovalHistory.PR_Date) " & _
                "Between [Forms]![F_PR_Status]![txb_Start_Date] And [Forms]![F_PR_Status]![txb_End_Date])) " & _
        "ORDER BY T_PRApprovalHistory.PR_ID, " & _
                "tblWorkflowApprovalStep.Workflow_Step_Order, " & _
                "T_PRApprovalHistory.Workflow_Step_Date;")
 
 

if i leave out the following statement it runs, but without the date parameters"

 
HAVING (((T_PRApprovalHistory.PR_Date) " & _
                "Between [Forms]![F_PR_Status]![txb_Start_Date] And [Forms]![F_PR_Status]![txb_End_Date])) "
 
 

[ADezii]

Copy and Paste this code exactly, and see what happens:

Dim dbs As dao.Database
Dim rst As dao.Recordset
Dim strSQL As String
 
'set variable values
Set dbs = CurrentDb
 
strSQL = "SELECT T_PRApprovalHistory.PR_ID, " & _
                "T_PRApprovalHistory.PR_Date, " & _
                "tblWorkflowApprovalStep.Workflow_Step_Order, " & _
                "T_PRApprovalHistory.Workflow_Step_Name, " & _
                "T_PRApprovalHistory.Workflow_Step_Date, " & _
                "Count(T_PRApprovalHistory.Workflow_Step_Date) AS CountOfWorkflow_Step_Date " & _
        "FROM T_PRApprovalHistory " & _
                "INNER JOIN tblWorkflowApprovalStep ON " & _
                "T_PRApprovalHistory.Workflow_Step_Name = tblWorkflowApprovalStep.Workflow_Step_Name " & _
        "GROUP BY T_PRApprovalHistory.PR_ID, " & _
                "T_PRApprovalHistory.PR_Date, " & _
                "tblWorkflowApprovalStep.Workflow_Step_Order, " & _
                "T_PRApprovalHistory.Workflow_Step_Name, " & _
                "T_PRApprovalHistory.Workflow_Step_Date " & _
        "HAVING T_PRApprovalHistory.PR_Date " & _
                "Between #" & [Forms]![F_PR_Status]![txb_Start_Date] & _
                "# And #" & [Forms]![F_PR_Status]![txb_End_Date] & "# " & _
        "ORDER BY T_PRApprovalHistory.PR_ID, " & _
                "tblWorkflowApprovalStep.Workflow_Step_Order, " & _
                "T_PRApprovalHistory.Workflow_Step_Date;"
 
  Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

[dowlingm815]

Thank you...it works!

[ADezii]

We were bound to get it sooner or later! (LOL)

[NeoPa]

Nice work boys.

Sorry to hear you were in the ER Dowling. I hope all is sorted now and you're back to full health.