Sub Form Date Range Filter :(

[Sly600rr]

Hello guys,

I'd first like to thank everyone for all their informative posts, I have only been working with access for a couple days but this fourm has already been a life-saver for me.

I'm usually pretty good at figuring stuff out, backwards engineering peoples examples, but I'm having difficulty with this one, I've seen tons of 'Date ranges' using queries and reports all over this fourm but none seem to be what I'm looking for.

The Main part of my example came from Gromit. It’s a "Search Form" using unbound and combo text boxes for input queries to filter subform results.

What I want to do is be able to specify a specific date, or a range of dates to filter the results with. Like a one week span of 10/13/2008-10/17/2008.

I'm sure there's a way, I just can't seem to grasp what I need to do.
IF anyone could take a look at my example and help me out it would be much appreciated.

Private Sub btnClear_Click()
    Dim intIndex As Integer
 
    ' Clear all search items
    Me.txtSWO = ""
    Me.txtPART = ""
    Me.txtondate = ""
    Me.txtStartDate = ""
    Me.txtEndDate = ""
    Me.cmbDisposition = 0
    Me.cmbInspector = 0
    Me.cmbNonConforming = 0
 
 
End Sub
Private Sub btnSearch_Click()
 
 
    ' Update the record source
    If BuildFilter = "" Then
        Me.qrydatasub.Form.RecordSource = "SELECT * FROM qrydata " & BuildFilter
    Else
        Me.qrydatasub.Form.RecordSource = "SELECT * FROM qrydata WHERE " & BuildFilter
    End If
 
 
End Sub
 
Private Sub Command21_Click()
End Sub
Private Sub Form_Load()
 
    ' Clear the search form
    btnClear_Click
 
End Sub
Private Function BuildFilter() As Variant
    Dim varWhere As Variant
    Dim varColor As Variant
    Dim varItem As Variant
    Dim intIndex As Integer
    varWhere = Null  ' Main filter
    varColor = Null  ' Subfilter used for colors
 
    ' Check for SWO Numbers
    If Me.txtSWO > "" Then
        varWhere = varWhere & "[SWO Number] LIKE """ & Me.txtSWO & "*"" AND "
    End If
 
    ' Check for Part Number
    If Me.txtPART > "" Then
        varWhere = varWhere & "[Part Number] LIKE """ & Me.txtPART & "*"" AND "
    End If
 
       ' Check for End Date
       If Me.txtStartDate = "" And Me.txtEndDate = "" Then
    If Me.txtEndDate > "" Then
    varWhere = varWhere & "[Date] <" & Me.txtondate & " AND "
    End If
    End If
 
 
    ' Check for Start date
   If Me.txtStartDate > "" Then
     varWhere = varWhere & "[Date] >" & Me.txtStartDate & " AND "
    End If
 
    ' Check for End Date
    If Me.txtEndDate > "" Then
    varWhere = varWhere & "[Date] <" & Me.txtEndDate & " AND "
    End If
 
    ' Check for Dispostion
    If Me.cmbDisposition > 0 Then
        varWhere = varWhere & "[DispositionID] = " & Me.cmbDisposition & " AND "
    End If
 
   ' Check for Inspector
    If Me.cmbInspector > 0 Then
        varWhere = varWhere & "[InspectorID] = " & Me.cmbInspector & " AND "
    End If
 
     ' Check for Non Conforming
    If Me.cmbNonConforming > 0 Then
        varWhere = varWhere & "[NonConformingID] = " & Me.cmbNonConforming & " AND "
    End If
 
 
    ' Test to see if we have subfilter for colors...
    If IsNull(varColor) Then
        ' do nothing
    Else
        ' strip off last "OR" in the filter
        If Right(varColor, 4) = " OR " Then
            varColor = Left(varColor, Len(varColor) - 4)
        End If
 
        ' Add some parentheses around the subfilter
        varWhere = varWhere & "( " & varColor & " )"
    End If
 
 
    ' Check if there is a filter to return...
    If IsNull(varWhere) Then
        varWhere = ""
    Else
        ' strip off last "AND" in the filter
        If Right(varWhere, 5) = " AND " Then
            varWhere = Left(varWhere, Len(varWhere) - 5)
        End If
 
    End If
 
    BuildFilter = varWhere
 
 
 
End Function

I Hope its just a matter of changing the syntax around so that it knows it’s dates that I’m trying to compare, I just don’t know how to do it.
Looking at similar posts on here I Tried changing the date format to a couple different things. Like below

 ' Check for Start date
   If Me.txtStartDate > "" Then
     varWhere = varWhere & "[Date] >" "#" & Me.txtStartDate & "#"" AND "
    End If
 
    ' Check for End Date
    If Me.txtEndDate > "" Then
    varWhere = varWhere & "[Date] <" "#" & Me.txtEndDate & "#"" AND "
    End If

But all that does it stop the program from running..
Please somebody help me


This is my first database I'm making to track all the product we have going through our quality facility, I has a Main input, for our inspectors, A disposition form for our engineers to disposition any non comforming parts, and then the search form, to filter out the unwanted results.

 

[pbaldy]

I haven't looked at the sample, but try

varWhere = varWhere & "[Date] > #" & Me.txtStartDate & "# AND "

 

[Sly600rr]

Ahh!!

So much frustration over so small of a change.. I must have tried moving those # signs around 10 different ways and times..
Oh well!

It Worked!!

  ' Check for On Date
       If Me.txtondate > "" Then
    varWhere = varWhere & "[Date] =#" & Me.txtondate & "# AND "
    End If
        
    ' Check for Start date
   If Me.txtStartDate > "" Then
     varWhere = varWhere & "[Date] >=#" & Me.txtStartDate & "# AND "
    End If
    
    
    ' Check for End Date
    If Me.txtEndDate > "" Then
    varWhere = varWhere & "[Date] <=#" & Me.txtEndDate & "# AND "
    End If

Thanks Alot pbaldy, you just helped releive a real annoying headache.

 

[pbaldy]

No problem; welcome to the site by the way.

 

[Ellis Ellis]

Hi Guys
I too have had the same headache but mine still persists. Everything works but when I press my search button the form it brings up erroneous data. Any chance of looking at the code


Option Compare Database
Option Explicit

Private Sub btnClear_Click()
Dim intIndex As Integer

' Clear all search items
Me.txtPGD = ""
Me.txtItem = ""
Me.txtHV = ""
Me.txtStartDate = ""
Me.txtEndDate = ""
Me.txtsubcat = ""
Me.txtLIQN = ""
Me.txtloc = ""
Me.txtGTAmazonPrice = ""
Me.txtLTAmazonPrice = ""
Me.txtOnDate = ""


' De-select each item in Color List (multiselect list)
For intIndex = 0 To Me.txtPGAM.ListCount - 1
Me.txtPGAM.Selected(intIndex) = False
Next


End Sub

Private Sub btnSearch_Click()

' Update the record source
Me.frmsubClients.Form.RecordSource = "SELECT * FROM qryClientData " & BuildFilter

' Requery the subform
Me.frmsubClients.Requery
End Sub


Private Sub Form_Load()

' Clear the search form
btnClear_Click

End Sub

Private Function BuildFilter() As Variant
Dim varWhere As Variant
Dim varColor As Variant
Dim varItem As Variant
Dim intIndex As Integer
Dim strWhere As String


varWhere = Null ' Main filter
varColor = Null ' Subfilter used for colors


' Check for LIKE Product Group Description

If Me.txtPGD > "" Then
varWhere = varWhere & "[Product Group Description] LIKE """ & Me.txtPGD & "*"" AND "
End If

' Check for On Date
If Me.txtOnDate > "" Then
varWhere = varWhere & "[processed_on] =#" & Me.txtOnDate & "# AND "
End If

' Check for Start date
If Me.txtStartDate > "" Then
varWhere = varWhere & "[processed_on] >=#" & Me.txtStartDate & "# AND "
End If

' Check for End Date
If Me.txtEndDate > "" Then
varWhere = varWhere & "[processed_on] <=#" & Me.txtEndDate & "# AND "
End If

' Check for LIKE First Name
If Me.txtItem > "" Then
varWhere = varWhere & "[Item] LIKE """ & Me.txtItem & "*"" AND "
End If


' Check for LIKE Last Name
If Me.txtHV > "" Then
varWhere = varWhere & "[HV] LIKE """ & Me.txtHV & "*"" AND "
End If

' Check for LIKE Last Name
If Me.txtsubcat > "" Then
varWhere = varWhere & "[subcat] LIKE """ & Me.txtsubcat & "*"" AND "
End If

' Check for min Amazon Price
If Me.txtGTAmazonPrice > "" Then
varWhere = varWhere & "[AP] > " & Me.txtGTAmazonPrice & " AND "
End If

' Check for max Amazon Price
If Me.txtLTAmazonPrice > "" Then
varWhere = varWhere & "[AP] < " & Me.txtLTAmazonPrice & " AND "
End If

' Check for LIKE Last Name
If Me.txtloc > "" Then
varWhere = varWhere & "[Loc] LIKE """ & Me.txtloc & "*"" AND "
End If

' Check for LIKE Last Name
If Me.txtLIQN > "" Then
varWhere = varWhere & "[LIQN] LIKE """ & Me.txtLIQN & "*"" AND "
End If

' Check for Colors in multiselect list
For Each varItem In Me.txtPGAM.ItemsSelected
varColor = varColor & "[Product Group Description] = """ & _
Me.txtPGAM.ItemData(varItem) & """ OR "

Next






' Test to see if we have subfilter for colors...
If IsNull(varColor) Then
' do nothing
Else
' strip off last "OR" in the filter
If Right(varColor, 4) = " OR " Then
varColor = Left(varColor, Len(varColor) - 4)
End If

' Add some parentheses around the subfilter
varWhere = varWhere & "( " & varColor & " )"
End If

' Check if there is a filter to return...
If IsNull(varWhere) Then
varWhere = ""
Else
varWhere = "WHERE " & varWhere

' strip off last "AND" in the filter
If Right(varWhere, 5) = " AND " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
End If
End If

BuildFilter = varWhere

End Function

Private Sub txtPGD_Enter()

End Sub

Private Sub txtPGD_Exit(Cancel As Integer)

End Sub
------------------------------------------------------------------------
As you can see it is modfied code......not modified enough !!!!!

Thanks People !!!!!

 

[pbaldy]

Here's a handy technique to use to help debug the SQL:

http://www.baldyweb.com/ImmediateWindow.htm

If you don't spot the problem, post the SQL here.

 

[justiwhi]

pbaldy,

These filters have wildcards after the user input, but not before. I have been unsuccessful in getting the before and after wildcard to work. Might you have a suggestion?

 

[pbaldy]

What have you got that works, and what have you tried that doesn't?

 

[rodmc]

is it possible that the date ranges are in US rather than International format? I believe the man (Paul) above has some code to fix that too

 

[justiwhi]

Sorry, I figured it out, but I do appreciate your reply and offer to help. I had the * in between the wrong set of " ".

I was trying:
'Check for Sender Name
If Me.txtSrchSendName > "" Then
varWhere = varWhere & "[SendName] LIKE " * "" & Me.txtSrchSendName & "*"" And "
End If

Instead of:
'Check for Sender Name
If Me.txtSrchSendName > "" Then
varWhere = varWhere & "[SendName] LIKE ""*" & Me.txtSrchSendName & "*"" And "
End If

Still a newbie, copying, pasting, and trying to learn along the way. I should have known something was up when it added extra spaces before and after the * when I put it in the wrong spot.