473,320 Members | 1,939 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Two listboxes with multiselection and Date Range Query

Midzie
25
Sample Records from table SLY
TransDate Machine Person In Charge Type
Feb. 7, 2008 BBAS Ashley B
Feb. 8, 2008 BHAL Janelle C
Feb. 9, 2008 ASCR Ness A
Feb. 10, 2008 PLYR Jennifer D
Feb. 11, 2008 KLMA April E

Hi all, if anyone could help me please. I have here a form with a txtStartDate, txtEndDate, lstMachine – bound to SLY.Machine with union select “ALL” and lstType – bound to SLY.Type with union select “ALL”. I need to query with the following conditions.
1. If I set txtStartDate = Feb. 7, 2008 and Feb. 9, 2008 and I multi-selected Machine BBAS, BHAL and PLYR and selected Type B, C, A

Result must be: Feb. 7, 2008 BBAS Ashley B
Feb. 8, 2008 BHAL Janelle C
2. If I set txtStartDate = Feb. 7, 2008 and Feb. 9, 2008 and I select “ALL” from Machine and selected Type B, A
Result must be: Feb. 7, 2008 BBAS Ashley B
Feb.9, 2008 ASCR Ness A
3. If I set txtStartDate = Feb. 7, 2008 and Feb. 10, 2008 and I selected “ALL” from Machine and selected “ALL” from Type
Result must be: Feb. 7, 2008 BBAS Ashley B
Feb. 8, 2008 BHAL Janelle C
Feb. 9, 2008 ASCR Ness A
Feb. 10, 2008 PLYR Jennifer D

In short, all the criteria are dependent from each other. Please help me fix my codes. Thanks.



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 rstSIMMISLY As Recordset
  6. Dim qdef As QueryDef
  7. Dim i As Integer
  8. Dim strSQL As String
  9. Dim strWhere As String
  10. Dim strIN As String
  11. Dim flgSelectAll As Boolean
  12. Dim varItem As Variant
  13. Dim strDateField As String
  14. Dim strWhereDate As String
  15. Const strcJetDate = "\#mm\/dd\/yyyy\#" 'do not change it to much your local settings
  16.  
  17.  
  18. Set db = CurrentDb
  19. Set rstSIMMISLY = CurrentDb.OpenRecordset("SLY")
  20.  
  21.     strSQL = "select * from SLY"
  22.     strDateField = "[TransDate]" 'date field from table SIMMISLY
  23.  
  24. 'Build the filter string
  25.     If IsDate(Me.txtStartDate) Then
  26.         strWhereDate = "(" & strDateField & ">= " & Format(Me.txtStartDate, strcJetDate) & ")"
  27.     End If
  28.  
  29.     If IsDate(Me.txtEndDate) Then
  30.         If strWhereDate <> vbNullString Then
  31.             strWhereDate = strWhereDate & " and "
  32.         End If
  33.             strWhereDate = strWhereDate & "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
  34.     End If
  35.  
  36. 'build the IN string by looping through the list
  37.     For i = 0 To lstType.ListCount - 1
  38.         If lstType.Selected(i) Then
  39.             If lstType.Column(0, i) = "All" Then
  40.                 flgSelectAll = True
  41.             End If
  42.                 strIN = strIN & "'" & lstType.Column(0, i) & "',"
  43.         End If
  44.     Next i
  45.  
  46. 'Create the WHERE string, and strip off the last comma of the IN string
  47.     strWhere = " where [Type] in " & "(" & Left(strIN, Len(strIN) - 1) & ")"
  48.  
  49. 'build the In string by looping the list
  50.     For i = 0 To lstMachine.ListCount - 1
  51.         If lstMachine.Selected(i) Then
  52.             If lstMachine.Column(0, i) = "All" Then
  53.                 flgSelectAll = True
  54.             End If
  55.                 strIN = strIN & "'" & lstMachine.Column(0, i) & "',"
  56.         End If
  57.     Next i
  58.  
  59. 'Create the Where string, and strip off the last comma of the IN string
  60.     strWhere = " where [Machine] in " & "(" & Left(strIN, Len(strIN) - 1) & ")"
  61.  
  62.     If strWhereDate <> vbNullString Then
  63.         strWhere = strWhere & " AND " & strWhereDate
  64.     End If
  65.  
  66. 'if ALL was selected in the listbox, don't add the WHERE condition
  67.     If Not flgSelectAll Then
  68.         strSQL = strSQL & strWhere
  69.     End If
  70.  
  71.     db.QueryDefs.Delete "qrySLY"
  72.     Set qdef = db.CreateQueryDef("qrySLY", strSQL)
  73.  
  74. 'Open the query, built using the In clause to set the criteria
  75.     DoCmd.OpenQuery "qrySLY", acViewNormal
  76.  
  77. 'Clear listbox selection after running query
  78.     For Each varItem In Me.lstType.ItemsSelected
  79.         Me.lstType.Selected(varItem) = False
  80.     Next varItem
  81.  
  82.     If Len(Me.lstType.ItemData(varItem)) Then
  83.         Call noselection("Work")
  84.         Exit Sub
  85.     Else
  86.         Me.lstType = Left((Me.lstType), Len(Me.lstType) - 1)
  87.     End If
  88.  
  89. 'Clear listbox selection after running query
  90.     For Each varItem In Me.lstMachine.ItemsSelected
  91.         Me.lstMachine.Selected(varItem) = False
  92.     Next varItem
  93.  
  94.     If Len(Me.lstMachine.ItemData(varItem)) Then
  95.         Call noselection("Machine")
  96.         Exit Sub
  97.     Else
  98.         Me.lstMachine = Left((Me.lstMachine), Len(Me.lstMachine) - 1)
  99.     End If
  100.  
  101. exit_cmdRunExtract_click:
  102. Exit Sub
  103.  
  104. Err_cmdRunExtract_click:
  105.     If Err.Number = 5 Then
  106.         MsgBox "You must make a selection(s) from the list", , "Selection Required!"
  107.         Resume exit_cmdRunExtract_click
  108.     Else
  109. 'Write out the error and exit the sub
  110.         MsgBox Err.Description
  111.         Resume exit_cmdRunExtract_click
  112.     End If
  113. End If
  114.     Me.lstMachine = ""
  115.     Me.lstType = ""
  116.     Me.txtStartDate = ""
  117.     Me.txtEndDate = ""
  118. End Sub
  119.  
  120. Private Sub noselection(LType As String)
  121.         Dim Msg As String
  122.         Select Case LType
  123.         Case Is = "Machine"
  124.             Msg = "No Machine"
  125.         Case Is = "Work"
  126.             Msg = "No Work Order Type"
  127.         Case Else
  128.             Msg = "Error: Do not know list box type :"
  129.         End Select
  130.         Msg = Msg & " has been selected" & vbCrLf
  131.         MsgBox Msg
  132. End Sub
