Filtering Subform by Mainform date text boxes

[acarterczyz]

Hey all,

I'm having issues with this one.
Table name is CompDates
Subform is ViewByMarket
Start date is Text13 and End Date is Text15.

I want it to filter out any matches between the 7 fields. Here is my code:

Private Sub Command19_Click()
Dim strFilter As Variant, _
        strSDate As String, _
        strEDate As String
    
    'check Text13
    Text13.SetFocus 'set focus to Text13 to be checked
    strSDate = Forms!SearchByBuild.Text13.Text
    If Not IsDate(strSDate) Then
        MsgBox "Invalid Date Format '" & strSDate & "'." & vbNewLine & _
                    "Please enter start date as MM/DD/YYYY.", _
                vbExclamation, _
                "Invalid Date"
        GoTo ExitSub
    End If
    
    'check Text15
    Text15.SetFocus 'set focus to Text15 to be checked
    strEDate = Forms!SearchByBuild.Text15.Text
    If Not IsDate(strEDate) Then
        MsgBox "Invalid Date Format '" & strEDate & "'." & vbNewLine & _
                    "Please enter start date as MM/DD/YYYY.", _
                vbExclamation, _
                "Invalid Date"
        GoTo ExitSub
    End If
    
    'if both Text13 and Text15 are valid, run filter
    strFilter = "[Build Date 1] BETWEEN #" & Format(strSDate, "MM/DD/YYYY") & _
                "# AND #" & Format(strEDate, "MM/DD/YYYY") & "# OR [Build Date 1] BETWEEN #" & Format(strSDate, "MM/DD/YYYY") & _
                "# AND #" & Format(strEDate, "MM/DD/YYYY") & "# OR [Build Date 2] BETWEEN #" & Format(strSDate, "MM/DD/YYYY") & _
                "# AND #" & Format(strEDate, "MM/DD/YYYY") & "# OR [Build Date 3] BETWEEN #" & Format(strSDate, "MM/DD/YYYY") & _
                "# AND #" & Format(strEDate, "MM/DD/YYYY") & "# OR [Build Date 4] BETWEEN #" & Format(strSDate, "MM/DD/YYYY") & _
                "# AND #" & Format(strEDate, "MM/DD/YYYY") & "# OR [Build Date 5] BETWEEN #" & Format(strSDate, "MM/DD/YYYY") & _
                "# AND #" & Format(strEDate, "MM/DD/YYYY") & "# OR [Build Date 6] BETWEEN #" & Format(strSDate, "MM/DD/YYYY") & _
                "# AND #" & Format(strEDate, "MM/DD/YYYY") & "# OR [Build Date 7] BETWEEN #" & Format(strSDate, "MM/DD/YYYY") & _
                "# AND #" & Format(strEDate, "MM/DD/YYYY") & "#"
    Forms!SearchByBuild!ViewByMarketSubF.Form.Filter = strFilter
    Forms!SearchByBuild!ViewByMarketSubF.Form.FilterOn = True
    
ExitSub:
    
End Sub

It will only filter for Build Date 1 and not for any of the other fields.

 

[MarkK]

A couple of things I see right off the bat:
Constructing 'strFilter' you Format() each date, which returns a string. Then, in the SQL you enclose that string in "#" which converts it back into a date. Probably you don't need to bother with formatting all those values, which is mostly a readability issue.

But the main thing, having the multiple values 'BuildDate1', 'BuildDate2', ..., 'BuildDateN', in a single record is a serious design flaw, and the reason that your work is hard. In fact, those build dates exist in a One-To-Many relationship with some other entity, and you have failed to design your tables accordingly.