Results 1 to 14 of 14
  1. #1
    Join Date
    May 2009
    Location
    Crete, Greece
    Posts
    90

    Unanswered: Help please on IIf syntax

    Hi, I'm sure this is going to be a quick answer for somebody, would be grateful for any assistance.

    In a query, I need to do a calculation, based on the result of a calculation in the same query.

    [Total] is the existing calculation, and if total = 7, then I want it multiplied by 4, or if the total is 14, then I want it multiplied by 7.

    Here's what I got, and it doesn't work.

    =IIf([Total] = "7" *4, "14"*7)

    many, many thanks

  2. #2
    Join Date
    Feb 2004
    Posts
    40
    You will have to get rid of the quotes, this is text which can not be used by a calculation.
    If this a text field use a function like CINT to convert it from text to integer.

    From then on you can calculate.
    Bye Erwin

  3. #3
    Join Date
    May 2009
    Location
    Crete, Greece
    Posts
    90
    ... which gives me

    =IIf([Total] = 7 *4, 14*7)

    which still doesn't work. The '7' and the '14' are number fields.

  4. #4
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    =IIf([Total] = "7" *4, "14"*7)
    In the above you are assuming that there are only two values for total. What happens when the total is not 7 or 14?

    Have you tried
    =IIf([Total] = 7,([total] *4), iif([total]=14,([total]*7),[total]))

  5. #5
    Join Date
    May 2009
    Posts
    258
    Since you need to process multiple cases, it'd be better to use the Switch function:
    Code:
    =[Total] * Switch([Total]=7, 4, [Total]=14, 7)
    Regards,

    Ax

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Agreed with the Smurf
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    May 2009
    Location
    Crete, Greece
    Posts
    90
    Thanks for the replies. I fear that I gave you bad info in the first place, the 'Total' field I was referring to is actually named 'Number_Of_Days'. It is a number field, and will only ever contain the numbers 7 and 14.

    I have replaced the word 'total' with 'Number_Of_Days' in your suggestions, and got the following error messages.

    =[Number_Of_Days] * Switch([Number_Of_Days]=7, 4, [Number_Of_Days]=14, 7)

    Invalid syntax

    =IIf([Number_Of_Days] = 7,([Number_Of_Days] *4), iif([Number_Of_Days]=14,([Number_Of_Daysl]*7),[total]))

    and then

    =IIf([Number_Of_Days] = 7,([total] *4), iif([Number_Of_Days]=14,([total]*7),[total]))

    The expression you entered has an invalid . ! or parenthesis

    Thanks for any further info, I'm not going to manage this on my own...

    Lisa

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what do you think is wrong with this line
    Code:
    =IIf([Number_Of_Days] = 7,([Number_Of_Days] *4), iif([Number_Of_Days]=14,([Number_Of_Daysl]*7),[total]))
    ?
    as far as I can see there is no significant issue witrh this as air code, but in using in your application there probably will be a problem

    have you looked in detail at what the help system suggests for the IIF function
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Feb 2004
    Posts
    40
    hi, i just tried it on a temporary database and this statemant in a query is working ( using access 2007 ):

    Expr1: IIf([Table1]![number_of_days]=7;[Table1]![number_of_days]*4;IIf([Table1]![number_of_days]=14;[Table1]![number_of_days]*7;[Table1]![total]))

    The things which are diffent are :

    - table mentioning
    - no () around your calculations
    - ; instead of , ( that could be qeury related )

    Are you using this in a query or in code ??
    Regards
    Bye Erwin

  10. #10
    Join Date
    May 2009
    Location
    Crete, Greece
    Posts
    90
    In query. I will check out your sample database, many thanks for that. L

  11. #11
    Join Date
    May 2009
    Location
    Crete, Greece
    Posts
    90
    Ok, I made a little tweak because the information I gave you was wrong, and it works perfectly. THANKS SO MUCH, now I can get these invoices out and get some money into the company.

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I think you need to develop some better debugging skills
    from what I can see I suspect your problem was the [total], which I'm guessing was a hangover from the original code...... had you changed that to [number_of_days] it would probably have worked, and probably have saved you some 4 days of time
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    May 2009
    Location
    Crete, Greece
    Posts
    90
    I don't really need to, to be honest. I'm a secretary. I just need to get this db working so I can manage a project.

  14. #14
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you are developing anything, you need better debug skills
    it saves YOU masses of time.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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