By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,627 Members | 1,870 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,627 IT Pros & Developers. It's quick & easy.

Passing form values into a query through a VBA Query.

100+
P: 133
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

Expand|Select|Wrap|Line Numbers
  1. Between #01/1/2010# And #3/3/2010# 
  2.  
instead of the

Expand|Select|Wrap|Line Numbers
  1. Between [Forms]![F_PR_Status]![txb_Start_Date] And [Forms]![F_PR_Status]![txb_End_Date])
  2.  
I would appreciate any guidance.

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

Expand|Select|Wrap|Line Numbers
  1. [Forms]![F_PR_Status]![txb_Start_Date]
  2. [Forms]![F_PR_Status]![txb_End_Date]
  3.  


The code that calls this routine is as follows:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Dim rst As DAO.Recordset
  4. Dim rstTemp As Recordset
  5. Dim rstSummary As Recordset
  6. Dim rstWorkflowLength As Recordset
  7.  
  8.  
  9. Dim i As Long
  10. Dim dbs As DAO.Database
  11. Dim strSQL As String
  12.  
  13. Dim strFirstRec As String
  14.  
  15. Private Sub Create_Report_Click()
  16. On Error GoTo Err_Hndlr
  17.  
  18. Call Q_PR_date
  19.  
  20.  
  21. Create_Report_Click_Exit:
  22.   Exit Sub
  23.  
  24.  
  25. Err_Hndlr:
  26.     MsgBox "[" & Err.Number & "]:  " & Err.Description, vbInformation, "Create_Report_Click_Exit()"
  27. End Sub
  28. Private Sub Q_PR_date()
  29. On Error GoTo Err_Hndlr
  30.  
  31. '**********************************************
  32. Dim dbs As Database
  33. Dim strSQL As String
  34. Dim strQueryName As String
  35. Dim qryDef As QueryDef
  36.  
  37.  Debug.Print Forms!F_PR_Status!txb_Start_Date; " "; Forms!F_PR_Status!txb_End_Date
  38.  
  39.     If (Eval("[Forms]![F_PR_Status]![txb_Start_Date] Is Null")) Then
  40.         MsgBox "Please select a Starting Date    ", vbInformation, "Required Date"
  41.         StartingBoardDate.SetFocus
  42.         Exit Sub
  43.     End If
  44.  
  45.     If (Eval("[Forms]![F_PR_Status]![txb_End_Date] Is Null")) Then
  46.         MsgBox "Please select an Ending Date     ", vbInformation, "Required Date"
  47.         EndingBoardDate.SetFocus
  48.         Exit Sub
  49.     End If
  50.  
  51.  
  52. 'set variable values
  53. Set dbs = CurrentDb
  54. strQueryName = "sql_PR_date"
  55.  
  56. 'Delete old query first - we want fresh data!
  57. dbs.QueryDefs.Delete strQueryName
  58.  
  59. 'Notice below how we inserted the variable as a parameter value - Visual Basic will evaluate strMonth and insert the value for us.
  60.  
  61.  
  62. strSQL = "SELECT T_PRApprovalHistory.PR_ID, " & _
  63.                 "T_PRApprovalHistory.PR_Date, " & _
  64.                 "tblWorkflowApprovalStep.Workflow_Step_Order, " & _
  65.                 "T_PRApprovalHistory.Workflow_Step_Name, " & _
  66.                 "T_PRApprovalHistory.Workflow_Step_Date, " & _
  67.                 "Count(T_PRApprovalHistory.Workflow_Step_Date) AS CountOfWorkflow_Step_Date " & _
  68.         "FROM T_PRApprovalHistory " & _
  69.                 "INNER JOIN tblWorkflowApprovalStep ON " & _
  70.                 "T_PRApprovalHistory.Workflow_Step_Name = tblWorkflowApprovalStep.Workflow_Step_Name " & _
  71.         "GROUP BY T_PRApprovalHistory.PR_ID, " & _
  72.                 "T_PRApprovalHistory.PR_Date, " & _
  73.                 "tblWorkflowApprovalStep.Workflow_Step_Order, " & _
  74.                 "T_PRApprovalHistory.Workflow_Step_Name, " & _
  75.                 "T_PRApprovalHistory.Workflow_Step_Date " & _
  76.         "HAVING (((T_PRApprovalHistory.PR_Date) " & _
  77.                 "Between [Forms]![F_PR_Status]![txb_Start_Date] And [Forms]![F_PR_Status]![txb_End_Date])) " & _
  78.         "ORDER BY T_PRApprovalHistory.PR_ID, " & _
  79.                 "tblWorkflowApprovalStep.Workflow_Step_Order, " & _
  80.                 "T_PRApprovalHistory.Workflow_Step_Date;"
  81.  
  82. 'Create query definition
  83. Set qryDef = dbs.CreateQueryDef(strQueryName, strSQL)
  84.  
  85.  
  86. Q_PR_date_Exit:
  87.   Exit Sub
  88.  
  89.  
  90. Err_Hndlr:
  91.     MsgBox "[" & Err.Number & "]:  " & Err.Description, vbInformation, "Q_PR_date()"
  92. End Sub
  93.  
