Results 1 to 13 of 13

Thread: Aging query

  1. #1
    Join Date
    Oct 2003
    Location
    Chicago, IL
    Posts
    154

    Unanswered: Aging query

    I would like to create a query that gathers records that are more than 120 days old from the current date. The field I am setting criteria for is a Date/Time field called CURDATE.

    Any suggestions? Thanks!

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: Aging query

    Originally posted by jimmyswinger
    I would like to create a query that gathers records that are more than 120 days old from the current date. The field I am setting criteria for is a Date/Time field called CURDATE.

    Any suggestions? Thanks!
    SELECT * FROM MyTable WHERE (DateDiff("d",Date,CURDATE) > 120);

    This do?

  3. #3
    Join Date
    Oct 2003
    Location
    Chicago, IL
    Posts
    154
    Aaugh! I get a "compile error in query expression (DateDiff("d",Date,CURDATE)"!

    However, you taught me how to create a SQL query, which I have never done before! I always used the design view before, so THANKS!

    I'm forced to use access 97 because the department I'm working for hasn't upgraded to xp yet... so maybe this is a problem?

    When I switched to SQL view I replaced the existing line (all it said was SELECT with what you typed in, and replaced the my table with [PROGRAMMED ADJ], so it reads:

    SELECT * FROM [Programmed adj] WHERE (DateDiff("d",Date,CURDATE) > 120);

    Any reason that won't work? Thank you very much for your help...

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by jimmyswinger
    Aaugh! I get a "compile error in query expression (DateDiff("d",Date,CURDATE)"!

    However, you taught me how to create a SQL query, which I have never done before! I always used the design view before, so THANKS!

    I'm forced to use access 97 because the department I'm working for hasn't upgraded to xp yet... so maybe this is a problem?

    When I switched to SQL view I replaced the existing line (all it said was SELECT with what you typed in, and replaced the my table with [PROGRAMMED ADJ], so it reads:

    SELECT * FROM [Programmed adj] WHERE (DateDiff("d",Date,CURDATE) > 120);

    Any reason that won't work? Thank you very much for your help...
    Keep in mind I'm using Access 2000 and 2002 so backwards compatibility is not assured ...

    I whipped this up:

    SELECT [WO Billing Detail].WorkOrderNumber, [WO Billing Detail].InvoiceID, [WO Billing Detail].[Billing Date], [WO Billing Detail].LaborCharged, [WO Billing Detail].PurchasesCharged, [WO Billing Detail].[Billed Cost]
    FROM [WO Billing Detail] WHERE (datediff("d",Date(),[WO Billing Detail].[Billing Date]) > 120);

    Try something like that ... I forgot that Date/date is a VBA function but in SQL it's Date().

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    date() sometimes misbehaves.

    try replacing with now()

    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Oct 2003
    Location
    Chicago, IL
    Posts
    154
    Hmmm, here's the latest!

    First, I'll describe how I created the query so I can make sure that it's appropriate:

    From the queries tab, I click "NEW". Then i choose "design view". Then I kill the table window and click "view", "SQL View" and *bam*! There I am.

    Then I delete the word "SELECT;" from that window.

    then I put
    SELECT * FROM [Programmed Adj] WHERE (DateDiff("d",Date(),[Programmed Adj]![curdate]) > 120);
    into there and saved. Tried to run, compile error.

    I also tried now() with no luck. I still get the compile error.

    This totally LOOKS like it should work, no? The CURDATE field is a date/time field... I don't need to convert that or get a VAL on that do I?

  7. #7
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by jimmyswinger
    Hmmm, here's the latest!

    First, I'll describe how I created the query so I can make sure that it's appropriate:

    From the queries tab, I click "NEW". Then i choose "design view". Then I kill the table window and click "view", "SQL View" and *bam*! There I am.

    Then I delete the word "SELECT;" from that window.

    then I put
    SELECT * FROM [Programmed Adj] WHERE (DateDiff("d",Date(),[Programmed Adj]![curdate]) > 120);
    into there and saved. Tried to run, compile error.

    I also tried now() with no luck. I still get the compile error.

    This totally LOOKS like it should work, no? The CURDATE field is a date/time field... I don't need to convert that or get a VAL on that do I?
    And like *BAM* your error is: [Programmed Adj]![curdate]

    You reference column names of tables with "." (in your case [Programmed Adj].[curdate])

    Got it?

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    you might also want to count the "(" and subtract the ")" ...i think i find one left over, but it might be the red wine talking.

    izy
    currently using SS 2008R2

  9. #9
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by izyrider
    you might also want to count the "(" and subtract the ")" ...i think i find one left over, but it might be the red wine talking.

    izy
    Red wine ...

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    oh yes, red wine (a modest Merlot/Gamaret 2002) and my wife just made pizza.

    is this heaven or what.

    izy
    currently using SS 2008R2

  11. #11
    Join Date
    Oct 2003
    Location
    Chicago, IL
    Posts
    154

    getting warmer!

    OH.. my... goodness...

    IT WORKED! At least, I didn't get a compile error!!

    Here's the freaky part: I had previously tried it with ! and . and but it still wouldn't work... UNTIL....

    *gasp*

    I closed access and then restarted it, it didn't flunk out! It prolly woulda worked five posts ago but hey... never thought I'd have to restart access to get something to work?!

    NO RED WINE YET I still don't get any records!!! he he! isn't this fun? (i'll share some egg salad sandwich with you, tho)...

    here is how the query now looks:

    SELECT *
    FROM [Programmed Adj]
    WHERE (DateDiff ("d", Date () , [Programmed Adj].[Curdate]) > 120);

    and when I run it, I don't get any records! I have verified that there are records that are from 2002 in there but they don't show up. I also tried using Date() and Now() but i still get an empty query.

    the CURDATE (Date/Time) field is in the table as such (one example: )
    12/18/2002

    What could it be, hmmmm? many many thanks

  12. #12
    Join Date
    Jun 2002
    Location
    Vienna, VA
    Posts
    53
    Date Diff is tricky. You're prolly going in the wrong direction. You might want to switch date() and curdate, otherwise you're getting negative numbers, which will never be >120.

    What I would do is create a new column in your query (yes, back to design view), make it equal to your date diff equation (to check that you're adding/subtracting the right way) then put a criteria in that column to be > 120. Then, look at the SQL for fun.

    Hope this is useful.

    Leah

  13. #13
    Join Date
    Oct 2003
    Location
    Chicago, IL
    Posts
    154
    Ding ding ding! AND WE HAVE A WINNER! woo hoo! Thanks, Leah!

    switching the curdate and date did the trick.

    I left it as SQL (but it works both ways, of course) and it now looks like this:

    SELECT *
    FROM [Programmed Adj]
    WHERE (((DateDiff("d",[Programmed Adj].[Curdate],Now()))>120));

    I love this forum!

    Thank you all for your prompt help, you saved my butt! (Bcuz I'm supposed to have this ready for the VP by tomorrow!). Best wishes- Mitch!

Posting Permissions

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