Results 1 to 14 of 14

Thread: Date() question

  1. #1
    Join Date
    Sep 2004
    Location
    Atlanta, GA
    Posts
    62

    Unanswered: Date() question

    I have a table (pbi) with a column (pbidate) with a date value, i want to run a query that finds all records that are from today to 2 weeks earlier.

    SELECT *
    FROM pbi
    WHERE pbi.pbidate between Date() AND Date() - 14

    I know this is crude but it MAY give you an isea what i looking for.
    Thanks in advance
    Brownie
    Last edited by Brownie13; 01-31-05 at 15:02.

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by Brownie13
    I have a table (pbi) with a column (pbidate) with a date value, i want to run a query that finds all records that are from today to 2 weeks earlier.

    SELECT *
    FROM pbi
    WHERE pbi.pbidate between Date() AND Date - 14

    I know this is crude but it MAY give you an isea what i looking for.
    Thanks in advance
    Brownie
    SELECT *
    FROM pbi
    WHERE (pbi.pbidate between Date AND DateAdd("d",-14,Date))
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Sep 2004
    Location
    Atlanta, GA
    Posts
    62
    Thanks for the quick reply,
    I'm, getting a "enter parameter value" box for "Date"
    Any ideas?

  4. #4
    Join Date
    Sep 2004
    Location
    Atlanta, GA
    Posts
    62
    SELECT *
    FROM pbi
    WHERE pbi.pbidate between Date() AND dateadd("d",-60,date())

    Thanks

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    If you are always referencing row created within the last 14 days then the between construct is superfluos

  6. #6
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Question

    Quote Originally Posted by Brownie13
    SELECT *
    FROM pbi
    WHERE pbi.pbidate between Date() AND dateadd("d",-60,date())

    Thanks
    Brownie,

    It appears you are missing the first (left) Parenthesis. Did you look at how he laid out the syntax fully?
    SELECT *
    FROM pbi
    WHERE (pbi.pbidate between Date AND DateAdd("d",-14,Date))

    BUD

  7. #7
    Join Date
    Sep 2004
    Location
    Atlanta, GA
    Posts
    62
    Thanks alot for the replies.
    Once again great support from the DBforums community.

    Brownie

  8. #8
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    ... And Neal's right: the BETWEEN is extraneous ...

    Ex:
    SELECT *
    FROM pbi
    WHERE (pbi.pbidate >= DateAdd("d",-14,Date))

    yields the same result ...
    Back to Access ... ADO is not the way to go for speed ...

  9. #9
    Join Date
    Sep 2004
    Location
    Atlanta, GA
    Posts
    62
    WHen i run

    SELECT *
    FROM pbi
    WHERE (pbi.pbidate >= DateAdd("d",-14,Date))


    I get the enter parameter box for Date again.

    If i add the parentheses here,

    SELECT *
    FROM pbi
    WHERE (pbi.pbidate >= DateAdd("d",-14,Date()))

    It returns all the records (from three months back?)

    What am i doing wrong?

  10. #10
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    What Access version is this? Also, are you creating a query with this or are you running the SQL?
    Back to Access ... ADO is not the way to go for speed ...

  11. #11
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Ok ... You're making a query to run ... Yes. You need to have the parens for the Date function to work in the QBE ...
    Back to Access ... ADO is not the way to go for speed ...

  12. #12
    Join Date
    Sep 2004
    Location
    Atlanta, GA
    Posts
    62
    If i add the parentheses though i get all the records returned (3 months worth)
    I'm using 2003.

    B

  13. #13
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by Brownie13
    If i add the parentheses though i get all the records returned (3 months worth)
    I'm using 2003.

    B
    I ran that same type of query on one of my tables with no problems ... Check your column type for the pbidate column. Make sure it's a Date/Time column ...
    Back to Access ... ADO is not the way to go for speed ...

  14. #14
    Join Date
    Sep 2004
    Location
    Atlanta, GA
    Posts
    62
    Yep, that was it (date time column)
    Thanks again
    Brownie

Posting Permissions

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