Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2007
    Posts
    21

    Unanswered: compare date, MS Access db

    HI all,
    I have a select statement with a where clause
    select * from table1 where date='19/04/2007'

    with this I get the following error message
    [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

    is there a special way to compare a date field in MS Access sql?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Having a field called "date" is going to cause you all sorts of problems!
    Have you tried using [Date] instead? (your field name surrounded by square brackets).

    Let me know how it goes!
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2007
    Posts
    21
    the date field is actuall buy-date
    so it is
    select * from table1 where buy-date = '19/04/2007'

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Again, minus signs may be causing you issues - try the square parens.
    George
    Home | Blog

  5. #5
    Join Date
    Apr 2007
    Posts
    21
    Thanks the square brackets did the magic

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Just another point...
    Dates in access queries should have hashes (#) surrounding them. (apostrophes are for strings, hashes for dates)
    Eg:
    Code:
    WHERE [buy-date] = #1/1/2001#
    Experts, correct me if I'm wrong
    George
    Home | Blog

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    This where clause is not valid SQL (we are in a SQL group here). First, the brackets are wrong, and second you have to use single-quotes as string delimiters. Instead of the brackets, SQL knows so-called delimited identifiers, which are enclosed by double quotes:
    Code:
    WHERE "buy-date" = '1/1/2001'
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    Apr 2007
    Posts
    21
    Thanks a lot guys, i figured it out...
    yes the date to which it has to be compared has to be put between # and the column names in betweeen []
    This even shows up within the MS Access queries
    select * from table1 where [buy-date] >= #01/10/2007#
    this works, though i havent tried if using the double quotes

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    lesson learned: post in the correct forum, and the sql you get will be the right sql

    thread moved to access forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •