445,704 Members | 1,861 Online
Need help? Post your question and get tips & solutions from a community of 445,704 IT Pros & Developers. It's quick & easy.

 P: 8 I want to create if statements to arrange days of the week from Monday through Sunday for course list. The day are in this format M for Monday, T for Tuesday etc. I have written 1 statement i.e DAY_OF_WEEK: IIf([CT_MTG.DAYS]='M',"MONDAY") but I need 6 more for the rest of the week! Please help. Sep 4 '07 #1
6 Replies

 Expert Mod 10K+ P: 12,389 Create a lookup table instead and join it to that table. Sep 4 '07 #2

 P: 8 Create a lookup table instead and join it to that table. I do not understand what a lookup table is and or how it will work. Is that the easier way to do it or the iif statement? Sep 5 '07 #3

 Expert 100+ P: 126 The structure of an IIF statement is IIF(boolean test, value if true, value if false). Modify your existing IIF statement to have another IIF statement as the false value. For example: Expand|Select|Wrap|Line Numbers IIf([CT_MTG.DAYS]='M',"MONDAY",IIf([CT_MTG.DAYS]='T', "Tuesday",IIf([CT_MTG.DAYS]='W',"WEDNESDAY"))) etc. I'm not writing the whole thing because it's a simple task, and I'm not sure how you represent the differences between Tuesday/Thursday and Saturday/Sunday with one letter. Good luck. Sep 5 '07 #4

 P: 8 The structure of an IIF statement is IIF(boolean test, value if true, value if false). Modify your existing IIF statement to have another IIF statement as the false value. For example: Expand|Select|Wrap|Line Numbers IIf([CT_MTG.DAYS]='M',"MONDAY",IIf([CT_MTG.DAYS]='T',"Tuesday",IIf([CT_MTG.DAYS]='W',"WEDNESDAY"))) etc. I'm not writing the whole thing because it's a simple task, and I'm not sure how you represent the differences between Tuesday/Thursday and Saturday/Sunday with one letter. Good luck. Thanks for the reply. The there are tow column in the table with days. One column (DAYS) has them entered in this format MTWHF(for Mon, Tue, Wed, Thuand Fri), W, H, T H(for Tue and Thur). There is also column for each day by day name eg MON, TUE, WED, THU, FRI, SAT. If a course if taught say on Monday, the column MON has Y and if the same course is taught on Thursday the column THU has a Y, etc. So when I write the iif statement it is only converting the M on DAYS but if there is also T H W, the iif statement does not convert Wednesday and Thursday. So what should I do so the iif statement can convert the DAYS eg MTWHF to MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY? Sep 5 '07 #5

 Expert Mod 10K+ P: 12,389 Why are you replicating your data? Why have multiple ways of representing the same data? A lookup table is just a table with two fields (usually). One is the coded value. The other is the full value. It's called a lookup table simply because of its use, which is to translate the coded value into the full value. In the query you just join the coded value field in the two tables. Sep 5 '07 #6

 Expert Mod 10K+ P: 12,389 Resuscribing . Sep 5 '07 #7