asked on

using dates as a bindingsource filter?

I have a dataset (based on a SQL table) which contains a column named DateOfOrder .
The column is set to a datetime data type.
I would like to use 2 datetimepickers (dtpFromDate & dtpToDate) to establish dates to filter the dataset with.
I have tried:

With me.myBindingSource
.Filter = “DateOfOrder Between dtpFromDate.value And dtpToDate.value”
End with

I’ve tried every conceivable combination using single quotes and pound signs. Does anyone know how to use a date as a bindingSource filter?

Sancler

Have a look at this

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatadatacolumnclassexpressiontopic.asp

Although it is stated to be specific to DataColumn.Expression, the same rules apply to .Filter expressions. In this case

a) you will need to use > AND < because BETWEEN is not supported; and

b) you will need to enclose dates in #

Roger

Gary Samuels

ASKER

I have been able to get the following to work:
.Filter = "DateOfOrder >= '#6/1/2006#' And DateOfOrder <= '#8/1/2006#'"

I've been trying to replace the hard coded dates with the value from the dateTimePickers with out any luck.
I've tried the following blocks:

.Filter = "DateOfOrder >= '#Me.dtpFromDate.Value#' And DateOfOrder <= '#8/1/2006#'"
.Filter = "DateOfOrder >= 'Me.dtpFromDate.Value' And DateOfOrder <= '#8/1/2006#'"
.Filter = "DateOfOrder >= #Me.dtpFromDate.Value# And DateOfOrder <= '#8/1/2006#'"

They all return the same error:
Cannot perform '>=' operation on System.DateTime and System.String.

It appears that one of the values is a string and because I was able to get the filter to work when dates were included I would assume the me.dtpFromDate.Value is being converted to a string so I tried the following.

Dim fromDate As Date
fromDate = CType(Me.dtpFromDate.Value, Date)

With Me.myBindingSource
.Filter = "DateOfOrder >= '#fromDate#' And DateOfOrder <= '#8/1/2006#'"
End With

And I still get the same error message:
Cannot perform '>=' operation on System.DateTime and System.String.

And suggestion would be greatly appreciated.
Thanks,
Gary

ASKER CERTIFIED SOLUTION

Sancler

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Gary Samuels

ASKER

Sorry for the delay, I had to work at my real job for a while. Now back to what I enjoy....

I did read the link you provided, in fact I was studying it before I received your suggestion but reading and understanding are two different things for me. I looked closely at you're example and I do clearly see what your demonstrating. From that I didn't have much trouble putting together this code:

Dim fromDate As String = "#" & dtpFromDate.Text & "#"
Dim toDate As String = "#" & dtpToDate.Text & "#"

With Me.myBindingSource
.Filter = "DateOfOrder >= " & fromDate
End With

I did have some trouble when I tried to include an "AND" in the Filter but this is what I came up with and it appears to be working. Please let me know if you see something wrong with it.

With Me.myBindingSource
.Filter = "DateOfOrder >= " & fromDate & " AND DateOfOrder <= " & toDate
' Debug.WriteLine("DateOfOrder >= " & fromDate & " AND DateOfOrder <= " & toDate)
End With

You may not realize it but you've given me assistance on some other questions and I couldn't be happier with the answers. Thank you for your assistance!

Gary

Sancler

Gary

Your code looks fine. Thanks for the points. Glad to help.

Roger