Hi everyone,
I wonder who can help me on the filter the Start Date and End Date. I still cannot manage to filter it.
Eg. When the user click the StartDate(combo Box as 16/7/07), the Start Date(textbox should show only 16/7/07) and this also apply to the EndDate.
Private Sub cboStartDate_Cl ick()
'Purpose: Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both inclusive. _
Start date only = all dates from this one onwards; _
End date only = all dates up to (and including this one).
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria string to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates in a JET query string.
'************** *************** *************** ***** *************** *******
'Look at each search box, and build up the criteria string from the non-blank ones.
'************** *************** *************** ***** *************** *******
'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.cboSt artDate) Then
strWhere = strWhere & "(([StartDate] = '" & Me.cboStartDate & "')) OR "
End If
'************** *************** *************** ***** *************** ******
'Chop off the trailing " AND ", and use the string as the form's Filter.
'************** *************** *************** ***** *************** *******
'See if the string has more than 5 characters (a trailng " AND ") to remove.
lngLen = Len(strWhere) - 4
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the " Or " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
Debug.Print strWhere
'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub
Private Sub cboEndDate_Clic k()
'Purpose: Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both inclusive. _
Start date only = all dates from this one onwards; _
End date only = all dates up to (and including this one).
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria string to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates in a JET query string.
'************** *************** *************** ***** *************** *******
'Look at each search box, and build up the criteria string from the non-blank ones.
'************** *************** *************** ***** *************** *******
'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.cboEn dDate) Then
strWhere = strWhere & "(([EndDate] = '" & Me.cboEndDate & "')) OR "
End If
'************** *************** *************** ***** *************** ******
'Chop off the trailing " AND ", and use the string as the form's Filter.
'************** *************** *************** ***** *************** *******
'See if the string has more than 5 characters (a trailng " AND ") to remove.
lngLen = Len(strWhere) - 4
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the " AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
Debug.Print strWhere
'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub
I wonder who can help me on the filter the Start Date and End Date. I still cannot manage to filter it.
Eg. When the user click the StartDate(combo Box as 16/7/07), the Start Date(textbox should show only 16/7/07) and this also apply to the EndDate.
Private Sub cboStartDate_Cl ick()
'Purpose: Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both inclusive. _
Start date only = all dates from this one onwards; _
End date only = all dates up to (and including this one).
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria string to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates in a JET query string.
'************** *************** *************** ***** *************** *******
'Look at each search box, and build up the criteria string from the non-blank ones.
'************** *************** *************** ***** *************** *******
'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.cboSt artDate) Then
strWhere = strWhere & "(([StartDate] = '" & Me.cboStartDate & "')) OR "
End If
'************** *************** *************** ***** *************** ******
'Chop off the trailing " AND ", and use the string as the form's Filter.
'************** *************** *************** ***** *************** *******
'See if the string has more than 5 characters (a trailng " AND ") to remove.
lngLen = Len(strWhere) - 4
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the " Or " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
Debug.Print strWhere
'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub
Private Sub cboEndDate_Clic k()
'Purpose: Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both inclusive. _
Start date only = all dates from this one onwards; _
End date only = all dates up to (and including this one).
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria string to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates in a JET query string.
'************** *************** *************** ***** *************** *******
'Look at each search box, and build up the criteria string from the non-blank ones.
'************** *************** *************** ***** *************** *******
'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.cboEn dDate) Then
strWhere = strWhere & "(([EndDate] = '" & Me.cboEndDate & "')) OR "
End If
'************** *************** *************** ***** *************** ******
'Chop off the trailing " AND ", and use the string as the form's Filter.
'************** *************** *************** ***** *************** *******
'See if the string has more than 5 characters (a trailng " AND ") to remove.
lngLen = Len(strWhere) - 4
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the " AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
Debug.Print strWhere
'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub
Comment