Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2009
    Posts
    37

    Unanswered: Held needed with IIF statement

    I've done searches on google and here but I can't work out what goes wrong and how I can fix it. I thought it wouldn't be difficult, but I just can't figure it out.

    I have a form (Dogs) which has a field 'Age'. This field is unbound and as recordsource it has this:
    Code:
    =Year(0)-Year([Date Born]-Now()+1)
    This gives me the age of the dog in years, just as I want.

    In an other field (AgeClass) I'd like to show the age class a dog is in.
    If the dog is under a year (ie 0) the age class is "Pup"
    If the dog is 1 year the age class is "Yearling"
    Everything else should be "Adult"

    I thought this would solve it but it only returns "#Name?"

    Code:
    IIF ([Me]![Age]=0, "Pup", IIF ([Me]![Age]=1, "Yearling", "Adult") )
    I've also tried to convert it to a script but haven't gotten very far with that and I don't really know what I'm doing while doing that. Not a good idea.

    Any ideas?

    Oops...the title should be: Help needed with IIF statement
    Last edited by VB noob; 03-14-09 at 17:13.

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    just a suggestion, but try:

    IIF(Me.Age = 0, "Pup", IIF(Me.Age = 1, "Yearling", "Adult"))
    Me.Geek = True

  3. #3
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    Also try changing Age to Age.Text or Age.Value - if it's an uncommitted value you have to reference it differently (I think ).

    C

  4. #4
    Join Date
    Feb 2009
    Posts
    37
    Thanks for the answer. You've put me on the right track.
    I set 'Age' to 'standard number' and got the code running looking like this:
    Code:
    =IIf([Age]=0;"Pup";(IIf([Age]=1;"Yearling";"Adult")))

  5. #5
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    those nested IIF can be hard to get your head around well they are sometime for me

    so why note write a function

    Code:
    Function AgeClass(Age)
    Select Case Age
    CASE 0
    AgeClass = "Pup"
    CASE 1 to 2
    AgeClass = "Yearling"
    CASE Else
    AgeClass = "Adult"
    End Select
    
    End Function
    Last edited by myle; 03-16-09 at 23:53.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  6. #6
    Join Date
    Feb 2009
    Posts
    37
    True that these IIf aren't always easy to read, but this one isn't too bad.
    Hence my nickname

    Tried your suggestion as well.
    It gives an error at: 'Select Age'

    The message is: 'Expected: Case'.

  7. #7
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    Select CASE Age

    You might also want to check for nulls in your data perhaps something like;

    Function AgeClass(intAge as integer)

    Select Case nz(intAge,99)
    CASE 0
    AgeClass = "Pup"
    CASE 1 to 2
    AgeClass = "Yearling"
    CASE 99
    AgeClass = "Enter age..."
    CASE Else
    AgeClass = "Adult"
    End Select

    End Function
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

Posting Permissions

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