Help with calculated field in Access report

[m-dash]

I'm *hoping* that someone can help me with a problem that I'm having trying to create a report format in Access XP. (I'm a "basic" Access user who has a small amount of experience, but am delving into new waters here.)

What I'm trying to do is print a report of our department's AV inventory titles, where each title in the Access table contains a field with a code designating which course level in our progam is the primary user of that title. On the report, I've set it to group the titles by course level, and I would like to have a text box in the header for each section (group) that identifies the name of the primary course, based on the contents of the field designating the primary course, which is called NSG_LEVEL.

I've pored into the depths of the Access help files, and found information on using both the IIf function and the If-Then-Else statements. Based on my needs, the If-Then-Else statements seems to be what I need, in order to specify the contents of the text box label, depending on the contents of the NSG_LEVEL field. But, my attempts so far have failed. (The IIf function I've experimented with seems to work, but the function doesn't seem to be able to handle enough possibilities to suit my needs, being an either-or type of thing.) I put together an If-Then-Else statement that follows the example in the help file, and the first version generated a syntax error message when attempting to generate the report, and the latest version seems to be ignored by Access (no syntax error message), which simply displays the contents of the NSG_LEVEL field in the text box, instead of the text label desired.

In case anyone can point me in the right direction, below is the current version of the If-Then-Else statement that I've typed into the control source area of the properties dialog of the text box, named Text48:

= IF NSG_LEVEL = "N1R" THEN
REPORTS![AV Titles in Course Manuals]![Text48] = "NURSING I"
ELSEIF NSG_LEVEL = "N2R" THEN
REPORTS![AV Titles in Course Manuals]![Text48] = "NURSING II"
ELSEIF NSG_LEVEL = "N3R" THEN
REPORTS![AV Titles in Course Manuals]![Text48] = "NURSING III"
ELSEIF NSG_LEVEL = "N4R" THEN
REPORTS![AV Titles in Course Manuals]![Text48] = "NURSING IV"
ELSEIF NSG_LEVEL = "N5R" THEN
REPORTS![AV Titles in Course Manuals]![Text48] = "NURSING V"
ELSEIF NSG_LEVEL = "NUR" THEN
REPORTS![AV Titles in Course Manuals]![Text48] = "NUTRITION"
ELSE
REPORTS![AV Titles in Course Manuals]![Text48] = "PHARMACOLOGY"
END IF


I'm new to this stuff, and I'm hoping there is something that I'm missing that, if added to this, will help produce the results I'm after. I've just about exhausted everything that I can make sense of in the Access help file, so if anyone can help, you will have my undying gratitude!

 

[Ziggy1]

Try this, I had it working with my data so I hope I didn't omit any bracketing when I edited.



=IIf([NSG_LEVEL ]="N1R","NURSING I",IIf([NSG_LEVEL ]="N2R","NURSING II"
,IIf([NSG_LEVEL ]="N3R","Nursing III",IIf([NSG_LEVEL ]="N4R","Nursing IV",IIf([NSG_LEVEL ]="N5R","Nursing V",IIf([number]="NUR","Nutrition"","Pharmacology"))))))

 

[m-dash]

Ziggy,

I pasted your code into the control source box and tried it... although I had my hopes up, it didn't do what I wanted. Weird... sometimes when I try to preview the report, Access gives me a syntax error, sometimes it shows only the contents of the field (course code), and sometimes it shows a blank text box!

I thought from what I'd read in the help file that an Iff statement could only be used for those cases where there are two possible outcomes, not more -- which is why I was trying to use the If-Then-Else form. What does the [number] designation in the code mean? I also tried replacing this with NSG_LEVEL instead, but with the same results.

I appreciate your help anyway!

m-dash

 

[Ziggy1]

oops, I tested the expression using my fields and then changed it to include yours, so just change the [Number] to [NSG_LEVEL].

This is a "Nested IIF" Statement.

IIF(test,True,IIF(test,True,False))




=IIf([NSG_LEVEL ]="N1R","NURSING I",IIf([NSG_LEVEL ]="N2R","NURSING II"
,IIf([NSG_LEVEL ]="N3R","Nursing III",IIf([NSG_LEVEL ]="N4R","Nursing IV",IIf([NSG_LEVEL ]="N5R","Nursing V",IIf([NSG_LEVEL ]="NUR","Nutrition"","Pharmacology"))))))

 

[m-dash]

Ziggy, I tried pasting the edited code again, still with the same results -- either blank text box, just the course code itself, or a syntax error message.

But, I did find out how to get what I want... an Access user on another forum suggested that I add another table containing just the course codes and associated course names, and add this new table to the query the report format is based on, then simply choose the course name field to insert into the text box.

I had actually tried that route *before* finding out about the "if" statements, but got confused about defining a relationship between tables, and was afraid that adding a table would somehow mess up my current table. After reading this other guy's posts, I made some mental connections that helped to piece together what I'd read in the help file... (I've never used relational capabilities before, and have recently imported my data over from a Dbase file that I created about 13 years ago, using Dbase ever since then to maintain and update the information. When I still had my old computer at work using Access 97, I had tried importing the file, but Access said my data was "corrupt"... but when I tried using my new computer with Access XP, it worked like a charm )

Thank you for your time trying to help me -- I appreciate it very much!

m-dash