[RESOLVED] SQL date and time range

Hi, I have an Access 2007 db, using the following vb.net2008 relevant items:

Dim con As New OleDb.OleDbConnection
Dim dbProvider As String
Dim dbSource As String
Dim ds As New DataSet
Dim da As OleDb.OleDbDataAdapter
Dim Command As OleDb.OleDbCommand

table1 data:

ID TRACE DATE TRACE TIME TRACE 1 TRACE 2 TRACE 3
1 25/11/2011 00:00:00 2000 1900 2100
2 25/11/2011 01:00:00 2100 2000 2200
3 25/11/2011 02:00:00 2000 1800 2100
4 25/11/2011 03:00:00 1900 1700 2050
5 25/11/2011 04:00:00 1800 1900 1900
6 25/11/2011 05:00:00 1600 1700 1500
7 25/11/2011 06:00:00 1400 1300 1350
8 25/11/2011 07:00:00 1100 1250 1200
9 25/11/2011 08:00:00 800 900 950
10 25/11/2011 09:00:00 400 550 500
11 25/11/2011 10:00:00 200 250 350
12 25/11/2011 11:00:00 0 100 0
13 25/11/2011 12:00:00 0 0 50
14 25/11/2011 13:00:00 0 50 0
15 25/11/2011 14:00:00 150 250 200
16 25/11/2011 15:00:00 350 350 400
17 25/11/2011 16:00:00 650 650 700
18 25/11/2011 17:00:00 650 750 650
19 25/11/2011 18:00:00 650 650 750
20 25/11/2011 19:00:00 850 950 850
21 25/11/2011 20:00:00 850 1000 900
22 25/11/2011 21:00:00 1200 1300 1100
23 25/11/2011 22:00:00 1500 1450 1450
32 25/11/2011 23:00:00 1450 1350 1200
33 26/11/2011 00:00:00 2000 1900 2100
34 26/11/2011 01:00:00 2100 2000 2200
35 26/11/2011 02:00:00 2000 1800 2100
36 26/11/2011 03:00:00 1900 1700 2050
37 26/11/2011 04:00:00 1800 1900 1900
38 26/11/2011 05:00:00 1600 1700 1500
39 26/11/2011 06:00:00 1400 1300 1350
40 26/11/2011 07:00:00 1100 1250 1200
41 26/11/2011 08:00:00 800 900 950
42 26/11/2011 09:00:00 400 550 500
43 26/11/2011 10:00:00 200 250 350
44 26/11/2011 11:00:00 0 100 0
45 26/11/2011 12:00:00 0 0 50
46 26/11/2011 13:00:00 0 50 0
47 26/11/2011 14:00:00 150 250 200
48 26/11/2011 15:00:00 350 350 400
49 26/11/2011 16:00:00 650 650 700
50 26/11/2011 17:00:00 650 750 650
51 26/11/2011 18:00:00 650 650 750
52 26/11/2011 19:00:00 850 950 850
53 26/11/2011 20:00:00 850 1000 900
54 26/11/2011 21:00:00 1200 1300 1100
55 26/11/2011 22:00:00 1500 1450 1450
56 26/11/2011 23:00:00 1450 1350 1200

I'm trying to filter out data from specific times on specific dates, for instance, i'd like to display data:

beginning on 25/11/2011 at 23:00:00
until 26/11/2011 at 02:00:00.

My sql statement is as follows:

SELECT * FROM table1
WHERE
[TRACE DATE] = #11/25/2011# AND [TRACE TIME] = '23:00:00'
BETWEEN
[TRACE DATE] = #11/26/2011# AND [TRACE TIME] = '02:00:00'"

But this is the data I get back, notice the omission of the whole days data for 26/11/2011 and the 23:00:00 entry on the 25/11/2011:

25/11/2011 00:00:00 200019002100
25/11/2011 01:00:00 210020002200
25/11/2011 02:00:00 200018002100
25/11/2011 03:00:00 190017002050
25/11/2011 04:00:00 180019001900
25/11/2011 05:00:00 160017001500
25/11/2011 06:00:00 140013001350
25/11/2011 07:00:00 110012501200
25/11/2011 08:00:00 800900950
25/11/2011 09:00:00 400550500
25/11/2011 10:00:00 200250350
25/11/2011 11:00:00 01000
25/11/2011 12:00:00 0050
25/11/2011 13:00:00 0500
25/11/2011 14:00:00 150250200
25/11/2011 15:00:00 350350400
25/11/2011 16:00:00 650650700
25/11/2011 17:00:00 650750650
25/11/2011 18:00:00 650650750
25/11/2011 19:00:00 850950850
25/11/2011 20:00:00 8501000900
25/11/2011 21:00:00 120013001100
25/11/2011 22:00:00 150014501450

It's almost like it's trying to work backwards? I'm so stumped, i've been working on this for a week now!

Maybe if you do something like:

Code:

---

SELECT * FROM table1
WHERE ( [TRACE DATE] + [TRACE TIME] ) BETWEEN #11/25/2011 23:00:00# AND #11/26/2011 02:00:00#

---

Hi I tried that, but it returned all the data for 25/11/2011?

Thanks,

sigh... what are the data types for each field? specifically the Trace Date and Trace Time fields... and more importantly, WHY isn't that being stored as a SINGLE datetime field?

-tg

Thanks, yes, i checked the datatypes, and it was set to Text for the time field. Thanks for all the help!

So WHAT did you do? Does this mean it works now? Take a penny, give a penny... help the next fellow... can you post what you did to fix your problem?

-tg

No problems, what I did was change the field data-type in MS Access. In Acess 2007, by right-clicking on the table, brings up an option 'Design View'.

From here, you can select the data-type for each field. It seems that if the data in the field is only going to be one of these data-types, then the SQL statement will only work if that's how it's assigned. I selected 'Date/Time' for my [TRACE DATE] and [TRACE TIME] fields, the others I set to 'Number'.

I'm no expert, but it seems to make sense with the previous post like 'techgnome' pointed out - thanks for that pointer :)