Help Please in Form Yes/no field

[christ2000]

Hello, please I need help on this, is there any way to update a yes/no field when a number in another field change?



For example, I have a table with a 4 fields, 2 are Field Dates, another is the difference of this to date that could be negative or positive, and the other field is name EXPIRED(yes/No), that I want is when the difference in 3 field is negative the field is YES, and when is positive is NO in the EXPIRED field, I get the change but just in afterupdate, but I need this automatic when the number change

Private Sub Expired_BeforeUpdate(Cancel As Integer)
If Me.Days_Ramaining.Value < 0 Then
Me.[Expired] = True
End If
Else
If Me.Days_Ramaining.Value > 0 Then
Me.[Expired] = False
End If
End Sub

THanks

[isladogs]

Move the code to the AfterUpdate event of the Days_Remaining field

BUT:
a) what if Days_Remaining = 0?
b) you can simplify the code as follows

Private Sub Days_Ramaining_AfterUpdate()
If Me.Days_Ramaining < 0 Then 'or use <=0??? see comment above
     Me.Expired = True
Else
     Me.Expired = False
End If
End Sub

[Minty]

I wouldn't actually store this - just calculate it. Same with your Days_Remaining field, you can always calculate it - therefore don't store it.
As you have found storing it means you have to try and capture any changes made anywhere in your system to keep it accurate. Simply calculate it in your forms underlying query and it will always be accurate.

Two calculated fields in your forms query;

DaysRemainCalc : DateDiff(["d",StartDate],[EndDate])
ExpiredCalc: IIf(DateDiff(["d",StartDate],[EndDate]) > 3, True, False)

[christ2000]

Thanks, I try this but do not update the Expired Field

[christ2000]

Private Sub Days_Ramaining_AfterUpdate()
If Me.Days_Ramaining < 0 Then 'or use <=0??? see comment above
Me.Expired = True
Else
Me.Expired = False
End If
End Sub

this do not work

[christ2000]

I wouldn't actually store this - just calculate it. Same with your Days_Remaining field, you can always calculate it - therefore don't store it.
As you have found storing it means you have to try and capture any changes made anywhere in your system to keep it accurate. Simply calculate it in your forms underlying query and it will always be accurate.

Two calculated fields in your forms query;

DaysRemainCalc : DateDiff(["d",StartDate],[EndDate])
ExpiredCalc: IIf(DateDiff(["d",StartDate],[EndDate]) > 3, True, False)

Hello, to try this where can I must put this codes? in the query criteria
thanks for help

[christ2000]

I wouldn't actually store this - just calculate it. Same with your Days_Remaining field, you can always calculate it - therefore don't store it.
As you have found storing it means you have to try and capture any changes made anywhere in your system to keep it accurate. Simply calculate it in your forms underlying query and it will always be accurate.

Two calculated fields in your forms query;

DaysRemainCalc : DateDiff(["d",StartDate],[EndDate])
ExpiredCalc: IIf(DateDiff(["d",StartDate],[EndDate]) > 3, True, False)

Thanks with some change I did this way work perfect... thanks all guys for your help

[christ2000]

HEllo, now I saw that if end date is empty, the expired is also true, is there any way if expiration date is null, the expired field become false too? this is the code I use:

Expr2: IIf(DateDiff("d",Date(),[Expiration Date])>0,False,True)

[isladogs]

Try this

Expr2: IIf((DateDiff("d",Date(),[Expiration Date])>0) Or (Nz([Expiration Date],0)=0),False,True)

[christ2000]

Try this

Expr2: IIf((DateDiff("d",Date(),[Expiration Date])>0) Or (Nz([Expiration Date],0)=0),False,True)

Work Perfect, Thanks For your help to all of U