1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Help with calculated field in Access report

Discussion in 'Business Applications' started by m-dash, Apr 14, 2004.

Thread Status:
Not open for further replies.
Advertisement
  1. m-dash

    m-dash Thread Starter

    Joined:
    Nov 12, 2003
    Messages:
    91
    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!
     
  2. Ziggy1

    Ziggy1

    Joined:
    Jun 17, 2002
    Messages:
    2,551
    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"))))))
     
  3. m-dash

    m-dash Thread Starter

    Joined:
    Nov 12, 2003
    Messages:
    91
    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
     
  4. Ziggy1

    Ziggy1

    Joined:
    Jun 17, 2002
    Messages:
    2,551
    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"))))))
     
  5. m-dash

    m-dash Thread Starter

    Joined:
    Nov 12, 2003
    Messages:
    91
    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
     
  6. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/220287

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice