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

Querying Records in VBA MS Access using Date Range and multiselection in ListBox

Midzie
P: 25
Hi All, if anyone could help me with my codes. I have a form with txtStartDate, txtEndDate, with a listbox lstWorkSLY and a cmdRunExtract button. I wanted to query records filtering txtStartDate to txtEndDate based on the items selected in lstWorkSLY. Here's my code, it is not working when I set Nov. 5, 2008 as my txtStartDate and Nov. 28, 2008 as my txtEndDate still all records with DateX June 1, 2008 to January 2009 appears as my output. I don't know where to place my date range condition. Please help me! Thanks in advance.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdRunExtract_click() 
  2.  
  3. On Error GoTo Err_cmdRunExtract_click 
  4. Dim db As Database 
  5. Dim qdef As QueryDef 
  6. Dim i As Integer 
  7. Dim strSQL As String 
  8. Dim strWhere As String 
  9. Dim strIN As String 
  10. Dim flgSelectAll As Boolean 
  11. Dim varItem As Variant 
  12. Dim strDateField As String 
  13. Dim strWhereDate As String 
  14. Const strcJetDate = "\#mm\/dd\/yyyy\#" 'do not change it to much your local settings 
  15.  
  16.  
  17. Set db = CurrentDb() 
  18.  
  19. strSQL = "select * from tblSLY" 
  20. strDateField = "[DateX]" 'date field from table tblSLY 
  21.  
  22. 'Build the filter string 
  23. If IsDate(Me.txtStartDate) Then 
  24. strWhereDate = "(" & strDateField & ">= " & Format(Me.txtStartDate, strcJetDate) & ")" 
  25. End If 
  26. If IsDate(Me.txtEndDate) Then 
  27. If strWhereDate <> vbNullString Then 
  28. strWhereDate = strWhereDate & " and " 
  29. End If 
  30. strWhereDate = strWhereDate & "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")" 
  31. 'end if 
  32. 'build the IN string by looping through the list 
  33. For i = 0 To lstWorkSIMMSLY.ListCount - 1 
  34. If lstWorkSLY.Selected(i) Then 
  35. If lstWorkSLY.Column(0, i) = "All" Then 
  36. flgSelectAll = True 
  37. End If 
  38. strIN = strIN & "'" & lstWorkSLY.Column(0, i) & "'," 
  39. End If 
  40. Next i 
  41.  
  42. 'Create the WHERE string, and strip off the last comma of the IN string 
  43. strWhere = " where [Natr] in " & _ 
  44. "(" & Left(strIN, Len(strIN) - 1) & ")" 
  45.  
  46. 'if ALL was selected in the listbox, don't add the WHERE condition 
  47. If Not flgSelectAll Then 
  48. strSQL = strSQL & strWhere 
  49. End If 
  50. End If 
  51. db.QueryDefs.Delete "qrySLY" 
  52. Set qdef = db.CreateQueryDef("qrySLY", strSQL) 
  53.  
  54. 'Open the query, built using the In clause to set the criteria 
  55. DoCmd.OpenQuery "qrySLY", acViewNormal 
  56.  
  57. 'Clear listbox selection after running query 
  58. For Each varItem In Me.lstWorkSLY.ItemsSelected 
  59. Me.lstWorkSLY.Selected(varItem) = False 
  60. Next varItem 
  61.  
  62. exit_cmdRunExtract_click: 
  63. Exit Sub 
  64.  
  65. Err_cmdRunExtract_click: 
  66. If Err.Number = 5 Then 
  67. MsgBox "You must make a selection(s) from the list", , "Selection Required!" 
  68. Resume exit_cmdRunExtract_click 
  69. Else 
  70. 'Write out the error and exit the sub 
  71. MsgBox Err.Description 
  72. Resume exit_cmdRunExtract_click 
  73. End If 
  74.  
  75. End Sub
Feb 8 '12 #1

✓ answered by TheSmileyCoder

I have reviewed your code. No where do you actually add the where condition regarding the dates. It also seems you have commented out a End IF on line 31 then isn't meant to be commented out.

Try stepping through your code, and look at the values as they change, and which "way" the code runs through the IF statements. While the code is running, you can always use the Immediate Pane to check values (and even modify them) by simply writing "? strSQL" to see the value of the string variable strSQL.

