Closing bracket or verticle bar missing

[MGAllen]

I've come this far trying to create an if statement as I do not know VBA. Can someone help me determine how and where to place the missing verticle bar? I'm not even sure I know where to locate such on the keyboard ;-0
Thank you for your help!
Mel


IIf(If[TERM]= '2122', 'Spring 2012',IIf([TERM]= '2114', 'Fall 2011', IIf([TERM]= '2113', 'Sumemr 2011', '-'))

 

[plog]

When you get that nested with IIF statements its time to either make a function or create a table. You need a table called 'Terms' that looks like this:

TermNum, TermName
2122, "Spring 2012"
2114, "Fall 2011"
2113, "Summer 2011"

You would then bring that table into your query, link the TermNum field of it to the Term field in your other table, change the relationship to 'Show all from Your Original Table name', then create a field like this:

IIf(IsNull(TermNum), "-", TermName)

This has the added benefit of being able to easily accomodate more terms than just the 3 you've noted. Simply add the new term and its number to the Terms table--no messing with the query required.

 

[boblarson]

A couple of problems with this.

1. You have an IF in there instead of IIF

2. There is no ( after IF which should be IIF(

3. Summer is misspelled

4. You don't have enough ending parens )

So, the code should be

IIf(IIf([TERM]= '2122', 'Spring 2012',IIf([TERM]= '2114', 'Fall 2011', IIf([TERM]= '2113', 'Summer 2011', '-'))))

 

[boblarson]

And I agree with Plog on that one. But I did want to point out what you had wrong so you know for the future.

 

[G37Sam]

Yeah it would be a lot easier to store the values in a table and look them up using the life saving dlookup() function.

 

[MGAllen]

As you can see, I'm not an expert and appreciate both suggestions. I have created the additional table and linked the fields - WONDERFUL SUGGESTIONS EVERYONE! Again THANKS!
Mel