Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Location
    Washington, DC
    Posts
    116

    Unanswered: weird fiscal year expression

    I'm trying to create a calculation on a report using a field in a query that is not part of the SQL for the recordsource. The field is a long interger signifying the current fiscal year.

    the recordsource for the report is a table which is linked to the fiscal year query's source table to signify what fiscal year an event is scheduled to take place.

    table 1
    id - long
    name - text
    year - linked to table2.fiscalyear

    table 2
    fiscal year - long
    current - yes/no

    query 1
    table2.fiscalyear where current=yes

    i'm trying to create a report that calculates the difference between the current year specified in query 1 and the year specified in table 1. can anyone help?

  2. #2
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    If you put this in a query, it will give you the requested results. No Join required.

    SELECT Table1.ID, Table1.Name, Table1.Year, Query1.FiscalYear, [Query1].[fiscalyear]-[table1].[year] AS Difference
    FROM Table1, Query1;

    Be aware that if you have more than one FiscalYear record marked as current in your Table2, you will get duplicates of your Table1 data. That is why I included the FiscalYear field in the Select statement so you will be able to see why.

    TD

  3. #3
    Join Date
    Sep 2003
    Location
    Washington, DC
    Posts
    116
    Thanks!!! That worked.

Posting Permissions

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