Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2007-12-03
3
Medium Priority
?
155 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:
Joel Coehoorn earned 2000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses

580 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