Search code examples
vb.netdatefilterbindingsource

How to stop the Month and Day from changing in the BindingSource.Filter?

This is the code for my BindingSource.Filter

 Dim FilterStartMonth As Date = DateTimePickerTodaysDateTime.Value.Date
 Dim Filter2MonthsBack As Date = DateTimePickerTodaysDateTime.Value.AddMonths(-2).Date
 ClockInTimesBindingSource.Filter = "EmployeeID = " & ComboBoxOfficeEmployeeFilter.SelectedValue & " and Date <= '" & Filter2MonthsBack & "' and Date >= '" & FilterStartMonth & "'"

When i go through it step by step as it runs these are the values:

Filter2MonthsBack = 1/2/2017 12:00:00 AM

FilterStartMonth = 3/2/2017 12:00:00 AM

Which is how i want them, but the BindingSource.Filter reads:

ClockInTimesBindingSource.Filter = "EmployeeID = 49 and Date <= '02/01/2017' and Date >= '02/03/2017'"

I can't work out why it is swapping the month and day around??

Any help would be much appreciated.

I have tried using .ToString("d") but it still chnges around in the filter string.

Solution

  • Your query says:

    filter where the date is <= Jan 2, 2017 AND date is >= Mar 2, 2017

    Query works, since that isn't possible.

    You probably meant to switch the equation around:

    " and Date >= '" & Filter2MonthsBack & "' and Date <= '" & FilterStartMonth & "'"