I have tried to modify your code, but please don't just copy paste it, look at it, and understand the changes made. I have also added indentation to your code. It helps to make the code more readable, and illustrates the structure and buildup of for example if statements.
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdRunExtract_click()
  2.  
  3. On Error GoTo Err_cmdRunExtract_click
  4. Dim db As Database
  5. Dim qdef As QueryDef
  6. Dim i As Integer
  7. Dim strSQL As String
  8. Dim strWhere As String
  9. Dim strIN As String
  10. Dim flgSelectAll As Boolean
  11. Dim varItem As Variant
  12. Dim strDateField As String
  13. Dim strWhereDate As String
  14. Const strcJetDate = "\#mm\/dd\/yyyy\#" 'do not change it to much your local settings
  15.  
  16.  
  17. Set db = CurrentDb()
  18.  
  19. strSQL = "select * from tblSLY"
  20. strDateField = "[DateX]" 'date field from table tblSLY
  21.  
  22. 'Build the filter string
  23.     If IsDate(Me.txtStartDate) Then
  24.         strWhereDate = "(" & strDateField & ">= " & Format(Me.txtStartDate, strcJetDate) & ")"
  25.     End If
  26.  
  27.     If IsDate(Me.txtEndDate) Then
  28.         If strWhereDate <> vbNullString Then
  29.         strWhereDate = strWhereDate & " and "
  30.         End If
  31.         strWhereDate = strWhereDate & "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
  32.     End If
  33.  
  34. 'build the IN string by looping through the list
  35.     For i = 0 To lstWorkSIMMSLY.ListCount - 1
  36.         If lstWorkSLY.Selected(i) Then
  37.             If lstWorkSLY.Column(0, i) = "All" Then
  38.                 flgSelectAll = True
  39.             End If
  40.             strIN = strIN & "'" & lstWorkSLY.Column(0, i) & "',"
  41.         End If
  42.     Next i
  43.  
  44. 'Create the WHERE string, and strip off the last comma of the IN string
  45.     strWhere = " where [Natr] in " & "(" & Left(strIN, Len(strIN) - 1) & ")"
  46.  
  47. 'if ALL was selected in the listbox, don't add the WHERE condition
  48.     If Not flgSelectAll Then
  49.         strSQL = strSQL & strWhere
  50.     End If
  51.  
  52.     'Check to see if we need to add data where clause
  53.     If strWhereDate <> "" Then
  54.         'We need to add it.
  55.         If flgSelectAll Then
  56.             strSQL = strSQL & " AND " & strWhereDate
  57.             Else
  58.             strSQL = strSQL & " WHERE " & strWhereDate
  59.         End If
  60.     End If
  61.  
  62.     db.QueryDefs.Delete "qrySLY"
  63.     Set qdef = db.CreateQueryDef("qrySLY", strSQL)
  64.  
  65. 'Open the query, built using the In clause to set the criteria
  66.     DoCmd.OpenQuery "qrySLY", acViewNormal
  67.  
  68. 'Clear listbox selection after running query
  69.     For Each varItem In Me.lstWorkSLY.ItemsSelected
  70.         Me.lstWorkSLY.Selected(varItem) = False
  71.     Next varItem
  72.  
  73. exit_cmdRunExtract_click:
  74.     Exit Sub
  75.  
  76. Err_cmdRunExtract_click:
  77.     If Err.Number = 5 Then
  78.         MsgBox "You must make a selection(s) from the list", , "Selection Required!"
  79.         Resume exit_cmdRunExtract_click
  80.     Else
  81.     'Write out the error and exit the sub
  82.         MsgBox Err.Description
  83.         Resume exit_cmdRunExtract_click
  84.     End If
  85.  
  86. End Sub
  87.  
  88.  
  89.  

Share this Question
Share on Google+
6 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
I have reviewed your code. No where do you actually add the where condition regarding the dates. It also seems you have commented out a End IF on line 31 then isn't meant to be commented out.

Try stepping through your code, and look at the values as they change, and which "way" the code runs through the IF statements. While the code is running, you can always use the Immediate Pane to check values (and even modify them) by simply writing "? strSQL" to see the value of the string variable strSQL.

