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)