Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

JULY TO JUNE FISCAL YEAR

Discussion in 'Information Technology' started by ZigZagZak, Jul 28, 2006.

  1. ZigZagZak

    ZigZagZak
    Expand Collapse
    Guest

    Hello-
    I need a query to find all entries within the current fiscal year (starting
    July 1st to June 30th). So if the date I search for is July 30th I only want
    it to find the results for that month. If I say June 30th. It would be for
    the past 12 months.
    Thanks in advance.

    Zach K.
     
  2. Loading...


  3. James A. Fortune

    James A. Fortune
    Expand Collapse
    Guest

    ZigZagZak wrote:
    > Hello-
    > I need a query to find all entries within the current fiscal year (starting
    > July 1st to June 30th). So if the date I search for is July 30th I only want
    > it to find the results for that month. If I say June 30th. It would be for
    > the past 12 months.
    > Thanks in advance.
    >
    > Zach K.


    ZZZK:

    How about a function whose input is a date within the year and a test
    date whose output is a boolean indicating whether or not the test date
    is within the fiscal year. Then you could use the function in a query
    with the criterion '= -1' to return only the records with a date during
    that fiscal year. Then add the condition that the test date is on or
    before the search date.

    'pseudosql:
    e.g., SELECT InFiscalYear(dtInFiscalYear, [InvoiceDate]) As ToSelect,
    .... WHERE ToSelect = -1 AND [InvoiceDate] <= dtInFiscalYear;

    'module air code:

    Public Function InFiscalYear(dtInFiscalYear As Date, dtTest As Date) As
    Boolean
    Dim dtFYStart As Date
    Dim dtFYEnd As Date

    'July 1 to June 30
    If Month(dtInFiscalYear) >= 7 Then
    dtFYStart = DateSerial(Year(dtInFiscalYear), 7, 1)
    dtFYEnd = DateSerial(Year(dtInFiscalYear) + 1, 6, 30)
    Else
    dtFYStart = DateSerial(Year(dtInFiscalYear) - 1, 7, 1)
    dtFYEnd = DateSerial(Year(dtInFiscalYear), 6, 30)
    End If
    If dtTest >= dtFYStart And dtTest <= dtFYEnd Then
    InFiscalYear = True
    Else
    InFiscalYear = False
    End If
    End Function

    or using SQL only with a search date from a form:

    'SQL air code

    SELECT * FROM table WHERE [InvoiceDate] <=
    Forms!frmMain!DateInFiscalYear AND
    IIf(Month(Forms!frmMain!DateInFiscalYear) >= 7, [InvoiceDate] BETWEEN
    DateSerial(Year(Forms!frmMain!DateInFiscalYear), 7, 1) AND
    DateSerial(Year(Forms!frmMain!DateInFiscalYear) + 1, 6, 30),
    [InvoiceDate] BETWEEN DateSerial(Year(Forms!frmMain!DateInFiscalYear) -
    1, 7, 1) AND DateSerial(Year(Forms!frmMain!DateInFiscalYear), 6, 30)) = -1

    These seem to do what you are asking for. Maybe a parameter in the
    query would be better than referencing the form field five times.

    James A. Fortune
    MPAPoster@FortuneJames.com
     
  4. ZigZagZak

    ZigZagZak
    Expand Collapse
    Guest

    ok I tried that a couple times, and a couple different ways and it works,
    BUT. It errors out sometimes. I think its because the "invoice date" in your
    sql I have being taken from results of another query, and it doesn't finish
    the update fast enough when theres alot of results for it to be used. Does
    this sound like what my problem is? And if so, how do I fix it.( I don't want
    to have to type in the date for the query, thats why I pointed to a date
    field in the other query) here is my sql.

    SELECT BOOKINGS.[PT BOOKED], BOOKINGS.[MH BOOKED], BOOKINGS.DATE,
    BOOKINGS.[TOTAL BOOKED], BOOKINGS.[PT TRANSFERED OUT], BOOKINGS.[MH
    TRANSFERED OUT], BOOKINGS.[SHEAVES TRANSERED OUT], BOOKINGS.[TOTAL ORDERS],
    BOOKINGS.[NUMBER PT ORDERS BOOKED], BOOKINGS.[NUMBER MH ORDERS BOOKED],
    BOOKINGS.[TOTAL SHIPPED], BOOKINGS.[TOTAL INVOICE], BOOKINGS.MONTH,
    BOOKINGS.YEAR, BOOKINGS.[MONTH NUMBER]
    FROM BOOKINGS
    WHERE (((BOOKINGS.DATE)<=[Forms]![BOOKINGS DAILY REPORT]![THE LAST DATE])
    AND ((IIf(Month([Forms]![BOOKINGS DAILY REPORT]![THE LAST DATE])>=7,[Date]
    Between DateSerial(Year([Forms]![BOOKINGS DAILY REPORT]![THE LAST DATE]),7,1)
    And DateSerial(Year([Forms]![BOOKINGS DAILY REPORT]![THE LAST
    DATE])+1,6,30),[DATE] Between DateSerial(Year([Forms]![BOOKINGS DAILY
    REPORT]![THE LAST DATE])-1,7,1) And DateSerial(Year([Forms]![BOOKINGS DAILY
    REPORT]![THE LAST DATE]),6,30)))=-1));


    "James A. Fortune" wrote:

    > ZigZagZak wrote:
    > > Hello-
    > > I need a query to find all entries within the current fiscal year (starting
    > > July 1st to June 30th). So if the date I search for is July 30th I only want
    > > it to find the results for that month. If I say June 30th. It would be for
    > > the past 12 months.
    > > Thanks in advance.
    > >
    > > Zach K.

    >
    > ZZZK:
    >
    > How about a function whose input is a date within the year and a test
    > date whose output is a boolean indicating whether or not the test date
    > is within the fiscal year. Then you could use the function in a query
    > with the criterion '= -1' to return only the records with a date during
    > that fiscal year. Then add the condition that the test date is on or
    > before the search date.
    >
    > 'pseudosql:
    > e.g., SELECT InFiscalYear(dtInFiscalYear, [InvoiceDate]) As ToSelect,
    > .... WHERE ToSelect = -1 AND [InvoiceDate] <= dtInFiscalYear;
    >
    > 'module air code:
    >
    > Public Function InFiscalYear(dtInFiscalYear As Date, dtTest As Date) As
    > Boolean
    > Dim dtFYStart As Date
    > Dim dtFYEnd As Date
    >
    > 'July 1 to June 30
    > If Month(dtInFiscalYear) >= 7 Then
    > dtFYStart = DateSerial(Year(dtInFiscalYear), 7, 1)
    > dtFYEnd = DateSerial(Year(dtInFiscalYear) + 1, 6, 30)
    > Else
    > dtFYStart = DateSerial(Year(dtInFiscalYear) - 1, 7, 1)
    > dtFYEnd = DateSerial(Year(dtInFiscalYear), 6, 30)
    > End If
    > If dtTest >= dtFYStart And dtTest <= dtFYEnd Then
    > InFiscalYear = True
    > Else
    > InFiscalYear = False
    > End If
    > End Function
    >
    > or using SQL only with a search date from a form:
    >
    > 'SQL air code
    >
    > SELECT * FROM table WHERE [InvoiceDate] <=
    > Forms!frmMain!DateInFiscalYear AND
    > IIf(Month(Forms!frmMain!DateInFiscalYear) >= 7, [InvoiceDate] BETWEEN
    > DateSerial(Year(Forms!frmMain!DateInFiscalYear), 7, 1) AND
    > DateSerial(Year(Forms!frmMain!DateInFiscalYear) + 1, 6, 30),
    > [InvoiceDate] BETWEEN DateSerial(Year(Forms!frmMain!DateInFiscalYear) -
    > 1, 7, 1) AND DateSerial(Year(Forms!frmMain!DateInFiscalYear), 6, 30)) = -1
    >
    > These seem to do what you are asking for. Maybe a parameter in the
    > query would be better than referencing the form field five times.
    >
    > James A. Fortune
    > MPAPoster@FortuneJames.com
    >
     
  5. ZigZagZak

    ZigZagZak
    Expand Collapse
    Guest

    Could I make the first query run....and have this query wait till its done,
    then run.

    "ZigZagZak" wrote:

    > ok I tried that a couple times, and a couple different ways and it works,
    > BUT. It errors out sometimes. I think its because the "invoice date" in your
    > sql I have being taken from results of another query, and it doesn't finish
    > the update fast enough when theres alot of results for it to be used. Does
    > this sound like what my problem is? And if so, how do I fix it.( I don't want
    > to have to type in the date for the query, thats why I pointed to a date
    > field in the other query) here is my sql.
    >
    > SELECT BOOKINGS.[PT BOOKED], BOOKINGS.[MH BOOKED], BOOKINGS.DATE,
    > BOOKINGS.[TOTAL BOOKED], BOOKINGS.[PT TRANSFERED OUT], BOOKINGS.[MH
    > TRANSFERED OUT], BOOKINGS.[SHEAVES TRANSERED OUT], BOOKINGS.[TOTAL ORDERS],
    > BOOKINGS.[NUMBER PT ORDERS BOOKED], BOOKINGS.[NUMBER MH ORDERS BOOKED],
    > BOOKINGS.[TOTAL SHIPPED], BOOKINGS.[TOTAL INVOICE], BOOKINGS.MONTH,
    > BOOKINGS.YEAR, BOOKINGS.[MONTH NUMBER]
    > FROM BOOKINGS
    > WHERE (((BOOKINGS.DATE)<=[Forms]![BOOKINGS DAILY REPORT]![THE LAST DATE])
    > AND ((IIf(Month([Forms]![BOOKINGS DAILY REPORT]![THE LAST DATE])>=7,[Date]
    > Between DateSerial(Year([Forms]![BOOKINGS DAILY REPORT]![THE LAST DATE]),7,1)
    > And DateSerial(Year([Forms]![BOOKINGS DAILY REPORT]![THE LAST
    > DATE])+1,6,30),[DATE] Between DateSerial(Year([Forms]![BOOKINGS DAILY
    > REPORT]![THE LAST DATE])-1,7,1) And DateSerial(Year([Forms]![BOOKINGS DAILY
    > REPORT]![THE LAST DATE]),6,30)))=-1));
    >
    >
    > "James A. Fortune" wrote:
    >
    > > ZigZagZak wrote:
    > > > Hello-
    > > > I need a query to find all entries within the current fiscal year (starting
    > > > July 1st to June 30th). So if the date I search for is July 30th I only want
    > > > it to find the results for that month. If I say June 30th. It would be for
    > > > the past 12 months.
    > > > Thanks in advance.
    > > >
    > > > Zach K.

    > >
    > > ZZZK:
    > >
    > > How about a function whose input is a date within the year and a test
    > > date whose output is a boolean indicating whether or not the test date
    > > is within the fiscal year. Then you could use the function in a query
    > > with the criterion '= -1' to return only the records with a date during
    > > that fiscal year. Then add the condition that the test date is on or
    > > before the search date.
    > >
    > > 'pseudosql:
    > > e.g., SELECT InFiscalYear(dtInFiscalYear, [InvoiceDate]) As ToSelect,
    > > .... WHERE ToSelect = -1 AND [InvoiceDate] <= dtInFiscalYear;
    > >
    > > 'module air code:
    > >
    > > Public Function InFiscalYear(dtInFiscalYear As Date, dtTest As Date) As
    > > Boolean
    > > Dim dtFYStart As Date
    > > Dim dtFYEnd As Date
    > >
    > > 'July 1 to June 30
    > > If Month(dtInFiscalYear) >= 7 Then
    > > dtFYStart = DateSerial(Year(dtInFiscalYear), 7, 1)
    > > dtFYEnd = DateSerial(Year(dtInFiscalYear) + 1, 6, 30)
    > > Else
    > > dtFYStart = DateSerial(Year(dtInFiscalYear) - 1, 7, 1)
    > > dtFYEnd = DateSerial(Year(dtInFiscalYear), 6, 30)
    > > End If
    > > If dtTest >= dtFYStart And dtTest <= dtFYEnd Then
    > > InFiscalYear = True
    > > Else
    > > InFiscalYear = False
    > > End If
    > > End Function
    > >
    > > or using SQL only with a search date from a form:
    > >
    > > 'SQL air code
    > >
    > > SELECT * FROM table WHERE [InvoiceDate] <=
    > > Forms!frmMain!DateInFiscalYear AND
    > > IIf(Month(Forms!frmMain!DateInFiscalYear) >= 7, [InvoiceDate] BETWEEN
    > > DateSerial(Year(Forms!frmMain!DateInFiscalYear), 7, 1) AND
    > > DateSerial(Year(Forms!frmMain!DateInFiscalYear) + 1, 6, 30),
    > > [InvoiceDate] BETWEEN DateSerial(Year(Forms!frmMain!DateInFiscalYear) -
    > > 1, 7, 1) AND DateSerial(Year(Forms!frmMain!DateInFiscalYear), 6, 30)) = -1
    > >
    > > These seem to do what you are asking for. Maybe a parameter in the
    > > query would be better than referencing the form field five times.
    > >
    > > James A. Fortune
    > > MPAPoster@FortuneJames.com
    > >
     
  6. James A. Fortune

    James A. Fortune
    Expand Collapse
    Guest

    ZigZagZak wrote:
    > Could I make the first query run....and have this query wait till its done,
    > then run.
    >
    > "ZigZagZak" wrote:
    >
    >
    >>ok I tried that a couple times, and a couple different ways and it works,
    >>BUT. It errors out sometimes. I think its because the "invoice date" in your
    >>sql I have being taken from results of another query, and it doesn't finish
    >>the update fast enough when theres alot of results for it to be used. Does
    >>this sound like what my problem is? And if so, how do I fix it.( I don't want
    >>to have to type in the date for the query, thats why I pointed to a date
    >>field in the other query) here is my sql.
    >>
    >>SELECT BOOKINGS.[PT BOOKED], BOOKINGS.[MH BOOKED], BOOKINGS.DATE,
    >>BOOKINGS.[TOTAL BOOKED], BOOKINGS.[PT TRANSFERED OUT], BOOKINGS.[MH
    >>TRANSFERED OUT], BOOKINGS.[SHEAVES TRANSERED OUT], BOOKINGS.[TOTAL ORDERS],
    >>BOOKINGS.[NUMBER PT ORDERS BOOKED], BOOKINGS.[NUMBER MH ORDERS BOOKED],
    >>BOOKINGS.[TOTAL SHIPPED], BOOKINGS.[TOTAL INVOICE], BOOKINGS.MONTH,
    >>BOOKINGS.YEAR, BOOKINGS.[MONTH NUMBER]
    >>FROM BOOKINGS
    >>WHERE (((BOOKINGS.DATE)<=[Forms]![BOOKINGS DAILY REPORT]![THE LAST DATE])
    >>AND ((IIf(Month([Forms]![BOOKINGS DAILY REPORT]![THE LAST DATE])>=7,[Date]
    >>Between DateSerial(Year([Forms]![BOOKINGS DAILY REPORT]![THE LAST DATE]),7,1)
    >>And DateSerial(Year([Forms]![BOOKINGS DAILY REPORT]![THE LAST
    >>DATE])+1,6,30),[DATE] Between DateSerial(Year([Forms]![BOOKINGS DAILY
    >>REPORT]![THE LAST DATE])-1,7,1) And DateSerial(Year([Forms]![BOOKINGS DAILY
    >>REPORT]![THE LAST DATE]),6,30)))=-1));


    I'm not sure how you're putting everything together so try running the
    queries one after another first. Note that Access is able to base one
    query on another and that when you do that Access will wait for the
    results of the initial query.

    James A. Fortune
    MPAPoster@FortuneJames.com
     
  7. ZigZagZak

    ZigZagZak
    Expand Collapse
    Guest

    Ok i got that to work by changing which field it looked to so it didn't have
    to wait for the query. Now I need the same thing but looking for the
    previous year. I don't really understand the sql statment enough to try to
    modify it. Thank you so much for all the help!!!!

    "James A. Fortune" wrote:

    > ZigZagZak wrote:
    > > Could I make the first query run....and have this query wait till its done,
    > > then run.
    > >
    > > "ZigZagZak" wrote:
    > >
    > >
    > >>ok I tried that a couple times, and a couple different ways and it works,
    > >>BUT. It errors out sometimes. I think its because the "invoice date" in your
    > >>sql I have being taken from results of another query, and it doesn't finish
    > >>the update fast enough when theres alot of results for it to be used. Does
    > >>this sound like what my problem is? And if so, how do I fix it.( I don't want
    > >>to have to type in the date for the query, thats why I pointed to a date
    > >>field in the other query) here is my sql.
    > >>
    > >>SELECT BOOKINGS.[PT BOOKED], BOOKINGS.[MH BOOKED], BOOKINGS.DATE,
    > >>BOOKINGS.[TOTAL BOOKED], BOOKINGS.[PT TRANSFERED OUT], BOOKINGS.[MH
    > >>TRANSFERED OUT], BOOKINGS.[SHEAVES TRANSERED OUT], BOOKINGS.[TOTAL ORDERS],
    > >>BOOKINGS.[NUMBER PT ORDERS BOOKED], BOOKINGS.[NUMBER MH ORDERS BOOKED],
    > >>BOOKINGS.[TOTAL SHIPPED], BOOKINGS.[TOTAL INVOICE], BOOKINGS.MONTH,
    > >>BOOKINGS.YEAR, BOOKINGS.[MONTH NUMBER]
    > >>FROM BOOKINGS
    > >>WHERE (((BOOKINGS.DATE)<=[Forms]![BOOKINGS DAILY REPORT]![THE LAST DATE])
    > >>AND ((IIf(Month([Forms]![BOOKINGS DAILY REPORT]![THE LAST DATE])>=7,[Date]
    > >>Between DateSerial(Year([Forms]![BOOKINGS DAILY REPORT]![THE LAST DATE]),7,1)
    > >>And DateSerial(Year([Forms]![BOOKINGS DAILY REPORT]![THE LAST
    > >>DATE])+1,6,30),[DATE] Between DateSerial(Year([Forms]![BOOKINGS DAILY
    > >>REPORT]![THE LAST DATE])-1,7,1) And DateSerial(Year([Forms]![BOOKINGS DAILY
    > >>REPORT]![THE LAST DATE]),6,30)))=-1));

    >
    > I'm not sure how you're putting everything together so try running the
    > queries one after another first. Note that Access is able to base one
    > query on another and that when you do that Access will wait for the
    > results of the initial query.
    >
    > James A. Fortune
    > MPAPoster@FortuneJames.com
    >
     
  8. James A. Fortune

    James A. Fortune
    Expand Collapse
    Guest

    ZigZagZak wrote:
    > Ok i got that to work by changing which field it looked to so it didn't have
    > to wait for the query. Now I need the same thing but looking for the
    > previous year. I don't really understand the sql statment enough to try to
    > modify it. Thank you so much for all the help!!!!
    >
    > "James A. Fortune" wrote:
    >
    >
    >>ZigZagZak wrote:
    >>
    >>>Could I make the first query run....and have this query wait till its done,
    >>>then run.
    >>>
    >>>"ZigZagZak" wrote:
    >>>
    >>>
    >>>
    >>>>ok I tried that a couple times, and a couple different ways and it works,
    >>>>BUT. It errors out sometimes. I think its because the "invoice date" in your
    >>>>sql I have being taken from results of another query, and it doesn't finish
    >>>>the update fast enough when theres alot of results for it to be used. Does
    >>>>this sound like what my problem is? And if so, how do I fix it.( I don't want
    >>>>to have to type in the date for the query, thats why I pointed to a date
    >>>>field in the other query) here is my sql.
    >>>>
    >>>>SELECT BOOKINGS.[PT BOOKED], BOOKINGS.[MH BOOKED], BOOKINGS.DATE,
    >>>>BOOKINGS.[TOTAL BOOKED], BOOKINGS.[PT TRANSFERED OUT], BOOKINGS.[MH
    >>>>TRANSFERED OUT], BOOKINGS.[SHEAVES TRANSERED OUT], BOOKINGS.[TOTAL ORDERS],
    >>>>BOOKINGS.[NUMBER PT ORDERS BOOKED], BOOKINGS.[NUMBER MH ORDERS BOOKED],
    >>>>BOOKINGS.[TOTAL SHIPPED], BOOKINGS.[TOTAL INVOICE], BOOKINGS.MONTH,
    >>>>BOOKINGS.YEAR, BOOKINGS.[MONTH NUMBER]
    >>>
    >>>>FROM BOOKINGS
    >>>
    >>>>WHERE (((BOOKINGS.DATE)<=[Forms]![BOOKINGS DAILY REPORT]![THE LAST DATE])
    >>>>AND ((IIf(Month([Forms]![BOOKINGS DAILY REPORT]![THE LAST DATE])>=7,[Date]
    >>>>Between DateSerial(Year([Forms]![BOOKINGS DAILY REPORT]![THE LAST DATE]),7,1)
    >>>>And DateSerial(Year([Forms]![BOOKINGS DAILY REPORT]![THE LAST
    >>>>DATE])+1,6,30),[DATE] Between DateSerial(Year([Forms]![BOOKINGS DAILY
    >>>>REPORT]![THE LAST DATE])-1,7,1) And DateSerial(Year([Forms]![BOOKINGS DAILY
    >>>>REPORT]![THE LAST DATE]),6,30)))=-1));

    >>
    >>I'm not sure how you're putting everything together so try running the
    >>queries one after another first. Note that Access is able to base one
    >>query on another and that when you do that Access will wait for the
    >>results of the initial query.
    >>
    >>James A. Fortune
    >>MPAPoster@FortuneJames.com
    >>


    I think all you need to do is use something like DateAdd("m", -12,
    dtInFiscalYear) instead of dtInFiscalYear. That should place the date
    into the prior fiscal year. Then if the function returns True the test
    date is in the prior fiscal year.

    James A. Fortune
    MPAPoster@FortuneJames.com
     
  9. ZigZagZak

    ZigZagZak
    Expand Collapse
    Guest

    Thanks again so much for all your help. I think I have everything I need to
    finish the project

    "James A. Fortune" wrote:

    > ZigZagZak wrote:
    > > Ok i got that to work by changing which field it looked to so it didn't have
    > > to wait for the query. Now I need the same thing but looking for the
    > > previous year. I don't really understand the sql statment enough to try to
    > > modify it. Thank you so much for all the help!!!!
    > >
    > > "James A. Fortune" wrote:
    > >
    > >
    > >>ZigZagZak wrote:
    > >>
    > >>>Could I make the first query run....and have this query wait till its done,
    > >>>then run.
    > >>>
    > >>>"ZigZagZak" wrote:
    > >>>
    > >>>
    > >>>
    > >>>>ok I tried that a couple times, and a couple different ways and it works,
    > >>>>BUT. It errors out sometimes. I think its because the "invoice date" in your
    > >>>>sql I have being taken from results of another query, and it doesn't finish
    > >>>>the update fast enough when theres alot of results for it to be used. Does
    > >>>>this sound like what my problem is? And if so, how do I fix it.( I don't want
    > >>>>to have to type in the date for the query, thats why I pointed to a date
    > >>>>field in the other query) here is my sql.
    > >>>>
    > >>>>SELECT BOOKINGS.[PT BOOKED], BOOKINGS.[MH BOOKED], BOOKINGS.DATE,
    > >>>>BOOKINGS.[TOTAL BOOKED], BOOKINGS.[PT TRANSFERED OUT], BOOKINGS.[MH
    > >>>>TRANSFERED OUT], BOOKINGS.[SHEAVES TRANSERED OUT], BOOKINGS.[TOTAL ORDERS],
    > >>>>BOOKINGS.[NUMBER PT ORDERS BOOKED], BOOKINGS.[NUMBER MH ORDERS BOOKED],
    > >>>>BOOKINGS.[TOTAL SHIPPED], BOOKINGS.[TOTAL INVOICE], BOOKINGS.MONTH,
    > >>>>BOOKINGS.YEAR, BOOKINGS.[MONTH NUMBER]
    > >>>
    > >>>>FROM BOOKINGS
    > >>>
    > >>>>WHERE (((BOOKINGS.DATE)<=[Forms]![BOOKINGS DAILY REPORT]![THE LAST DATE])
    > >>>>AND ((IIf(Month([Forms]![BOOKINGS DAILY REPORT]![THE LAST DATE])>=7,[Date]
    > >>>>Between DateSerial(Year([Forms]![BOOKINGS DAILY REPORT]![THE LAST DATE]),7,1)
    > >>>>And DateSerial(Year([Forms]![BOOKINGS DAILY REPORT]![THE LAST
    > >>>>DATE])+1,6,30),[DATE] Between DateSerial(Year([Forms]![BOOKINGS DAILY
    > >>>>REPORT]![THE LAST DATE])-1,7,1) And DateSerial(Year([Forms]![BOOKINGS DAILY
    > >>>>REPORT]![THE LAST DATE]),6,30)))=-1));
    > >>
    > >>I'm not sure how you're putting everything together so try running the
    > >>queries one after another first. Note that Access is able to base one
    > >>query on another and that when you do that Access will wait for the
    > >>results of the initial query.
    > >>
    > >>James A. Fortune
    > >>MPAPoster@FortuneJames.com
    > >>

    >
    > I think all you need to do is use something like DateAdd("m", -12,
    > dtInFiscalYear) instead of dtInFiscalYear. That should place the date
    > into the prior fiscal year. Then if the function returns True the test
    > date is in the prior fiscal year.
    >
    > James A. Fortune
    > MPAPoster@FortuneJames.com
    >
     

Share This Page