Mar 24 '10 #1

✓ answered by ADezii

Copy and Paste this code exactly, and see what happens:
Expand|Select|Wrap|Line Numbers
  1. Dim dbs As dao.Database
  2. Dim rst As dao.Recordset
  3. Dim strSQL As String
  4.  
  5. 'set variable values
  6. Set dbs = CurrentDb
  7.  
  8. strSQL = "SELECT T_PRApprovalHistory.PR_ID, " & _
  9.                 "T_PRApprovalHistory.PR_Date, " & _
  10.                 "tblWorkflowApprovalStep.Workflow_Step_Order, " & _
  11.                 "T_PRApprovalHistory.Workflow_Step_Name, " & _
  12.                 "T_PRApprovalHistory.Workflow_Step_Date, " & _
  13.                 "Count(T_PRApprovalHistory.Workflow_Step_Date) AS CountOfWorkflow_Step_Date " & _
  14.         "FROM T_PRApprovalHistory " & _
  15.                 "INNER JOIN tblWorkflowApprovalStep ON " & _
  16.                 "T_PRApprovalHistory.Workflow_Step_Name = tblWorkflowApprovalStep.Workflow_Step_Name " & _
  17.         "GROUP BY T_PRApprovalHistory.PR_ID, " & _
  18.                 "T_PRApprovalHistory.PR_Date, " & _
  19.                 "tblWorkflowApprovalStep.Workflow_Step_Order, " & _
  20.                 "T_PRApprovalHistory.Workflow_Step_Name, " & _
  21.                 "T_PRApprovalHistory.Workflow_Step_Date " & _
  22.         "HAVING T_PRApprovalHistory.PR_Date " & _
  23.                 "Between #" & [Forms]![F_PR_Status]![txb_Start_Date] & _
  24.                 "# And #" & [Forms]![F_PR_Status]![txb_End_Date] & "# " & _
  25.         "ORDER BY T_PRApprovalHistory.PR_ID, " & _
  26.                 "tblWorkflowApprovalStep.Workflow_Step_Order, " & _
  27.                 "T_PRApprovalHistory.Workflow_Step_Date;"
  28.  
  29.   Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

Share this Question
Share on Google+
17 Replies


NeoPa
Expert Mod 15k+
P: 31,769
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.
Mar 24 '10 #2

ADezii
Expert 5K+
P: 8,703
Try modifying the HAVING Clause, namely:
Expand|Select|Wrap|Line Numbers
  1. "HAVING (((T_PRApprovalHistory.PR_Date) " & _
  2.                 "Between #" & [Forms]![F_PR_Status]![txb_Start_Date]" & "# And #" & _
  3.                 [Forms]![F_PR_Status]![txb_End_Date] & #)) " & _
Mar 24 '10 #3

NeoPa
Expert Mod 15k+
P: 31,769
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.
Mar 24 '10 #4

100+
P: 133
Receiving a syntax error on # added..

