Filter ms access table between two dates from vb.net

Question

I am trying to filter tables between two date in ms access from vb.net . the query below does Not work when data type is date/ time . When I change data type to text, it gives me sum of the columns

select * into Table30 from (SELECT 'Table6' AS [Table], SUM(a) - SUM(b) AS Result FROM table6 where date_ BETWEEN " & FromDate & " AND " & ToDate & " UNION ALL SELECT 'Table7', SUM(a) - SUM(b)  FROM table7 where date_ BETWEEN  " & FromDate & " AND " & ToDate & ")a

I have added # sign before date , I get the error "Syntax error in date in query expression 'date_ BETWEEN# 1/1/13 AND 1/5/13

select * into Table30 from (SELECT 'Table6' AS [Table], SUM(a) - SUM(b) AS Result FROM table6 where date_ BETWEEN# " & FromDate & " AND " & ToDate & "# UNION ALL SELECT 'Table7', SUM(a) - SUM(b)  FROM table7 where date_ BETWEEN # " & FromDate & " AND " & ToDate & "#)a

is there anyway to fix it .thanks 

Answer 1

You need to enclose each date in "#" characters, like this:

"select * into Table30 from (SELECT 'Table6' AS [Table], SUM(a) - SUM(b) AS Result FROM table6 where date_ BETWEEN #" & FromDate & "# AND #" & ToDate & "# UNION ALL SELECT 'Table7', SUM(a) - SUM(b)  FROM table7 where date_ BETWEEN #" & FromDate & "# AND #" & ToDate & "#)"

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 2

You need the # before and after both dates.

ie. you need

"# AND #"

in both BETWEEN clauses.