Union Queries (1 Viewer)

[ssworthi]

I have been successful in joining 4 years of data through a union query (which I am very novice at) and off of that query I would like to categorize them by year (crop) and have written the following iif statement which is not producing CY08 records, they are reflecting CY09. I see CY10 & CY11 but just something I've done in the iif statement. Could you please advise my error:

CY: IIf([Pay Period End]<=#10/25/2008#,"CY08",IIf([Pay Period End]<=#10/24/2009#,"CY09",IIf([Pay Period End]<=#10/23/2011#,"CY10"IIf([Pay Period End]<=#4/23/2011#,"CY11"))))
Thank you so very much for any help!

 

[boblarson]

Why not just use:

CY:"CY" & Format([Pay Period End], "yy")

 

[ssworthi]

Thanks so much Bob; so are you saying I can just call the expression CY and not use iif's?
Like CY: "CY" & Format([Pay Period]<=#10/25/2008,"2008",<=#10/24/09","2009") etc.?

 

[boblarson]

Thanks so much Bob; so are you saying I can just call the expression CY and not use iif's?
Like CY: "CY" & Format([Pay Period]<=#10/25/2008,"2008",<=#10/24/09","2009") etc.?

Quick question as I may have misunderstood. Are you on a Fiscal year that runs from about 10/26 to 10/25 each year? Is that why you are chcking with the dates?

 

[ssworthi]

Thanks Bob. These are crop years from late september to the following (early to mid)October which is one growing year. In that year there are tasks that I hope to plot throughout each year and then compare hours/$$ over 3-4 years. (later exercise) Thanks so much for your help!

 

[boblarson]

Yes, then you would need an IIF but we should be able to make it more generic. So, is there a place in the database which defines the start date of the crop year?

 

[ssworthi]

I could make a table. Currently we downloaded from our labor system into a database into a table by crop year. ( I was pretty new at this when I started and no resources to learn from) so I did it by copy year. Then I was taught about union queries so now I have one data base that is separate which has each crop year table. (I'm being a bit wordy here, sorry) I have made the union query and get all the data but want to define which year it is cy CY09, etc. Again, thanks so much Bob, I am such a novice at this.

 

[cimathers]

If you decide to stick with the IIF statement:

I noticed there was a missing comma; as below.

Change this:

CY: IIf([Pay Period End]<=#10/25/2008#,"CY08",IIf([Pay Period End]<=#10/24/2009#,"CY09",IIf([Pay Period End]<=#10/23/2011#,"CY10"IIf([Pay Period End]<=#4/23/2011#,"CY11"))))

to this:

CY: IIf([Pay Period  End]<=#10/25/2008#,"CY08",IIf([Pay Period  End]<=#10/24/2009#,"CY09",IIf([Pay Period End]<=#10/23/2011#,"CY10",IIf([Pay Period End]<=#4/23/2011#,"CY11"))))

Hope this helps.