date choice

[pastorkc]

Is there a way in access either by query or report to have the user select a date range before the query is run? For example the user wants to run a report for a specific fiscal year.

 

[Joe4]

What do you mean "combined query"?
Are you combining it with something else?

 

[pastorkc]

I did a union between my two tables and it was working but when I replaced it with the new code it stopped working, but the others are working with the new code. I updated the code for the union query but it isn't working.

 

[Joe4]

In your UNION queries, do you have the exact same field names in the exact same order, in each of the two queries you are trying to join together?
Also, do you have any blank or error values in your Date field in each underlying query?

 

[pastorkc]

yes exactly the same order and same fields. No blank or error fields.

 

[Joe4]

What are the names of the two underlying queries and the Union query?
Are they all found in the database you uploaded?

 

[pastorkc]

Yes they are. One is Combined Query. The two that are linked are Fiscal_Year-Fixed and Fiscal_Year-TODD.

 

[Joe4]

Please change your Union query to SQL view and copy and paste the SQL code here.

 

[pastorkc]

SELECT Category.*, Year([Date]) AS CalendarYear, Month([Date]) AS CalendarMonth, IIf(Month([Date])<7,Year([Date]),Year([Date])+1) AS Fiscal_Year, MonthName([CalendarMonth],False) AS MonthName
FROM Category
WHERE (((IIf(Month([Date])<7,Year([Date]),Year([Date])+1))>=[Start Fiscal Year] And (IIf(Month([Date])<7,Year([Date]),Year([Date])+1))<=[End Fiscal Year]))
ORDER BY Month([Date])

UNION

SELECT [TODD Category].*, Year([Date]) AS CalendarYear, Month([Date]) AS CalendarMonth, IIf(Month([Date])<7,Year([Date]),Year([Date])+1) AS Fiscal_Year, MonthName([CalendarMonth],False) AS MonthName
FROM [TODD Category]
WHERE (((IIf(Month([Date])<7,Year([Date]),Year([Date])+1))>=[Start Fiscal Year] And (IIf(Month([Date])<7,Year([Date]),Year([Date])+1))<=[End Fiscal Year]))
ORDER BY Month([Date]);

 

[Joe4]

Try replacing the current SQL code with this:

SELECT Category.*, Year([Date]) AS CalendarYear, Month([Date]) AS CalendarMonth, IIf(Month([Date])<7,Year([Date]),Year([Date])+1) AS Fiscal_Year, MonthName([CalendarMonth],False) AS MonthName
FROM Category
WHERE (((IIf(Month([Date])<7,Year([Date]),Year([Date])+1))>=[Start Fiscal Year] And (IIf(Month([Date])<7,Year([Date]),Year([Date])+1))<=[End Fiscal Year]))
UNION
SELECT [TODD Category].*, Year([Date]) AS CalendarYear, Month([Date]) AS CalendarMonth, IIf(Month([Date])<7,Year([Date]),Year([Date])+1) AS Fiscal_Year, MonthName([CalendarMonth],False) AS MonthName
FROM [TODD Category]
WHERE (((IIf(Month([Date])<7,Year([Date]),Year([Date])+1))>=[Start Fiscal Year] And (IIf(Month([Date])<7,Year([Date]),Year([Date])+1))<=[End Fiscal Year]))
ORDER BY 8;

 

[pastorkc]

Try replacing the current SQL code with this:

SELECT Category.*, Year([Date]) AS CalendarYear, Month([Date]) AS CalendarMonth, IIf(Month([Date])<7,Year([Date]),Year([Date])+1) AS Fiscal_Year, MonthName([CalendarMonth],False) AS MonthName
FROM Category
WHERE (((IIf(Month([Date])<7,Year([Date]),Year([Date])+1))>=[Start Fiscal Year] And (IIf(Month([Date])<7,Year([Date]),Year([Date])+1))<=[End Fiscal Year]))
UNION
SELECT [TODD Category].*, Year([Date]) AS CalendarYear, Month([Date]) AS CalendarMonth, IIf(Month([Date])<7,Year([Date]),Year([Date])+1) AS Fiscal_Year, MonthName([CalendarMonth],False) AS MonthName
FROM [TODD Category]
WHERE (((IIf(Month([Date])<7,Year([Date]),Year([Date])+1))>=[Start Fiscal Year] And (IIf(Month([Date])<7,Year([Date]),Year([Date])+1))<=[End Fiscal Year]))
ORDER BY 8;

That worked, Thank you. Can I ask why you put ORDER BY with an 8?