Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2003
    Posts
    300

    Unhappy Unanswered: IIF statement not working

    I have the following IIF statement:

    IIF([Name] Like"4*",[Name],"N/A")

    the Name field in the table is text and the data is alpha + numbers, so if the data is 4000 for example, I want it to print 4000 on the line on the report...
    If the data is TOTAL then I want it to print N/A.

    It is not working properly, all it will print out is N/A on ONE line and then blanks on all the rest...

    HELP!!

  2. #2
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    Look at "DemoIIfNameA2000.mdb"
    Report1, Report2.
    Attached Files Attached Files

  3. #3
    Join Date
    Nov 2003
    Posts
    300
    Thank you.. I realized what I did wrong.. the field is not coming from a table, it is from a query...

    But now I am getting #error in every field on the report.. no data, just #error..

    now what??

  4. #4
    Join Date
    May 2006
    Posts
    6

    Look at the Properties!

    This is a common misunderstanding about MS Access. When a report is built by the report wizard it makes the textbox name and the data name the same. When you change the data value to be a formula, EVERYONE forgets that they MUST change the object name to something 'neutral', like txbValue121. Otherwise internally access creates something like circular references.
    Go to design mode, right click on the control and select properties. Select the Other tab and change the name property to ANYTHING but [Name]! Congratulations on using the good practice of using braces around field names. It is a "best practice".

  5. #5
    Join Date
    Nov 2003
    Posts
    300
    Still no luck with this IIf statement. So I will give you as much info as I have.

    This is on a report, in the control source under the data tab of the text box:

    =IIf([Name] Like "4*",[Name],"N/A")

    However the result on the report shows N/A under the first detail record, which is wrong because the first name is 4000. And the other 5 detail records are all blank on the report eventhough they should be 4001, 4002, etc.

    Now if I change it to this:

    =IIf([Name] Like "*",[Name],"N/A")

    which should print the name which is 4000, instead it prints the name of the report in the first detail record and the rest are blank

    If I just leave [Name] in the control source it will print detail records

    4000
    Restricted
    Restricted
    4001
    4002 etc....

    Instead of Restricted or any other data printed, I want it to print N/A

    any thoughts?

  6. #6
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372

    Hello databasemon!

    Here is a new "DemoIIfNameA2000.mdb"
    Look at Query1, Open Query3.
    Attached Files Attached Files

  7. #7
    Join Date
    Nov 2003
    Posts
    300

    Got It!!

    Thanks very much for your input and suggestions!!=MStef-ZG and JavaDancer!!!

    I went back to the original query and found that [Name] existed in more than one table... I think that creates a circular reference.? I modified the query to add another field :

    NewName: IIf([Name] Like "4*",[Name],"N/A") {just like you suggested}

    and IT works!!!

    as I said, no luck trying to get it to work in the report.

    Again--Thanks!!

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    here's a list of A's reserved words
    ...including 'name'

    izy
    currently using SS 2008R2

  9. #9
    Join Date
    May 2006
    Posts
    6

    An IMPORTANT Lesson!

    Sorry I didn't catch the problem earlier!
    In a Report, and probably in a form also, a control by the name of "Name", even if you reference it by [Name] is NOT accessible!
    If you create a control with the ControlSource of =[Name] what will print is the Name property of the Report!!! Easy FIX --> re-name the field in the query. SELECT Name as MyName... then use =[MyName].

    Being able to specify a scope is very handy! Where name collisions like this occur it is ALWAYS nice to be able to specify which one you want!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •