Solved

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

Posted on 2007-12-03
3
147 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
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 …
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

752 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