Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    IIF? usage (2000/All)

    I have a simple form that has fieelds for student given names (and preferred name) and family name for data entry.

    At the top of the form I have two fields that the user can't change. These fields are to be the family name field and preferred name (depending on whether one is entered) or the the given names.

    Basically it is a "friendly name" at the top of the form for users as a confirmation that they are working with the desired student (but they can check the given names for confirmation).

    I have used an IIF function to check whether the preferred name field is blank (and then use the given names) but this is showing the #Name? (as if the Control Source is not defined correctly).

    This is the code I tried. Where am I going wrong, please?


    <pre>txtFirstNames.Value = IIf(Me.txtPreferredName <> "", [txtPreferredName], [txtGivenNames])</pre>


  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: IIF? usage (2000/All)

    Whenever you use the expression me you are referring to the current form.

    so txtFirstNames.Value = IIf(Me.txtPreferredName <> "", [txtPreferredName], [txtGivenNames]) requires that there be a control on the form called txtPreferredName

    If txtpreferredname is a field in the underlying query, but is not a control on the form (which I think is the case) just remove the me.

    txtFirstNames.Value = IIf([txtPreferredName] <> "", [txtPreferredName], [txtGivenNames])

    Another issue. If the field txtPreferredName is really empty its value will be Null . This is different from "" which is a zero length string. It would be safe to test for both.


    txtFirstNames.Value = IIf(([txtPreferredName] <> "") and not isNull([txtpreferredname]) , [txtPreferredName], [txtGivenNames])
    Regards
    John



  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IIF? usage (2000/All)

    Thanks John for your post - but I am still experiencing the same problem.

    It possibly is just obvious but what am I doing wrong please?

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: IIF? usage (2000/All)

    Is txtPreferredName both the name of the field and the name of the controL? If so, that is causing you the problems. If you refer to a field in an IIF statement or code, it can't have the same name as the control you're populating.
    Charlotte

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: IIF? usage (2000/All)

    You have set the control source of FirstName to a line of VBA instead of a formula (expression). It should be

    =IIf(([txtPreferredName]<>"") And Not IsNull([txtPreferredName]),[txtPreferredName],[txtGivenNames])

    i.e. without FirstName.Value at the beginning.

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: IIF? usage (2000/All)

    In the FirstName text box on the form change the ControlSource from:

    Firstname.Value=IIf(([txtPreferredName]<>"") And Not IsNull([txtPreferredName]),[txtPreferredName],[txtGivenNames])

    to

    =IIf(([txtPreferredName]<>"") And Not IsNull([txtPreferredName]),[txtPreferredName],[txtGivenNames])

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IIF? usage (2000/All)

    Many thanks Pat, Hans and Charlotte for your posts.

    That did the trick - I had been 'inadvertantly' using VBA (because I didn't know better).

    And Charlotte, has your tiara always sparkled? I just noticed it now.

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: IIF? usage (2000/All)

    I added the sparkles to my crown at Christmas and like them so well, I kept them. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Charlotte

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IIF? usage (2000/All)

    I called it a tiara - but a quick check reveals that it should have been a crown or at the very least diadem, Your Highness <g>

Posting Permissions

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