Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Jul 2003
    Location
    Cincinnati, Ohio
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date Aging Query (2002)

    I have a table of project information includine ProjectID, ProjectCreatedDate, and ProjectName. I can query the table individually for projects older than 30 and less than 60 days old using <Date()-30 And >Date()-60. Is there an easy way to include grouping within a query that would allow for 61-90, 90-120 days old, etc.? Thank you. - scott

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Aging Query (2002)

    In the query add a field with the following expression :
    Int((Date()-[ProjectCreatedDate])/30)
    and group on that field
    Francois

  3. #3
    2 Star Lounger
    Join Date
    Jul 2003
    Location
    Cincinnati, Ohio
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Aging Query (2002)

    Thank you for the help. I created a field within the query Expr1: Int((Date()-[ProjectCreatedDate])/30) and set totals to group by. When I run the query I get an error: ODBC Call Failed. Microsoft ODBC SQL Server Driver - Sql server Invalid operator for data type. Operator equals divide, type equals datetime (#403) The table is a linked sql server table. The field property is set to smalldatetime. Thanks.

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Aging Query (2002)

    Please test the following function, it seems to work on my sql 2000, but I have to recognize that I have very little experience with sql server.
    <pre>Expr1: Val(IIf(Not IsDate([ProjectCreatedDate]),"",Int((Date()-DateSerial(Year([ProjectCreatedDate]),
    Month([ProjectCreatedDate]),Day([ProjectCreatedDate])))/30)))</pre>

    Maybe someone will come with a better idea to handel this date problem with sql server.
    Francois

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Date Aging Query (2002)

    I assume you're running this query in Access, right? SQL smalldatetime fields aren't fully compatible with Access, so you may need to use a stored procedure to cast the ProjectCreatedDate as a datetime instead. Also, try wrapping the expression (Date()-[ProjectCreatedDate]) in a conversion function like CInt() and see if that makes a difference.

    You might also want to take a look at the Partition function to group your data into ranges.
    Charlotte

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Date Aging Query (2002)

    Another strategy you might try is using the DateDiff() function on the current date and the ProjectCreated date, and request the difference in either days or months depending on how you want to group things. That is then an arithmetic value that you could for example do a cross tab or a pivot table.
    Wendell

  7. #7
    2 Star Lounger
    Join Date
    Jul 2003
    Location
    Cincinnati, Ohio
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Aging Query (2002)

    Thank you all for your help. I will try your suggestions and let you know how it turns out. -scott

Posting Permissions

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