not returning correct data from access database in vb.net 2005

Hi,

I have a table that has a date column in the following format dd/mm/yyyy. when I use the following code: -

        rs.Open("Select sum(TaxCost + NetDelCost) as DeliveryNetttotal , Sum(NetDelCost) as DeliveryGrossTotal from products WHERE Sold = true AND Delivery between #" & DateTimePicker1.text  & "# AND #" & DateTimePicker2.text & "#", conn)

it does not show the data correctly. If I have an entry with the date 03/12/2007 and if I put a date of between 02/12/2007 - 04/12/2007 not data is returned. if I put the date between as 02/12/2007 - 12/12/2007 it shows the data. I thought this may be because it likes the us formatting so I used the following code: -

        rs.Open("Select sum(TaxCost + NetDelCost) as DeliveryNetttotal , Sum(NetDelCost) as DeliveryGrossTotal from products WHERE Sold = true AND Delivery between #" & DateTimePicker1.Value.ToString("MM/dd/yyyy") & "# AND #" & DateTimePicker2.Value.ToString("MM/dd/yyyy") & "#", conn)

but it does the same. what am I doing wrong please.

Many Thanks
Lee
ljhodgettAsked:
Who is Participating?
 
Joel CoehoornConnect With a Mentor Director of Information TechnologyCommented:
My first thought was that you were storing your date as a string value, but since you're using the # format that's probably not the case.

So instead I'll suggest that you try using this format with Value.ToString():
"yyyy-MM-dd 12:00:00 AM"
0
 
ChumadCommented:
For kicks, you could try adding the time to the date as well:

  rs.Open("Select sum(TaxCost + NetDelCost) as DeliveryNetttotal , Sum(NetDelCost) as DeliveryGrossTotal from products WHERE Sold = true AND Delivery between #" & DateTimePicker1.text  & " 12:00 AM# AND #" & DateTimePicker2.text & " 11:59 PM#", conn)

Generally speaking, if your date of 3/12/2007 includes a time of say 6 PM, the database and you do a search using 2/12/07 and 3/12/07 it only searches up until 12:00 AM of 3/12/07 and excludes the record that occured at 6 PM.
0
 
ljhodgettAuthor Commented:
Hi,

No joy i'm afraid.

Best Regards
Lee
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.