Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2004
    Posts
    77

    Unanswered: Sql please help !!

    For example, This is the table name microprice in Microsoft Access.

    dat price
    8/23/2004 11
    8/24/2004 28
    8/25/2004 13
    8/26/2004 24
    8/27/2004 56
    8/28/2004 12
    8/29/2004 32
    8/30/2004 21

    I want to show the last five price not include the date of today. Assume today is 8/30/2004

    I want to write the sql that show
    8/25/2004 13
    8/26/2004 24
    8/27/2004 56
    8/28/2004 12
    8/29/2004 32

    So how can I write SQl. I try to use
    sql="select top 5 * from microprice where dat <> '%"&date()&"%' order by dat desc"

    But it doesn't work !!!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    where dat <> date()
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2004
    Posts
    77
    I have already tried. But it doesn't work!!! It still shows the date of today.

  4. #4
    Join Date
    Aug 2004
    Posts
    77
    Ok. It works now.
    It must be
    "select top 5 * from microprice where dat not like '%"&date()&"%' order by dat desc "

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ok, the date values must have time components

    try where dat < date()
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    like '%"&date()&"%' treats the dat value as a string

    if this "works" it is not efficient, and will disregard any index on dat

    furthermore, it relies on the default access display format being the same format as your default scripting language date() function

    the solutions i gave you use the builtin access database date() function, thus treating dat values as a datetime values
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Aug 2004
    Posts
    77
    I tried but it does not work !!

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what did you try, and what did it produce?

    what is the datatype of your date column?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Aug 2004
    Posts
    77
    I tried
    sql="select top 5 * from microprice where dat < date() order by dat desc"
    But it is not work.

    My datatype of date column is Text
    Because the first time I use date/time but there is nothing. But when I change into text and use

    sql="select top 5 * from microprice where dat not like '%"&date()&"%' order by dat desc "

    It work

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    TEXT is a poor choice in comparison to DATE/TIME

    good luck and have fun
    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
  •