Results 1 to 6 of 6
  1. #1
    Join Date
    May 2004
    Posts
    90

    Unanswered: Dates in a query

    I am writing a query to find out what fiscal year a date is in. The fiscal year would start 6/1/xx and end on 5/31/xx+1.

    The dates I have are in the form mm/dd/yy

    For example:

    Date: Fiscal Year:
    1/1/2000 1999
    7/1/2000 2000
    4/15/2001 2000
    6/4/2001 2001

    etc....

    I have something like this:

    SELECT Year(DateAdd("m",6,[Seminar].[Date])) AS FiscalYear

    but it is wrong.

    Can someone help me with the code? Thanks

  2. #2
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    this one seems working, I used -5 instead of 6 because month numbers starts from 1;

    Code:
     
    SELECT Year(DateAdd("m",-5,[Seminar].[Date])) AS FiscalYear;
    ghozy.

  3. #3
    Join Date
    May 2004
    Posts
    90
    It is not going to work though because lets say the date is 1/8/2001 and that should be in the fiscal year 2000. So adding to the date does not work. But whenver I try to subtract then it says that "You tried to execute a query that does not include the specified expersion 'Year(DateAdd("m",-6,[Seminar].[Date])' as part of an aggregate function.

    How can I get around this?
    Last edited by lskuff; 06-14-04 at 18:35.

  4. #4
    Join Date
    May 2004
    Posts
    90
    Alright sorry I fixed the above post now it is correctly asking my question. Also when I put anything else except Year(DateAdd("m",6,[Seminar].[Date]) it says the same thing. For example I cannot put
    Year(DateAdd("m",5,[Seminar].[Date]) either.

  5. #5
    Join Date
    May 2004
    Posts
    90
    I just put GROUP BY and then the function now it works. Thanks for the code.

  6. #6
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    um no problem. you found out your way by yourself though.
    ghozy.

Posting Permissions

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