Date Format on filters

[molloyjh]

Hello all,

me again! Got a very strange error today when trying to filter on a form that maybe someone has seen before.

I have a form that displays a list of outstanding work items for a user. They have the ability to filter these items based on, among other things, the date that they came in.

So I have 2 unbound text boxes for the date range itself, both set to Short Date format (UK format - dd/mm/yyyy). The date field on the table and form is the same format.

So I have the following code to implement the filter, and if I type in dates like, for example, 15th June 2009 to 26th June 2009 (in the Short Date format) it all works ok. However if I type in a single digit day (i.e. anything from the 01st to the 09th of a month) it converts the date into US format for the actual filtering. I have a msgbox set up to check the dates as I filter and it seems fine there, but the filter itself is incorrect.

        'Generate the SQL code for the filter
        DateCriteria = "[Start Date]" & " >= #" & Format(Me.FromDateFilter, "dd/mm/yyyy") & "# AND " & "[Start Date]" & " <= #" & DateAdd("d", 1, Format(Me.ToDateFilter, "dd/mm/yyyy")) & "#  AND Status <> 'Completed' AND [User Name] = '" & DLookup("[User Name]", "[Users]", "[User ID] = " & MyUserID) & "'"
        
        'Count the results of the filter
        FilterResults = DCount("[ID]", "[Tracker]", DateCriteria)
        MsgBox DateCriteria, vbOKOnly, "Test"
        
        'If there are results then apply the filter
        If (FilterResults > 0) Then
                
            'Set the global DateFilter variable to True - this will identify the date filter as being active in the event that
            'other filters are also being used.
            DateFilter = True
            
            'Apply the criteria to the Form
            Me.Filter = DateCriteria
            Me.FilterOn = True
            
            
        
        'If there are no results return an error
        Else
            MsgBox "There are no queries matching this date range criteria, please try again.", vbOKOnly, "No Queries Found"
            
        End If

Anyone got any ideas????? It's really bugging me now!

Thanks,
John.

 

[DCrake]

Up to yesterday no problems... today problems... That's because everything was less than 13 both the month and the day number. But as today is the 13th Access has converted your date to US format.

David

 

[molloyjh]

Up to yesterday no problems... today problems... That's because everything was less than 13 both the month and the day number. But as today is the 13th Access has converted your date to US format.

David

I noticed it first last week based on some of the dates I was entering, but I just don't know why it would happen.

I set up a msgbox earlier today to print the dates in the code above with the dd-mmmm-yyyy format and they came up correctly, i.e. 01/07/2009 was appearing as 01st July 2009 rather than the 07th January 2009. If I enter 01/27/2009 into the unbound text box it converts it to 27/01/2009. My regional settings are correct etc so I have absolutely no idea why the filter is based on US dates when everything displays as UK.

 

[DCrake]

If you convert the dates to julien numbers and use the number range on the dates in your query that should resolve it.

For example if the Julien number for 13/07/2009 is 36941 (DateDiff("d",#13/07/2009#,Date))

Then in your query you can use where Date = 36941

David

 

[molloyjh]

If you convert the dates to julien numbers and use the number range on the dates in your query that should resolve it.

For example if the Julien number for 13/07/2009 is 36941 (DateDiff("d",#13/07/2009#,Date))

Then in your query you can use where Date = 36941

David

Will give it a go David.

Thanks,
John.

 

[groengoen]

Thanks! that DateDiff is brilliant for when dates are gratuitously converted to US format