Expand|Select|Wrap|Line Numbers
  1.             ("SELECT T_PRApprovalHistory.PR_ID, " & _
  2.             "T_PRApprovalHistory.PR_Date, " & _
  3.             "tblWorkflowApprovalStep.Workflow_Step_Order, " & _
  4.             "T_PRApprovalHistory.Workflow_Step_Name, " & _
  5.             "T_PRApprovalHistory.Workflow_Step_Date, " & _
  6.             "Count(T_PRApprovalHistory.Workflow_Step_Date) AS CountOfWorkflow_Step_Date " & _
  7.             "FROM T_PRApprovalHistory INNER JOIN tblWorkflowApprovalStep ON " & _
  8.             "T_PRApprovalHistory.Workflow_Step_Name = tblWorkflowApprovalStep.Workflow_Step_Name " & _
  9.             "GROUP BY T_PRApprovalHistory.PR_ID, T_PRApprovalHistory.PR_Date, tblWorkflowApprovalStep.Workflow_Step_Order, " & _
  10.             "T_PRApprovalHistory.Workflow_Step_Name, T_PRApprovalHistory.Workflow_Step_Date " & _
  11.             "HAVING (((T_PRApprovalHistory.PR_Date) " & _
  12.                 "Between #" & [Forms]![F_PR_Status]![txb_Start_Date]" & "# And #"[Forms]![F_PR_Status]![txb_End_Date] & #)) " & _
  13.             "ORDER BY T_PRApprovalHistory.PR_ID, tblWorkflowApprovalStep.Workflow_Step_Order, T_PRApprovalHistory.Workflow_Step_Date;")
Mar 24 '10 #5

100+
P: 133
The code needs to go as follows:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Insert_Temp_Table()
  2.  
  3. On Error GoTo Err_Hndlr
  4. 'Delete temporary table
  5. DoCmd.RunSQL "DROP TABLE tblTemp;"
  6.  
  7. 'Create temporary table
  8. 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)")
  9.  
  10. 'Bind rstTemp to the temporary table
  11. Set rstTemp = CurrentDb.OpenRecordset("tblTemp")
  12.  
  13.  
  14. 'Grab the data and put it in rst
  15.  
  16.  
  17.  
  18. Set rst = CurrentDb.OpenRecordset _
  19.             ("SELECT T_PRApprovalHistory.PR_ID, " & _
  20.             "T_PRApprovalHistory.PR_Date, " & _
  21.             "tblWorkflowApprovalStep.Workflow_Step_Order, " & _
  22.             "T_PRApprovalHistory.Workflow_Step_Name, " & _
  23.             "T_PRApprovalHistory.Workflow_Step_Date, " & _
  24.             "Count(T_PRApprovalHistory.Workflow_Step_Date) AS CountOfWorkflow_Step_Date " & _
  25.             "FROM T_PRApprovalHistory INNER JOIN tblWorkflowApprovalStep ON " & _
  26.             "T_PRApprovalHistory.Workflow_Step_Name = tblWorkflowApprovalStep.Workflow_Step_Name " & _
  27.             "GROUP BY T_PRApprovalHistory.PR_ID, T_PRApprovalHistory.PR_Date, tblWorkflowApprovalStep.Workflow_Step_Order, " & _
  28.             "T_PRApprovalHistory.Workflow_Step_Name, T_PRApprovalHistory.Workflow_Step_Date " & _
  29.             "HAVING (((T_PRApprovalHistory.PR_Date) Between [Forms]![F_PR_Status]![txb_Start_Date] And [Forms]![F_PR_Status]![txb_End_Date])) " & _
  30.             "ORDER BY T_PRApprovalHistory.PR_ID, tblWorkflowApprovalStep.Workflow_Step_Order, T_PRApprovalHistory.Workflow_Step_Date;")
Mar 24 '10 #6

ADezii
Expert 5K+
P: 8,703
Your HAVING Clause is NOT what I suggested.
Mar 24 '10 #7

