We help IT Professionals succeed at work.

using dates as a bindingsource filter?

Gary Samuels
Gary Samuels asked
on
5,696 Views
Last Modified: 2012-05-05
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?

Comment
Watch Question

Commented:
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 SamuelsPlant Manager

Author

Commented:
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
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Gary SamuelsPlant Manager

Author

Commented:
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

Commented:
Gary

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

Roger

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.