Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Posts
    300

    Red face Unanswered: Help with IIF Statement

    I am not having much luck with this:

    I have a combo box with either open or closed as the options. If you select closed, I want another field populated with today's date.

    Here is what I have but I am getting an error -

    Private Sub fldLineStatus_AfterUpdate()
    IIf ([fldLineStatus] = "Closed", [fldCompletionDate] = Date(),)
    End IIf
    End Sub

  2. #2
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by databasemon View Post
    I am not having much luck with this:

    I have a combo box with either open or closed as the options. If you select closed, I want another field populated with today's date.

    Here is what I have but I am getting an error -

    Private Sub fldLineStatus_AfterUpdate()
    IIf ([fldLineStatus] = "Closed", [fldCompletionDate] = Date(),)
    End IIf
    End Sub

    I would suggest not using the IIF. It is design to only return a value, not set one.

    Try:

    Code:
    Private Sub fldLineStatus_AfterUpdate()
    
    If (Me.[fldLineStatus] = "Closed" Then 
       Me.[fldCompletionDate] = Date()
    Else
       ' remove date if reopened
       Me.[fldCompletionDate] = Null
    
    End If
    
    End Sub
    Of you must use the IIF then try this:

    Code:
    Private Sub fldLineStatus_AfterUpdate()
    
    
    Me[fldCompletionDate] =  IIf (Me.[fldLineStatus] = "Closed", Date(), Null)
    
    End Sub
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  3. #3
    Join Date
    Nov 2003
    Posts
    300
    Much appreciation!== it worked perfect!

    Sometimes it is the simple things that elude us.

  4. #4
    Join Date
    May 2010
    Posts
    601

    Thumbs up

    Glad to hear you figured it out!
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

Posting Permissions

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