Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Location
    Chicago, IL
    Posts
    154

    Unanswered: "On Change" iif statement help...

    Greetings,

    I have a form that shows a status field, values pulled from a combo box. If someone changes the status to "cancelled" i would like to have another field (not visible) update with the current date.

    I put the following into the "On Change" box:

    =IIf(([status]="Cancelled"),([Cancelled Date]=Date()),)

    Seems easy enough but I can't get it to populate the "cancelled date" field with anything.

    PS, does the "cancelled date" field even need to be on the form?

    Thanks

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I don't think you can set values for controls that have a visible property set to false.. I'd have to check... Perhaps you could set the value to date() by calling the recordset and field directly?

  3. #3
    Join Date
    Oct 2003
    Location
    Chicago, IL
    Posts
    154
    By calling the recordset and field directly, do you mean:

    [programmed adj]![status] instead of just [status]?
    and
    [programmed adj]![cancelled date] instead of just [cancelled date]?

    (note the title under my name! it's true!)

    Hmmmm

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Originally posted by jimmyswinger
    By calling the recordset and field directly, do you mean:

    [programmed adj]![status] instead of just [status]?
    and
    [programmed adj]![cancelled date] instead of just [cancelled date]?

    (note the title under my name! it's true!)

    Hmmmm
    Depends on how your application is setup. I was thinking dim'ing a recordset and manually navigating to the record, or perhaps running an update statement.

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    iif() returns a value, so
    something = iif(this, 1, 2)
    sets something to 1 if this is true, else 2

    simple if will do it:
    if [status]="Cancelled" then [Cancelled Date]=Date()

    or:
    if [status]="Cancelled" then
    [Cancelled Date]=Date()
    else
    [Cancelled Date]=""
    endif

    and there are (of course) immediate-if equivalents:
    [Cancelled Date] = iif([status] = "Cancelled", Date())
    [Cancelled Date] = iif([status] = "Cancelled", Date(), "")


    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Oct 2003
    Location
    Chicago, IL
    Posts
    154
    Yup, that did it... I used:

    if [status]="Cancelled" then [Cancelled Date]=Date()

    I didn't know that I couldn't put it directly into the "on change" field, I just learned that all statements should be put into the code (i used code builder). I'm so green.

    Thanks, guys...

    Hope that pizza was tasty, izy!

Posting Permissions

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