IIF statement and data tracking

[Tcurtis]

I have a database where the status of a part can change from being in, out, lost, etc. I would like to track when the part goes Lost. I created another field to do this with but when the status changes, the field I create changes. I would like for it to remain as it is no matter what the status is as long as it went to Lost one time.
IIF([Status]="5", "Found", "OK") is the If statement I am using currently. How do I make the number in my field stay 5 after the Status field is updated to another status?

Thanks

 

[GinaWhipp]

Then you're going to need another field to *hold* the 5. You can then put and If() statement on that other field once it's 5, it's 5 forever.

 

[Tcurtis]

I put he IIF statement in the other field to hold the 5 but when the other field changes, the 5 is removed.

 

[GinaWhipp]

Is the *other* field bound?

 

[Tcurtis]

Not sure what you mean. The field is in a table.

 

[GinaWhipp]

Okay, I was thinking if you created a separate that you dropped the 5 into, one time, and it was never connected to the original field. So, something like...

If MyNewField = "" Then
   MyNewField = DropInMyData
End If

 

[Tcurtis]

I know what I am doing wrong, I am putting the IIF statement in the table which changes. I should be the IF statement in the form for after update and drop the 5 in one time.

 

[GinaWhipp]

Oh, good deal... I couldn't see that from here... Glad you got it straightened out.

 

[Tcurtis]

Thanks for the help