Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2014
    Posts
    9

    Unanswered: #Name? error when using Iff statement

    Hello new to access and developing my skills for work required at my job.

    I have a list of user inputs, CTR(1-40)H(Hours) and CTR(1-40)S(Status), Hours are a general number and Status is one of four options from a combo list;
    Proposed - With Tender
    Proposed - Additional Work
    Agreed - Initial Conditions
    Agreed - Initial Work.
    The equation I used is to check if the status is "Proposed - With Tender", and if it is to add the hours [CTR(1-40)H].
    This is the equation I am using, Only up to 10 so as to not take too much space, but it repeats the same way for the last 30 items.

    =IIf([CTR1S]="Proposed - With Tender",[CTR1H],0)+IIf([CTR2S]="Proposed - With Tender",[CTR2H],0)+IIf([CTR3S]="Proposed - With Tender",[CTR3H],0)+IIf([CTR4S]="Proposed - With Tender",[CTR4H],0)+IIf([CTR5S]="Proposed - With Tender",[CTR5H],0)+IIf([CTR6S]="Proposed - With Tender",[CTR6H],0)+IIf([CTR7S]="Proposed - With Tender",[CTR7H],0)+IIf([CTR8S]="Proposed - With Tender",[CTR8H],0)+IIf([CTR9S]="Proposed - With Tender",[CTR9H],0)+IIf([CTR10S]="Proposed - With Tender",[CTR10H],0)

    Each of the text boxes name are correct as I have check them but cannot find where my error is. I have run this expression before on another form;

    =IIf([1STAtus]="Proposed - With Tender",[1CTRH],0)

    And it worked however it is not now.

    All and any help is greatly appreciated.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    short of going through each iif statement I don't think there is an easy way of resolving what is wrong
    what id suggest you do is start with a single IIF, then add others in till you find the fault

    #name usually means that access runtime cannot resolve the name of a column or function in the code.
    as you are unable to identify the iif that is reporting the fault then I doubt there is much anyone else can do.
    check each iif statement to make certain its referrign to a valid colun name77you ay be able to diagnose which iif is failing if when the code breaks you move the mouse over each statement in turn
    and/or whilst the code is halted run each iif in the immediate window
    eg
    ?IIf([CTR3S]="Proposed - With Tender",[CTR3H],0)
    repeat for each iif in the statement till you find the error

    Hours are a general number
    is meaningless, thats a display format NOT a datatype

    check you aren't using a reserved word or symbol in your column names. Im highly suspicious of [CTR(1-40)H]
    Access 2007 reserved words and symbols - Access
    to be hinest I wasnt aware you could use a bracket in a column name
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Aug 2014
    Posts
    9
    Thank you, I will go through adding each statement and hopefully I will find the fault. The CTR(1-40)H was just me saying that numbers go from 1-40 ie CTR1H, CTR2H,....

    I will also check the reserved words for any contradictions

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Using a single IIF can be problematical, because even when the Expression evaluates to the TruePart, Access continues on and evaluates the FalsePart, as well, and this can lead to unexpected errors.

    Nested IIFs, because of their complexity, are even more prone to problems, and this complexity makes eyeballing them, to pinpoint the cause of a problem, even more so.

    Quote Originally Posted by intern91 View Post

    ...This is the equation I am using, Only up to 10 so as to not take too much space, but it repeats the same way for the last 30 items...
    Does it? How do we know that? You're asking us to find the error in your code, but you're only posting 1/4 of that code! What if the error is in the non-posted, 3/4 of the code?

    You have to see the problem with this. The error could be something as simple as a misplaced/dropped comma, double-quotation mark or bracket, but we have no way to know that, without having the entire code before us.

    And lastly, you apparently have 80+ Fields in the Table, and that, plus the fact that you have names like [CTR1H], [CTR2H], [CTR3H] etc and [CTR2S], [CTR3S], [CTR4S], tends to indicate that your database is almost certainly not normalized.

    Serial Field names, like[CTR1H], [CTR2H], [CTR3H] etc, are appropriate in a Spreadsheet, but seldom in a Relational Database.

    Linq ;0)>
    Last edited by Missinglinq; 08-17-14 at 00:45.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Tags for this Thread

Posting Permissions

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