TomJamieson
Registered User.
- Local time
- Today, 16:29
- Joined
- Jun 8, 2006
- Messages
- 50
Hi.
I hope somebody can help me with this... I'm not a VBA expert, just self taught in things I need to know.
I have a form with several combo boxes, and a subform below. Every combo box represents a field on my main table, allowing people to search by chosen criteria. I've written code that goes through the comboboxes and adds the details in any used ones into a filter, which is applied to a table. All results then appear in the subform.
Unfortunately, I've gotten really stuck on the date field. Ideally, I want them to be able to choose two dates, so they can search all jobs that are due out between those two dates. But I don't know how to work with dates in VBA at all. I tried > and < but it didn't work. Can anybody help?
This is my code so far:
Private Sub btnSearch_Click()
Dim strFilter As String
Dim varItem As Variant
strFilter = ""
If Not IsNull(Me!cboOpen) Then strFilter = strFilter & "[Job Open]=" & Chr(34) & Me!cboOpen & Chr(34) & " AND "
If Not IsNull(Me!cboStatus) Then strFilter = strFilter & "[QC Status]=" & Chr(34) & Me!cboStatus & Chr(34) & " AND "
If Not IsNull(Me!cboRef) Then strFilter = strFilter & "[Customer]=" & Chr(34) & Me!cboRef & Chr(34) & " AND "
If Not IsNull(Me!txtDate) Then strFilter = strFilter & "[Out date]>" & Me!txtDate & " AND "
If strFilter <> "" Then strFilter = Left$(strFilter, Len(strFilter) - 5)
If strFilter = "" Then
Me!subJobs.Form.FilterOn = False
Else
Me!subJobs.Form.FilterOn = True
Me!subJobs.Form.Filter = strFilter
End If
End Sub
I hope somebody can help me with this... I'm not a VBA expert, just self taught in things I need to know.
I have a form with several combo boxes, and a subform below. Every combo box represents a field on my main table, allowing people to search by chosen criteria. I've written code that goes through the comboboxes and adds the details in any used ones into a filter, which is applied to a table. All results then appear in the subform.
Unfortunately, I've gotten really stuck on the date field. Ideally, I want them to be able to choose two dates, so they can search all jobs that are due out between those two dates. But I don't know how to work with dates in VBA at all. I tried > and < but it didn't work. Can anybody help?
This is my code so far:
Private Sub btnSearch_Click()
Dim strFilter As String
Dim varItem As Variant
strFilter = ""
If Not IsNull(Me!cboOpen) Then strFilter = strFilter & "[Job Open]=" & Chr(34) & Me!cboOpen & Chr(34) & " AND "
If Not IsNull(Me!cboStatus) Then strFilter = strFilter & "[QC Status]=" & Chr(34) & Me!cboStatus & Chr(34) & " AND "
If Not IsNull(Me!cboRef) Then strFilter = strFilter & "[Customer]=" & Chr(34) & Me!cboRef & Chr(34) & " AND "
If Not IsNull(Me!txtDate) Then strFilter = strFilter & "[Out date]>" & Me!txtDate & " AND "
If strFilter <> "" Then strFilter = Left$(strFilter, Len(strFilter) - 5)
If strFilter = "" Then
Me!subJobs.Form.FilterOn = False
Else
Me!subJobs.Form.FilterOn = True
Me!subJobs.Form.Filter = strFilter
End If
End Sub