Page 1 of 2 12 LastLast
Results 1 to 15 of 19

Thread: Iff Statements

  1. #1
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273

    Question Unanswered: Iff Statements

    After the confusion I was having with my previous thread about Sequences. It turned out that whilst working how to do what I was doing I couldnt actually do that.

    This is an alternate strategy to see if can get what I am trying to do.

    In the sample database attached is a table. What I am looking at is a way to determine what was done by what Appt.
    For example after Appt1 and NA1 I want to put a field called Appt1Outcome. From this I want to compare the Appt1 date with the NA1 Date & the CompletedDate. If there is an NA1 date and no CompletedDate I want to input NA1 if there is a CompletedDate and an NA1 Date or No NA1Date then I want to put Comp. I will also do the same for the Appt2, 3, 4 & 5

    In Excel I would use if statment but am unsure on them in Access.
    Attached Files Attached Files

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Excel's =If(-,-,-) and Access's =IIf(-,-,-) functions are identical, and their syntax is the same as well.

    BTW, Access's function is 2 i's and an f, not one i and 2 f's.

    Sam

  3. #3
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    Thanks for that, will give it a go.

    I always forget the IIf and not Iff

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Use it more often and you won't confuse the spelling for long. IIf is a very commonly used function!
    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

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Double-eye-eff sounds better than double-eff-eye (it's really stupid the way we remember stuff, eh?)
    George
    Home | Blog

  6. #6
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    As I understand it the Immediate If, IIF(Condition,True,False), you use in VB, VBA Queries etc, ie. everywhere EXCEPT in an Excel spreadsheet.

    The IF(Condition,True,False) is purly a spreadsheet function only, or is that an over simplifiation?



    MTB

  7. #7
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    It works like a treat

    How can I do the as is in Excel the IF(AND( or the IF(OR in Access?

    Also if I want to put IIF(-=Date,-,-) how do I write the Date is it with or without " " because if I write it with "30/05/2008" then when running the query I get #error and if I put 30/5/2008 then I just get a blank when I've told it to input something?
    Last edited by JezLisle; 05-30-08 at 06:19.

  8. #8
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    You use the same logical constuct as you would with the IF THEN ELSE statment

    ie IIF(A=B AND A=C, TrueAction, FalseAction)
    or
    IIF(A=B OR A=C,TrueAction, FalseAction)

    or any other (bracketed?) combination you can think of/require !

    MTB

  9. #9
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    Thanks will give it a try...

    What can I do about the dates?

  10. #10
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    Also if IIf(-=Blank,-

    What do I write if the field I'm looking at is blank? do I write Null or "" like in Excel?

  11. #11
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    This depends on where you are using the IIF and what you are comparing it to (variable type - date/string etc). You will need to use # signs


    If it is in code then, as you are in the UK, you will need to ensure the date is in mm/dd/yy format, ie. #05/30/08#.

    If it is in a stored query and you are using a field date then it is more straight forward.

    More info/example would be helpful.


    MTB

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Jez - before we get all excited about Iif constructs - are you aware that the design is flawed? Are you familiar with normalisation? Here is a super article that is well worth reading (and posting it will get George into a flurry of excitement too):
    http://www.tonymarston.net/php-mysql...se-design.html

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Also - reading your first post again it sounds like you would need to use nested iifs. There is a neat function called Switch that, IMHO, is much better than nested iifs (much more readable and easier to write). The syntax is in the help files & on the web.

  14. #14
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by MikeTheBike
    Hi

    You use the same logical constuct as you would with the IF THEN ELSE statment

    ie IIF(A=B AND A=C, TrueAction, FalseAction)
    or
    IIF(A=B OR A=C,TrueAction, FalseAction)

    or any other (bracketed?) combination you can think of/require !

    MTB
    you can do an if | else or even fake a select case using nested IIF's.. its not pretty, it can be a pain to debug and understand

    eg
    Code:
    iif(condition1,trueaction,iif(condition2,trueaction,iif(condition...n,trueaction)))
    ferinstance
    Code:
    iif(code=1,"blah",iif(code=2,"blah, blah",iif(code=3,"Blah,Blah,Blah","Blaaaaaah")))
    but much as I 'hate' to admit it Poots is correct.. the switch function is much neater! although it isn't as extendable as IIF
    I'd rather be riding on the Tiger 800 or the Norton

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by healdem
    much as I 'hate' to admit it Poots is correct..
    Oh sweet, smug joy

    Quote Originally Posted by healdem
    although it isn't as extendable as IIF
    Whatcha talkin bout Willis?

Posting Permissions

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