Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2007-12-03
3
Medium Priority
?
153 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

972 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