I have tried to modify your code, but please don't just copy paste it, look at it, and understand the changes made. I have also added indentation to your code. It helps to make the code more readable, and illustrates the structure and buildup of for example if statements.
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdRunExtract_click()
  2.  
  3. On Error GoTo Err_cmdRunExtract_click
  4. Dim db As Database
  5. Dim qdef As QueryDef
  6. Dim i As Integer
  7. Dim strSQL As String
  8. Dim strWhere As String
  9. Dim strIN As String
  10. Dim flgSelectAll As Boolean
  11. Dim varItem As Variant
  12. Dim strDateField As String
  13. Dim strWhereDate As String
  14. Const strcJetDate = "\#mm\/dd\/yyyy\#" 'do not change it to much your local settings
  15.  
  16.  
  17. Set db = CurrentDb()
  18.  
  19. strSQL = "select * from tblSLY"
  20. strDateField = "[DateX]" 'date field from table tblSLY
  21.  
  22. 'Build the filter string
  23.     If IsDate(Me.txtStartDate) Then
  24.         strWhereDate = "(" & strDateField & ">= " & Format(Me.txtStartDate, strcJetDate) & ")"
  25.     End If
  26.  
  27.     If IsDate(Me.txtEndDate) Then
  28.         If strWhereDate <> vbNullString Then
  29.         strWhereDate = strWhereDate & " and "
  30.         End If
  31.         strWhereDate = strWhereDate & "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
  32.     End If
  33.  
  34. 'build the IN string by looping through the list
  35.     For i = 0 To lstWorkSIMMSLY.ListCount - 1
  36.         If lstWorkSLY.Selected(i) Then
  37.             If lstWorkSLY.Column(0, i) = "All" Then
  38.                 flgSelectAll = True
  39.             End If
  40.             strIN = strIN & "'" & lstWorkSLY.Column(0, i) & "',"
  41.         End If
  42.     Next i
  43.  
  44. 'Create the WHERE string, and strip off the last comma of the IN string
  45.     strWhere = " where [Natr] in " & "(" & Left(strIN, Len(strIN) - 1) & ")"
  46.  
  47. 'if ALL was selected in the listbox, don't add the WHERE condition
  48.     If Not flgSelectAll Then
  49.         strSQL = strSQL & strWhere
  50.     End If
  51.  
  52.     'Check to see if we need to add data where clause
  53.     If strWhereDate <> "" Then
  54.         'We need to add it.
  55.         If flgSelectAll Then
  56.             strSQL = strSQL & " AND " & strWhereDate
  57.             Else
  58.             strSQL = strSQL & " WHERE " & strWhereDate
  59.         End If
  60.     End If
  61.  
  62.     db.QueryDefs.Delete "qrySLY"
  63.     Set qdef = db.CreateQueryDef("qrySLY", strSQL)
  64.  
  65. 'Open the query, built using the In clause to set the criteria
  66.     DoCmd.OpenQuery "qrySLY", acViewNormal
  67.  
  68. 'Clear listbox selection after running query
  69.     For Each varItem In Me.lstWorkSLY.ItemsSelected
  70.         Me.lstWorkSLY.Selected(varItem) = False
  71.     Next varItem
  72.  
  73. exit_cmdRunExtract_click:
  74.     Exit Sub
  75.  
  76. Err_cmdRunExtract_click:
  77.     If Err.Number = 5 Then
  78.         MsgBox "You must make a selection(s) from the list", , "Selection Required!"
  79.         Resume exit_cmdRunExtract_click
  80.     Else
  81.     'Write out the error and exit the sub
  82.         MsgBox Err.Description
  83.         Resume exit_cmdRunExtract_click
  84.     End If
  85.  
  86. End Sub
  87.  
  88.  
  89.  
Feb 8 '12 #2

Midzie
P: 25
Hi Smiley, I stepped into my codes and I noticed it skipped these codes and error "Item not found in this collection" appeared.

Expand|Select|Wrap|Line Numbers
  1. Set qdef = db.CreateQueryDef("qrySLY", strSQL)
  2.  
  3. 'Open the query, built using the In clause to set the criteria
  4. DoCmd.OpenQuery "qrySLY", acViewNormal
  5.  
  6. 'Clear listbox selection after running query
  7. For Each varItem In Me.lstWorkSLY.ItemsSelected
  8. Me.lstWorkSLY.Selected(varItem) = False
  9. Next varItem
  10.  
  11. exit_cmdRunExtract_click:
  12. Exit Sub
Feb 8 '12 #3

TheSmileyCoder
Expert Mod 100+
P: 2,321
Its been a while since I used the listbox control, but try:
Expand|Select|Wrap|Line Numbers
  1. For Each varItem In Me.lstWorkSLY.ItemsSelected 
  2. varItem.Selected = False 
  3. Next varItem 
Remember though, that the For Each will loop through all items in that collection. So there is no need to refernce it again inside the loop.
Feb 8 '12 #4

Midzie
P: 25
Hi Smiley, it's working already. The error "Item not found in the collection" appeared because I didn't yet create a query "qrySLY" from query design. I created it and it's working already. I've chosen your first reply as the best answer. Thanks a lot!
Feb 9 '12 #5

Midzie
P: 25
I just want to ask a follow up question, what if I add another listbox here for filtering records? Is it possible? Another is that if i select "ALL" in the listbox the filtering date range was disregarded, I mean it should find first all records within the date range before selecting "ALL" codes in the listbox. Let's say, i have records from Feb 1 t0 28, 2012 with codes from listbox A, B, C, D, E and ALL. If I set start date - Feb. 1, 2012 and end date - Feb. 14, 2012 and select ALL from listbox, It should display ALL records from Feb. 1, 2012 to Feb. 14, 2012 not ALL records of Feb. 1 to 28, 2012. Thanks in advance!
Feb 9 '12 #6

NeoPa
Expert Mod 15k+
P: 31,186
Cascaded Form Filtering explains many of the possibilities, but you have to implement your specific logic. The possibilities are various, but this gives the tools with which you can build your logic.

If what you need is more than this simple direction then you should ask it in a new thread. This one's already answered.
Feb 9 '12 #7

Post your reply

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