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
- Private Sub cmdRunExtract_click()
- On Error GoTo Err_cmdRunExtract_click
- Dim db As Database
- Dim rstSIMMISLY As Recordset
- Dim qdef As QueryDef
- Dim i As Integer
- Dim strSQL As String
- Dim strWhere As String
- Dim strIN As String
- Dim flgSelectAll As Boolean
- Dim varItem As Variant
- Dim strDateField As String
- Dim strWhereDate As String
- Const strcJetDate = "\#mm\/dd\/yyyy\#" 'do not change it to much your local settings
- Set db = CurrentDb
- Set rstSIMMISLY = CurrentDb.OpenRecordset("SLY")
- strSQL = "select * from SLY"
- strDateField = "[TransDate]" 'date field from table SIMMISLY
- 'Build the filter string
- If IsDate(Me.txtStartDate) Then
- strWhereDate = "(" & strDateField & ">= " & Format(Me.txtStartDate, strcJetDate) & ")"
- End If
- If IsDate(Me.txtEndDate) Then
- If strWhereDate <> vbNullString Then
- strWhereDate = strWhereDate & " and "
- End If
- strWhereDate = strWhereDate & "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
- End If
- 'build the IN string by looping through the list
- For i = 0 To lstType.ListCount - 1
- If lstType.Selected(i) Then
- If lstType.Column(0, i) = "All" Then
- flgSelectAll = True
- End If
- strIN = strIN & "'" & lstType.Column(0, i) & "',"
- End If
- Next i
- 'Create the WHERE string, and strip off the last comma of the IN string
- strWhere = " where [Type] in " & "(" & Left(strIN, Len(strIN) - 1) & ")"
- 'build the In string by looping the list
- For i = 0 To lstMachine.ListCount - 1
- If lstMachine.Selected(i) Then
- If lstMachine.Column(0, i) = "All" Then
- flgSelectAll = True
- End If
- strIN = strIN & "'" & lstMachine.Column(0, i) & "',"
- End If
- Next i
- 'Create the Where string, and strip off the last comma of the IN string
- strWhere = " where [Machine] in " & "(" & Left(strIN, Len(strIN) - 1) & ")"
- If strWhereDate <> vbNullString Then
- strWhere = strWhere & " AND " & strWhereDate
- End If
- 'if ALL was selected in the listbox, don't add the WHERE condition
- If Not flgSelectAll Then
- strSQL = strSQL & strWhere
- End If
- db.QueryDefs.Delete "qrySLY"
- Set qdef = db.CreateQueryDef("qrySLY", strSQL)
- 'Open the query, built using the In clause to set the criteria
- DoCmd.OpenQuery "qrySLY", acViewNormal
- 'Clear listbox selection after running query
- For Each varItem In Me.lstType.ItemsSelected
- Me.lstType.Selected(varItem) = False
- Next varItem
- If Len(Me.lstType.ItemData(varItem)) Then
- Call noselection("Work")
- Exit Sub
- Else
- Me.lstType = Left((Me.lstType), Len(Me.lstType) - 1)
- End If
- 'Clear listbox selection after running query
- For Each varItem In Me.lstMachine.ItemsSelected
- Me.lstMachine.Selected(varItem) = False
- Next varItem
- If Len(Me.lstMachine.ItemData(varItem)) Then
- Call noselection("Machine")
- Exit Sub
- Else
- Me.lstMachine = Left((Me.lstMachine), Len(Me.lstMachine) - 1)
- End If
- exit_cmdRunExtract_click:
- Exit Sub
- Err_cmdRunExtract_click:
- If Err.Number = 5 Then
- MsgBox "You must make a selection(s) from the list", , "Selection Required!"
- Resume exit_cmdRunExtract_click
- Else
- 'Write out the error and exit the sub
- MsgBox Err.Description
- Resume exit_cmdRunExtract_click
- End If
- End If
- Me.lstMachine = ""
- Me.lstType = ""
- Me.txtStartDate = ""
- Me.txtEndDate = ""
- End Sub
- Private Sub noselection(LType As String)
- Dim Msg As String
- Select Case LType
- Case Is = "Machine"
- Msg = "No Machine"
- Case Is = "Work"
- Msg = "No Work Order Type"
- Case Else
- Msg = "Error: Do not know list box type :"
- End Select
- Msg = Msg & " has been selected" & vbCrLf
- MsgBox Msg
- End Sub