Feb 10 '12 #1
3 1840
patjones
931 Expert 512MB
Is there a particular point where your code stops working?

Pat
Feb 12 '12 #2
Midzie
25
I think on my dates condition and ALL values of the two listboxes. When I select All it will display all the records regardless of the dates I set. Do I need to change my conditions?
Feb 13 '12 #3
patjones
931 Expert 512MB
Good morning,

Sorry for my late reply. Is it the case then that the filters for list and the machine type do work when you select something other than "ALL"? For the dates, Access needs to have them enclosed in "#" signs. Additionally, you can collapse the entire date code down to one line:

Expand|Select|Wrap|Line Numbers
  1. strWhereDate = "([TransDate] BETWEEN #" & Me.txtStartDate & "# AND #" & Me.txtEndDate & "#)"

This is much simpler, but it is based upon a couple of assumptions. First, if the text boxes txtStartDate and txtEndDate are formatted as date types and have an appropriate input mask, the IsDate() check shouldn't be necessary. Second, in order for this to work, there needs to be a value in both the start and end date boxes. If there isn't, the operation will likely fail. If you want to make this such that it can handle nulls in both boxes, or a non-null start date with a null end date, then we need to modify this line of code slightly.

Please give it a try and let me know how it turns out.

Pat
Feb 15 '12 #4

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

Similar topics

1
by: isetea | last post by:
Hi, I want to create a from where user can select from a date range / type in a date range to get only data from an underlying query within this range. This should overwrite the existing criteria...
3
by: Don | last post by:
Hi, I would like my users to be able to enter STARTDATE and leave the ENDDATE open in the query by form that I have built. Using some code from another group post I have this within my SQL...
1
by: mikevde | last post by:
Hi, I want to select data from a range of dates i.e over a 1 month period I've tried this: SELECT * FROM table WHERE DATE_SUB(Date(), INTERVAL 30 DAY) but the query isnt working as it is...
2
by: sixdeuce62 | last post by:
Hello, I am trying to create a query that will prompt me to enter the parameter value if beginning date and ending date. I have created everything I need in the query, but I have to manually go...
1
by: flumpuk | last post by:
Hi My job currently requires me to enter data from 300+ forms a month. The system which we used in Excel was slow , and theprevious guy had three workbooks for this job . I have created...
1
by: devyon122 | last post by:
I'm trying to allow a user to type in a beginning data and an ending date to be used in a crosstab query - Below is the query: TRANSFORM Count(imagingdb.ID) AS CountOfID TRANSFORM...
19
by: ali3n8 | last post by:
Hello I have attempted to create a date range report from a query called qrycustomerinformation. The field that contains the value of my date is called Followup. When i run a report on this it is...
20
by: kujito | last post by:
I'm working with mine production data in Access 2007. I'll try to make this make sense and keep it simple (mostly to not confuse myself further). The range of years operating varies between...
12
by: jamieboy86 | last post by:
Hi Everyone, I'm new here and to access as well, The problem I'm having right now is that I made a Query that upon the input of a date, it will search for the last purchase I made and at what...
19
by: phill86 | last post by:
Hi I am re-posting this thread because it has become very confusing and I have got some way to solving the problem so it is a slightly different question from the initial thread. here is the...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.