Filter date field and include null

[auntielo]

Hi again,

I have 2 combo boxes, one has the fields and the other has date options. So cbo1 has values like Closed Date, End Date, Start Date, Length and cbo2 has This Week, Next Week, 120 Days+, etc.

I have tried various formulas that have worked in other objects, but because this one has dates, nothing has worked. So now I am trying to nest it, but I keep getting invalid use of null!!!! There won't be many results with no date, so I always want to include the null fields when I filter.

Any thoughts on how to do that? I tried playing with OR IS NULL, but no luck.

I apologize if this is all sorts of ugly...

Private Sub cmdfilter_Click()


Dim strDate As Date
Dim strField As String

Select Case Me.cboReportField.Value

Case "Closed Date"
strField = "Closed Date"

Select Case Me.Filterby.Value

Case "This Month"
strDate = Year([Closed Date]) = Year(Now()) And Month([Closed Date]) = Month(Now()) & " "


Case "This Quarter"
strDate = Year([Closed Date]) = Year(Now()) And DatePart("q", [Closed Date]) = DatePart("q", Now())

Case "This Year"

strDate = Year([Closed Date]) = Year(Date)

End Select

Case "End Date"
strField = "End Date"

    Select Case Me.Filterby.Value

Case "This Month"
strDate = Year([End Date]) = Year(Now()) And Month([End Date]) = Month(Now())

Case "This Quarter"
strDate = Year([End Date]) = Year(Now()) And DatePart("q", [End Date]) = DatePart("q", Now())

Case "This Year"

strDate = Year([End Date]) = Year(Date)

Case "This Week"
strDate = DatePart("ww", [End Date]) = DatePart("ww", Date) And Year([End Date]) = Year(Date)

Case "Next Week"
strDate = Year([End Date]) * 53 + DatePart("ww", [End Date]) = Year(Date) * 53 + DatePart("ww", Date) + 1

Case "Last Month"
strDate = Year([End Date]) * 12 + DatePart("m", [End Date]) = Year(Date) * 12 + DatePart("m", Date) - 1

End Select
    
Case "Start Date"
strField = "Start Date"
Select Case Me.Filterby.Value

Case "This Month"
strDate = Year([Start Date]) = Year(Now()) And Month([Start Date]) = Month(Now())

Case "This Quarter"
strDate = Year([Start Date]) = Year(Now()) And DatePart("q", [Start Date]) = DatePart("q", Now())

Case "This Year"

strDate = Year([Start Date]) = Year(Date)

Case "Last Month"
strDate = Year([Start Date]) * 12 + DatePart("m", [Start Date]) = Year(Date) * 12 + DatePart("m", Date) - 1

End Select

Case "Trial Length"
strField = "Trial Length"
Select Case Me.Filterby.Value
Case "120 Days+"
strDate = "[End Date] < [Start Date] + 120"
Case "30 Days+"
strDate = "[End Date] < [Start Date] + 30"
End Select

End Select

If Me.cboReportField.Value = strField Then
DoCmd.ApplyFilter , strDate


End If
End Sub

 

[June7]

Use an If IsDate(fieldname) Then that checks for date value and runs the filter code only if a date value is present.

 

[auntielo]

Thanks June7, (that's my birthday!)

Where do I put it?

I tried
If IsDate(strField) Then
before the select case - trying to cheat and not put in all the fields

I got an error so I added another End If at the bottom. But now when I use the filter, nothing happens. So I bet that is wrong

 

[Cronk]

A good tip is to create a query with the particular criteria in mind eg this month, and then look at the SQL that Access has generated. That will show how your criteria string should be.

It will be something like
strDate = "Year([Closed Date]) = " & Year(Now() & " And Month([Closed Date]) = " & Month(Now()

Actually a neater way is
strDate = "format([Closed Date],'yyyymm') = " & format(date(),"yyyymm")

Work on the situations where there is an entry first, and then tackle the null situation later.

Incidentally, any reason for having a space in the field name so you need to put square brackets around it? (I hate typing)

 

[June7]

I would name the variable dteDate instead of strDate.

I must be missing something. Where do the [End Date] and [Start Date] values come from? Are these unbound controls used to input parameters? If user does not provide valid date for either, then why would you expect the filter to accomplish something? What logic do you want to follow? Write out a flow chart if you have to. Then build the code to implement the logic.