Filter Specific Date in Access.

L

laheyc

Joined
Apr 6, 2010
Messages
2
Reaction score
0
I'm looking for help on filtering by a specific date. I have a form with several fields where the users input the data. At the bottom of the form I have unbound text boxes where the user can filter out for different criteria. IThey can sort by things like entry date(between two dates), part number, work order, cycle time and date unloaded. everything works except the date unloaded and I cannot figure out why. I want the filter to return only those records who criteria meets what is placed in the unbound text box. Here is the code I have so far that is triggered by a button click.

Unloaded = field that contains the differnet dates.
txtunload = the field I use to type a specific date in, in hopes of returning only those records who match that date.

Any help will be appreciated.

Chris

Private Sub Command150_Click()
Dim strForm As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.
Const conDateFormat = "\#mm\/dd\/yyyy\#"
strForm = "InfoInput"
strField = "Data_Entry"

If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
strWhere = strField & " < " & Format(Me.txtEndDate, conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then 'Start date, but no End.
strWhere = strField & " > " & Format(Me.txtStartDate, conDateFormat)
Else 'Both start and end dates.
strWhere = strField & " Between " & Format(Me.txtStartDate, conDateFormat) _
& " And " & Format(Me.txtEndDate, conDateFormat)
End If
End If

If Not IsNull(Me.[txtPartNum]) Then
If strWhere <> "" Then
strWhere = "(" & strWhere & ") AND (PartNumber = """ & Me.[txtPartNum] & """)"
Else
strWhere = "PartNumber = """ & Me.[txtPartNum] & """"
End If
End If

If Not IsNull(Me.[txtWrkOrder]) Then
If strWhere <> "" Then
strWhere = "(" & strWhere & ") AND (WorkOrderNumber = """ & Me.[txtWrkOrder] & """)"
Else
strWhere = "WorkOrderNumber = """ & Me.[txtWrkOrder] & """"
End If
End If

If Not IsNull(Me.[txtPan]) Then
If strWhere <> "" Then
strWhere = "(" & strWhere & ") AND (PanNumber = """ & Me.[txtPan] & """)"
Else
strWhere = "PanNumber = """ & Me.[txtPan] & """"
End If
End If

If Not IsNull(Me.[txtMachine]) Then
If strWhere <> "" Then
strWhere = "(" & strWhere & ") AND (MachineNumber = """ & Me.[txtMachine] & """)"
Else
strWhere = "MachineNumber = """ & Me.[txtMachine] & """"
End If
End If

If Not IsNull(Me.[txtunload]) Then
If strWhere <> "" Then
strWhere = "(" & strWhere & ") AND (Unloaded = """ & Format(txtunload, "\#mm\/dd\/yyyy\#") & """)"
Else
strWhere = "Unloaded = """ & Format(txtunload, "\#mm\/dd\/yyyy\#") & """"
End If
End If

If Not IsNull(Me.[txtCycleTime]) Then
If strWhere <> "" Then
strWhere = "(" & strWhere & ") AND (CycleTime = """ & Me.[txtCycleTime] & """)"
Else
strWhere = "CycleTime = """ & Me.[txtCycleTime] & """"
End If
End If
If strWhere <> "" Then
Me.Filter = strWhere
Me.FilterOn = True

Else
Me.FilterOn = False
End If

If [CurrentRecord] = 0 Then
DoCmd.RunCommand acCmdRemoveFilterSort
MsgBox "No records were found within your criteria."
End If

End Sub
 
Back
Top