NeoPa
Expert Mod 15k+
P: 31,769
You seem to have ignored my comment too. Maybe you're just not interested in portable code ;)
Mar 25 '10 #8

100+
P: 133
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.
Mar 31 '10 #9

100+
P: 133
when the # were added to the code an error was generated:

Expand|Select|Wrap|Line Numbers
  1. HAVING (((T_PRApprovalHistory.PR_Date) " & _ 
  2.                 "Between #" & [Forms]![F_PR_Status]![txb_Start_Date]" & "# And #" & _ 
  3.                 [Forms]![F_PR_Status]![txb_End_Date] & #)) " & _ 
  4.  
Mar 31 '10 #10

ADezii
Expert 5K+
P: 8,703
What exactly was the Error displayed?
Mar 31 '10 #11

100+
P: 133
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?

Expand|Select|Wrap|Line Numbers
  1. Dim dbs As Database
  2. Dim strSQL As String
  3. Dim strQueryName As String
  4.  
  5.  
  6. 'set variable values
  7. Set dbs = CurrentDb
  8. strQueryName = "sql_PR_date"
  9.  
  10. 'Delete old query first - we want fresh data!
  11. 'dbs.QueryDefs.Delete strQueryName
  12.  
  13. strSQL = "SELECT T_PRApprovalHistory.PR_ID, " & _
  14.                 "T_PRApprovalHistory.PR_Date, " & _
  15.                 "tblWorkflowApprovalStep.Workflow_Step_Order, " & _
  16.                 "T_PRApprovalHistory.Workflow_Step_Name, " & _
  17.                 "T_PRApprovalHistory.Workflow_Step_Date, " & _
  18.                 "Count(T_PRApprovalHistory.Workflow_Step_Date) AS CountOfWorkflow_Step_Date " & _
  19.         "FROM T_PRApprovalHistory " & _
  20.                 "INNER JOIN tblWorkflowApprovalStep ON " & _
  21.                 "T_PRApprovalHistory.Workflow_Step_Name = tblWorkflowApprovalStep.Workflow_Step_Name " & _
  22.         "GROUP BY T_PRApprovalHistory.PR_ID, " & _
  23.                 "T_PRApprovalHistory.PR_Date, " & _
  24.                 "tblWorkflowApprovalStep.Workflow_Step_Order, " & _
  25.                 "T_PRApprovalHistory.Workflow_Step_Name, " & _
  26.                 "T_PRApprovalHistory.Workflow_Step_Date " & _
  27.         "HAVING (((T_PRApprovalHistory.PR_Date) " & _
  28.                 "Between [Forms]![F_PR_Status]![txb_Start_Date] And [Forms]![F_PR_Status]![txb_End_Date])) " & _
  29.         "ORDER BY T_PRApprovalHistory.PR_ID, " & _
  30.                 "tblWorkflowApprovalStep.Workflow_Step_Order, " & _
  31.                 "T_PRApprovalHistory.Workflow_Step_Date;"
  32.  
  33.  
  34. Set rst = CurrentDb.OpenRecordset(strQueryName, strSQL)
  35.  
  36.  
Mar 31 '10 #12

ADezii
Expert 5K+
P: 8,703
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.
Mar 31 '10 #13

100+
P: 133
when define recordset as follows, it generates "Too few parameters. Expected 2."

Expand|Select|Wrap|Line Numbers
  1.  
  2.  
  3. Set rst = CurrentDb.OpenRecordset("SELECT T_PRApprovalHistory.PR_ID, " & _
  4.                 "T_PRApprovalHistory.PR_Date, " & _
  5.                 "tblWorkflowApprovalStep.Workflow_Step_Order, " & _
  6.                 "T_PRApprovalHistory.Workflow_Step_Name, " & _
  7.                 "T_PRApprovalHistory.Workflow_Step_Date, " & _
  8.                 "Count(T_PRApprovalHistory.Workflow_Step_Date) AS CountOfWorkflow_Step_Date " & _
  9.         "FROM T_PRApprovalHistory " & _
  10.                 "INNER JOIN tblWorkflowApprovalStep ON " & _
  11.                 "T_PRApprovalHistory.Workflow_Step_Name = tblWorkflowApprovalStep.Workflow_Step_Name " & _
  12.         "GROUP BY T_PRApprovalHistory.PR_ID, " & _
  13.                 "T_PRApprovalHistory.PR_Date, " & _
  14.                 "tblWorkflowApprovalStep.Workflow_Step_Order, " & _
  15.                 "T_PRApprovalHistory.Workflow_Step_Name, " & _
  16.                 "T_PRApprovalHistory.Workflow_Step_Date " & _
  17.         "HAVING (((T_PRApprovalHistory.PR_Date) " & _
  18.                 "Between [Forms]![F_PR_Status]![txb_Start_Date] And [Forms]![F_PR_Status]![txb_End_Date])) " & _
  19.         "ORDER BY T_PRApprovalHistory.PR_ID, " & _
  20.                 "tblWorkflowApprovalStep.Workflow_Step_Order, " & _
  21.                 "T_PRApprovalHistory.Workflow_Step_Date;")
  22.  
  23.  
if i leave out the following statement it runs, but without the date parameters"

Expand|Select|Wrap|Line Numbers
  1.  
  2. HAVING (((T_PRApprovalHistory.PR_Date) " & _
  3.                 "Between [Forms]![F_PR_Status]![txb_Start_Date] And [Forms]![F_PR_Status]![txb_End_Date])) "
  4.  
  5.  
Mar 31 '10 #14

ADezii
Expert 5K+
P: 8,703
Copy and Paste this code exactly, and see what happens:
Expand|Select|Wrap|Line Numbers
  1. Dim dbs As dao.Database
  2. Dim rst As dao.Recordset
  3. Dim strSQL As String
  4.  
  5. 'set variable values
  6. Set dbs = CurrentDb
  7.  
  8. strSQL = "SELECT T_PRApprovalHistory.PR_ID, " & _
  9.                 "T_PRApprovalHistory.PR_Date, " & _
  10.                 "tblWorkflowApprovalStep.Workflow_Step_Order, " & _
  11.                 "T_PRApprovalHistory.Workflow_Step_Name, " & _
  12.                 "T_PRApprovalHistory.Workflow_Step_Date, " & _
  13.                 "Count(T_PRApprovalHistory.Workflow_Step_Date) AS CountOfWorkflow_Step_Date " & _
  14.         "FROM T_PRApprovalHistory " & _
  15.                 "INNER JOIN tblWorkflowApprovalStep ON " & _
  16.                 "T_PRApprovalHistory.Workflow_Step_Name = tblWorkflowApprovalStep.Workflow_Step_Name " & _
  17.         "GROUP BY T_PRApprovalHistory.PR_ID, " & _
  18.                 "T_PRApprovalHistory.PR_Date, " & _
  19.                 "tblWorkflowApprovalStep.Workflow_Step_Order, " & _
  20.                 "T_PRApprovalHistory.Workflow_Step_Name, " & _
  21.                 "T_PRApprovalHistory.Workflow_Step_Date " & _
  22.         "HAVING T_PRApprovalHistory.PR_Date " & _
  23.                 "Between #" & [Forms]![F_PR_Status]![txb_Start_Date] & _
  24.                 "# And #" & [Forms]![F_PR_Status]![txb_End_Date] & "# " & _
  25.         "ORDER BY T_PRApprovalHistory.PR_ID, " & _
  26.                 "tblWorkflowApprovalStep.Workflow_Step_Order, " & _
  27.                 "T_PRApprovalHistory.Workflow_Step_Date;"
  28.  
  29.   Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
Mar 31 '10 #15

100+
P: 133
Thank you...it works!
Mar 31 '10 #16

ADezii
Expert 5K+
P: 8,703
We were bound to get it sooner or later! (LOL)
Mar 31 '10 #17

NeoPa
Expert Mod 15k+
P: 31,769
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.
Apr 8 '10 #18

Post your reply

Sign in to post your reply or Sign up for a free account.