Smarton Smarton - 6 months ago 10
Vb.net Question

How to Select record between a given time range and Item Category

I have an MS Access database table "tbltransactions" with seperate date and time column. I need to select all data from my table checking three conditions

1.records that are in a specific category.

2.records with custom date range I set.

3.records with custom time range I set.

My table structure is shown below

+-------+---------+-------+--------+------------+-------------+------------+
| id | item | units | Amount | category | adddate | addtime |
+-------+---------+-------+--------+------------+-------------+------------+
| 1 | A | 1 | 100 | toys | 5/23/2016 | 8:05 AM |
+-------+---------+-------+--------+------------+-------------+------------+
| 2 | A | 1 | 100 | toys | 5/23/2016 | 8:45 AM |
+-------+---------+-------+--------+------------+-------------+------------+
| 3 | B | 1 | 200 | book | 5/23/2016 | 9:05 AM |
+-------+---------+-------+--------+------------+-------------+------------+
| 4 | A | 1 | 100 | toys | 5/23/2016 | 9:25 AM |
+-------+---------+-------+--------+------------+-------------+------------+
| 5 | A | 1 | 100 | toys | 5/23/2016 | 11:05 AM |
+-------+---------+-------+--------+------------+-------------+------------+
| 6 | A | 1 | 100 | toys | 5/23/2016 | 1:10 PM |
+-------+---------+-------+--------+------------+-------------+------------+
| 7 | A | 1 | 100 | toys | 5/23/2016 | 3:30 PM |
+-------+---------+-------+--------+------------+-------------+------------+
| 8 | A | 1 | 100 | toys | 5/23/2016 | 4:12 PM |
+-------+---------+-------+--------+------------+-------------+------------+
| 9 | A | 1 | 100 | toys | 5/23/2016 | 5:47 PM |
+-------+---------+-------+--------+------------+-------------+------------+
| 10 | A | 1 | 100 | toys | 5/23/2016 | 6:22 PM |
+-------+---------+-------+--------+------------+-------------+------------+
| 11 | B | 1 | 200 | book | 5/24/2016 | 8:15 AM |
+-------+---------+-------+--------+------------+-------------+------------+
| 12 | B | 1 | 200 | book | 5/24/2016 | 9:33 AM |
+-------+---------+-------+--------+------------+-------------+------------+
| 13 | A | 1 | 100 | toys | 5/24/2016 | 1:03 PM |
+-------+---------+-------+--------+------------+-------------+------------+
| 14 | B | 1 | 200 | book | 5/24/2016 | 2:22 PM |
+-------+---------+-------+--------+------------+-------------+------------+
| 15 | A | 1 | 100 | toys | 5/24/2016 | 3:10 PM |
+-------+---------+-------+--------+------------+-------------+------------+
| 16 | A | 1 | 100 | toys | 5/24/2016 | 4:07 PM |
+-------+---------+-------+--------+------------+-------------+------------+
| 17 | A | 1 | 100 | toys | 5/24/2016 | 6:15 PM |
+-------+---------+-------+--------+------------+-------------+------------+
| 18 | B | 1 | 200 | book | 5/24/2016 | 6:17 PM |
+-------+---------+-------+--------+------------+-------------+------------+
| 19 | A | 1 | 100 | toys | 5/24/2016 | 6:30 PM |
+-------+---------+-------+--------+------------+-------------+------------+
| 20 | B | 1 | 200 | book | 5/24/2016 | 6:42 PM |
+-------+---------+-------+--------+------------+-------------+------------+


I can select record based on category and a specific date

sql = "SELECT id, item
FROM Table1
WHERE category ='toys'
AND DateValue(adddate) = #'5/23/2016'#


this returns records 1,2,4,5,6,7,8,9,10

I can also select record based on category and a date range

sql = "SELECT id, item
FROM Table1
WHERE category ='toys'
AND DateValue(adddate) > #'5/23/2016'#
AND DateValue(adddate) <= #'5/24/2016'#


this returns records 1,2,4,5,6,7,8,9,10,13,15,16,17,19

now I need to get record from 5/23/2016 8:00 AM to 5/24/2016 3:00 PM. How to do this?

Answer

Consider concatenating string/date values in the CDate() function available in Jet/ACE ODBC as well as the Access.exe UI:

sql = "SELECT id, item 
         FROM Table1
        WHERE category ='toys' 
          AND CDate(adddate & ' ' & addtime) > CDate('5/23/2016 8:00 AM')
          AND CDate(adddate & ' ' & addtime) <= CDate('5/24/2016 3:00 PM')

Alternatively, use BETWEEN operator (which includes end points, so I add a second):

sql = "SELECT id, item 
         FROM Table1
        WHERE category ='toys' 
          AND CDate(adddate & ' ' & addtime) 
          BETWEEN CDate('5/23/2016 8:01 AM') AND CDate('5/24/2016 3:00 PM')