none
Filter ms access table between two dates from vb.net RRS feed

  • 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 

    Thursday, September 24, 2015 10:30 PM

Answers

All replies

  • 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

    Thursday, September 24, 2015 10:54 PM
  • You need the # before and after both dates.

    ie. you need

    "# AND #"

    in both BETWEEN clauses.


    Thursday, September 24, 2015 10:55 PM