Solved

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

Posted on 2007-12-03
3
145 Views
Last Modified: 2010-04-23
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
0
Comment
Question by:ljhodgett
3 Comments
 
LVL 8

Expert Comment

by:Chumad
ID: 20398193
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
 
LVL 18

Accepted Solution

by:
jcoehoorn earned 500 total points
ID: 20398556
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
 

Author Comment

by:ljhodgett
ID: 20406290
Hi,

No joy i'm afraid.

Best Regards
Lee
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question