FY dates vs. CY dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am working on a report that has order dates. (these dates are calendar
dates - CY). Our company goes by a 4/4/5 week Fiscal calendar year.
Example: January is 12/24/05 - 01/28/06. How do I set my dates to list
January orders with the Fiscal dates.
 
I am working on a report that has order dates. (these dates are calendar
dates - CY). Our company goes by a 4/4/5 week Fiscal calendar year.
Example: January is 12/24/05 - 01/28/06. How do I set my dates to list
January orders with the Fiscal dates.

I'd suggest setting up a Fiscal Year table (perhaps using fill-down in
Excel for convenience) with the startdate, enddate, and fiscal month
name, from now for the next five or ten years. You can then use a "Non
Equi Join" query:

SELECT <whatever fields you want>, FiscalYear.FiscalMonth
FROM Orders INNER JOIN FiscalYear
ON Orders.OrderDate >= FiscalYear.StartDate
AND Orders.OrderDate <= FiscalYear.EndDate;


John W. Vinson[MVP]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Calculate a year to date 6
Code Optimization 0
iff statement 15
Count the Number of Weekdays by Month 3
Date calculations 5
Adding prompt responses as as a field value 3
Fiscal year 2
Reporting in Access 11

Back
Top