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.

 

[pastorkc]

Fiscal Year Fixed

 

[Joe4]

I am not sure if you have hidden things, or my security is clamping down on it, but I am having trouble exposing the Tables and Queries. I am seeing mostly forms and reports.

 

[pastorkc]

OK yes, I had the tables and queries hidden. I unhid them for you.

 

[Joe4]

OK, it has been a long time since I have used Parameter Queries.
I think it may not like the fact that it is a calculated field. I will need to play round with it.

The other thing I have done in the past is to make a selection Form, where they enter in all the criteria, the use VBA to dynamically build the SQL code for the query behind the scenes. A bit more labor intensive, but much more dynamic and flexible.

 

[pastorkc]

OK, not sure how to do that. I a, just getting back into Access and alot has changed.

 

[Joe4]

Actually, I figured it out. The issue was that your Fiscal_Year calculated field is using ANOTHER calculated field (CalendarMonth).
Access doesn't like the imbedded calculation functions. But it doesn't really need it.

If you change your Fiscal_Year calculated field from:

Fiscal_Year: IIf([CalendarMonth]<7,Year([Date]),Year([Date])+1)

to:

Fiscal_Year: IIf(Month([Date])<7,Year([Date]),Year([Date])+1)

it will give your the EXACT same value, but then allow you Parameter Query to work properly.

 

[pastorkc]

Actually, I figured it out. The issue was that your Fiscal_Year calculated field is using ANOTHER calculated field (CalendarMonth).
Access doesn't like the imbedded calculation functions. But it doesn't really need it.

If you change your Fiscal_Year calculated field from:

Fiscal_Year: IIf([CalendarMonth]<7,Year([Date]),Year([Date])+1)

to:

Fiscal_Year: IIf(Month([Date])<7,Year([Date]),Year([Date])+1)

it will give your the EXACT same value, but then allow you Parameter Query to work properly.

Thank you. Then do I do away with the Calendar Month calculation?

 

[Joe4]

No, there is no harm in leaving it if you want to see it.

Just remember that when putting in parameter for a parameter query, if you are putting them on a calculated field, it is best to do all the calculations straight on that field, and not reference other calculated fields within that calculated field.

 

[pastorkc]

That worked perfect, thank you so much

 

[pastorkc]

ok I fixed those queries with that calculated field. But now my combined query is coming back with this error and I fixed the calculation in SQL view.