Query and IFF function
I need to create a new field called "Quarters" in my query. Quarter identified as 1st, 2nd, 3rd, and 4th as divided into the 4 quarters in the year for the respective periods.
January, february and march: 1st
April, may and june: 2nd
July, august and september: 3rd
October, november and december: 4th
How would I do this using the IFF function?
*note: the IFF function should allow for all 4 quarters, even if the table data may not span the 4 quarters
Thank you in advance
A little more detail is needed. What fields do you have that need to be analyzed with the IIF function, what results do you want in the new field. Are you sure you only want one new field and not four (one for each quarter)? Perhaps if you shared your business story on what you are doing it would help.
Alan
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
The field to be analyzed is "Flight_Date". The results i would want in my new field is the quarter the date belongs to. Sorry i am very new to access and I'm not sure I am being clear.
Fight_Date
01-05-2013
03-05-2013
05-05-2013
06-05-2013
07-05-2013
10-05-2013
10-05-2013
11-05-2013
12-05-2013
13-05-2013
14-05-2013
15-05-2013
16-05-2013
17-05-2013
18-05-2013
19-05-2013
21-05-2013
22-05-2013
23-05-2013
24-05-2013
24-05-2013
25-05-2013
25-05-2013
27-05-2013
28-05-2013
29-05-2013
30-05-2013
30-05-2013
01-06-2013
01-06-2013
02-06-2013
04-06-2013
05-06-2013
07-06-2013
08-06-2013
08-06-2013
09-06-2013
09-06-2013
09-06-2013
10-06-2013
10-06-2013
10-06-2013
11-06-2013
12-06-2013
12-06-2013
12-06-2013
12-06-2013
14-06-2013
17-06-2013
19-06-2013
20-06-2013
21-06-2013
21-06-2013
21-06-2013
22-06-2013
22-06-2013
23-06-2013
23-06-2013
24-06-2013
24-06-2013
25-06-2013
25-06-2013
25-06-2013
26-06-2013
26-06-2013
27-06-2013
27-06-2013
28-06-2013
29-06-2013
01-07-2013
02-07-2013
03-07-2013
04-07-2013
04-07-2013
05-07-2013
05-07-2013
05-07-2013
06-07-2013
07-07-2013
07-07-2013
08-07-2013
08-07-2013
09-07-2013
10-07-2013
10-07-2013
11-07-2013
12-07-2013
12-07-2013
12-07-2013
13-07-2013
13-07-2013
13-07-2013
14-07-2013
15-07-2013
15-07-2013
18-07-2013
18-07-2013
18-07-2013
19-07-2013
19-07-2013
20-07-2013
20-07-2013
21-07-2013
25-07-2013
25-07-2013
26-07-2013
27-07-2013
29-07-2013
30-07-2013
30-07-2013
31-07-2013
01-08-2013
01-08-2013
02-08-2013
03-08-2013
05-08-2013
05-08-2013
05-08-2013
06-08-2013
07-08-2013
07-08-2013
07-08-2013
08-08-2013
08-08-2013
09-08-2013
Originally Posted by alansidman
Try this:
=iif([Flight_Date]>#12/31/2012# and [Flight_Date]<#4/1/2013#,"1st",iif([Flight_Date]>#3/31/2013# and [Flight_Date]<#7/1/2013#,"2nd",iif([Flight_Date]>#6/30/2013# and [Flight_Date]<#10/1/2013#,"3rd","4th")))
It has worked however it indicates that all dates in Flight_date are in the 4th quarter? I'm not sure if this is correct.
I didn't test it because you have used Canada/European format and when I copied and tried to paste into a table they got all messed up as I am on US date format. If you put the dates in an Excel spreadsheet and upload and put them in US format, I will repair and make sure it works.
Ok. Here are the dates in US format. Thank you so much for your help.
Fight_Date
05-01-13
05-03-13
05-05-13
05-06-13
05-07-13
05-10-13
05-10-13
05-11-13
05-12-13
05-13-13
05-14-13
05-15-13
05-16-13
05-17-13
05-18-13
05-19-13
05-21-13
05-22-13
05-23-13
05-24-13
05-24-13
05-25-13
05-25-13
05-27-13
05-28-13
05-29-13
05-30-13
05-30-13
06-01-13
06-01-13
06-02-13
06-04-13
06-05-13
06-07-13
06-08-13
06-08-13
06-09-13
06-09-13
06-09-13
06-10-13
06-10-13
06-10-13
06-11-13
06-12-13
06-12-13
06-12-13
06-12-13
06-14-13
06-17-13
06-19-13
06-20-13
06-21-13
06-21-13
06-21-13
06-22-13
06-22-13
06-23-13
06-23-13
06-24-13
06-24-13
06-25-13
06-25-13
06-25-13
06-26-13
06-26-13
06-27-13
06-27-13
06-28-13
06-29-13
07-01-13
07-02-13
07-03-13
07-04-13
07-04-13
07-05-13
07-05-13
07-05-13
07-06-13
07-07-13
07-07-13
07-08-13
07-08-13
07-09-13
07-10-13
07-10-13
07-11-13
07-12-13
07-12-13
07-12-13
07-13-13
07-13-13
07-13-13
07-14-13
07-15-13
07-15-13
07-18-13
07-18-13
07-18-13
07-19-13
07-19-13
07-20-13
07-20-13
07-21-13
07-25-13
07-25-13
07-26-13
07-27-13
07-29-13
07-30-13
07-30-13
07-31-13
08-01-13
08-01-13
08-02-13
08-03-13
08-05-13
08-05-13
08-05-13
08-06-13
08-07-13
08-07-13
08-07-13
08-08-13
08-08-13
08-09-13
Just created a test db and tested. Works for me. Try changing the dates in the IIF statement to Canadian format.
See attached.
yes it worked! thank you so much!
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.
Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.
Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!
Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).
Posted at: http://forums.aspfree.com/microsoft-...ml#post1004254
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks