Working with dates in vba...

[pdx_man]

What version are your running? I am not familiar with the conDateFormat qualifier in the Format statement.

Well, let's try this:

        If Not IsNull(Me.txtToDate) Then   'End date, but no start.
            strFilter = strFilter & "[Out Date] <= #" & Format(Me.txtToDate, conDateFormat) & "#"
        End If
    Else
        If IsNull(Me.txtToDate) Then       'Start date, but no End.
            strFilter = strFilter & "[Out Date] >= #" & Format(Me.txtFromDate, conDateFormat) & "#"
        Else                                'Both start and end dates.
            strFilter = "[Out Date]" & " Between #" & Format(Me.txtFromDate, conDateFormat) & "#" _
                & " And #" & Format(Me.txtToDate, conDateFormat) & "#"
        End If

A question for you. Why are you removing the last 5 characters in strFilter? Is that related to something the conDateFormat is adding?

If strFilter <> "" Then strFilter = Left$(strFilter, Len(strFilter) - 5)

Also, set the filter before turning it on:

   Me!subJobs.Form.Filter = strFilter
   Me!subJobs.Form.FilterOn = True

 

[TomJamieson]

I'm running Access XP.

I copied the ConDateFormat code from the tutorial posted earlier in this thread; http://allenbrowne.com/casu-08.html

I remove the last 5 characters because it is a search with multiple criteria, and for each populated text box in the search form, I add to the string. So I add the criteria and " AND " on the end, for the next piece of criteria. Then, at the end, when there is no more criteria to add to the string, I remove the " AND ". Does that make sense?



Now, the problem I face using your code...

I have written MsgBox(strFilter) into the code so I can see what the filter is when I hit search. If I put in two dates the filter is: "[Out Date] Between #04/09/2006# And #30/09/2006#" That is what it should be, right? But then when it applies the filter, it errors, giving me this message: "Run-time error '3075': Syntax error in date in query expression '[Out Date] Between #04/09/2006# And #30/09/'."

Strange. Any ideas? I really, really appreciate you help so far!

 

[pdx_man]

I don't see where you are going through checking each populated text box. Maybe somewhere else. If that is true, then you need to check the last 5 characters of your string before removing the last 5.

Where are you putting the MsgBox(strFilter)? It appears the last 5 characters are being dropped.

If Right(strFilter,5) <> " AND " Then strFilter = Left$(strFilter, Len(strFilter) - 5)

 

[TomJamieson]

I see what you're saying. The checking each text box code is earlier on, I didn't put it up because it works perfectly. But I see now, I wasn't putting the AND on the end of the date part of the string. So now I have added & " AND " onto the end of the date part, and left the code to remove the last five letters in. It runs without erroring, but the dates of the records it gives are all wrong! It seems quite random.

 

[TomJamieson]

OH MY GOD. I think I may have just figured out what it is. I cannot believe this. I'm so stupid. I was playing with different date inputs trying to find a pattern for the returns it was giving me.... and I realised, the date format is MM/DD/YYYY, not DD/MM/YYYY as we use here in England.

I'm sorry.

 

[pdx_man]

Glad you got it figured out.