Fiscal Quarters in a query

[brownsugar7210]

Hi, I'm an Access novice. My issue is 2-fold. I have query with one of its field called "Start Date". I need to create parameter query that outputs a specific quarter (Ex Qtr 1, 2, 3, or 4) based on the quarter # entered as well as ask the user to input the year as well...so the query will produce on the QTR and the corresponding year. Hope this makes sense...can you assist pls. ps both parameters uses the "start date" field.

[alansidman]

Try this:

Select yourtable.yourfield1, yourfield2,......etc
From Yourtable
WHERE ((yourtable.StartDate = Year([Enter Year])) and (yourtable.Quarter = [Enter Quarter]));

I think that you need another field that has the quarter value. Based upon the current information you have supplied I see no other way. This may be a calculated field in your query using a nested IIF statement.

[brownsugar7210]

Thanks for your insight. I did a bit of research on trying to create a field that would determine the "quarter value" and I came up with this (which of course is flawed) Can you help me troubleshoot?

Quarter: IIf(Month([Start Date]) In (1,2,3),"1",IIf(Month([Start Date]) In (4,5,6),"2",IIf(Month([Start Date]) In (7,8,9),"3",IIf(Month([Start Date]) In (10,11,12),"4")))]
I've attached a snapshot of the qry I'm working with...many thanks in advance.

[alansidman]

I think that you would be better served using "Or" instead.

=iif(Month([Start Date]= 1 or Month([Start Date]=2 or Month([Start Date]=3, "1",iif(Month([Start Date]=4 or Month([Start Date]=5 etc..........

Alan

[brownsugar7210]

Here's what I have - I'm getting an syntax error.
=iif(Month([Start Date]= 1 or Month([Start Date]=2 or Month([Start Date]=3, "1",iif(Month([Start Date]=4 or Month([Start Date]=5, Month([Start Date]=6 “2” ,iif(Month([Start Date]= 7 or Month([Start Date]=8 or Month([Start Date]=9, "3",iif(Month([Start Date]= 10 or Month([Start Date]=11 or Month([Start Date]=12, "4")))

[alansidman]

Here's what I have - I'm getting an syntax error.
=iif(Month([Start Date]= 1 or Month([Start Date]=2 or Month([Start Date]=3, "1",iif(Month([Start Date]=4 or Month([Start Date]=5, Month([Start Date]=6, “2” ,iif(Month([Start Date]= 7 or Month([Start Date]=8 or Month([Start Date]=9, "3",iif(Month([Start Date]= 10 or Month([Start Date]=11 or Month([Start Date]=12, "4")))

Need a comma after the 6. I have highlighted in Blue.

[brownsugar7210]

Alan, Sorry to me a pest...i'm still having difficulty...Here's what i'm doing:

I've added 2 columns to the query (attached in the previous thread).

In the first column under "field" I have the following:
Select Events.Start Date, From EventsWHERE ((Events.StartDate = Year([Enter Year])) and (yourtable.Quarter = [Enter Quarter]));and this in the second column:

1. Quarter=iif(Month([Start Date]= 1 orMonth([Start Date]=2 or Month([Start Date]=3, "1",iif(Month([StartDate]=4 or Month([Start Date]=5, Month([Start Date]=6, “2” ,iif(Month([StartDate]= 7 or Month([Start Date]=8 or Month([Start Date]=9,"3",iif(Month([Start Date]= 10 or Month([Start Date]=11 orMonth([Start Date]=12, "4")))

Please please provide some guidance...thanks again!

[alansidman]

Here is a sample db, that someone else had a similar issue with. Look at Query two as to how I handled it. FYI, Query 1 sums the data by quarter.

In the criteria for the quarter, you can set which quarters you want as well as which years in the Year field.

Keep it Simple

[brownsugar7210]

Thank you for the DB....it helped...ALOT!! one more thing: I'm using the following YY: Year([Invoice Date]) and QQ: Choose(Month([Start Date]),1,1,1,2,2,2,3,3,3,4,4,4) in the query. My only issue is its outputting every quarter for the year and I only need a specific quarter (this will be inputed by the user as well as the year. I need the year to be displayed as well instead of the #s 0, -1 etc. Thank you sooooo much for hanging with me this...i truely appreciate your assistance.

[alansidman]

I need the year to be displayed as well instead of the #s 0, -1 etc.

I don't understand what you are asking. In the field YY, you should be getting the full year. Where is the 0 and -1 coming from. Sounds like a Boolean format, True/False.

[brownsugar7210]

I figured it out. You have been a tremendous help...Thank you so much!!!!