Using Dates in If Then statements in Continuous Forms

[bamrak]

I have a form that displays records for a table. I have added some if
then statements to the form, but it appears that my date logic does
not work. In testing, it appears it is picking up the first record in
the form and using it for the logic. If I remove the date calculation,
the logic works across the whole form. Is there some other way I can
accomplish date logic to where it works on a row by row basis? ( in
the below code if I use Dt = Date, it returns the same failure).
The logic I am trying for should be If the difference between the
date in txtAssigned and today is more than 15 days = true.

Private Sub Frmt_Dt()
Dim Dt As Date
Dim lngRed As Long, lngYellow As Long, lngWhite As Long, lngBlack As
Long
Dim Dt As Date
lngRed = RGB(255, 0, 0)
lngBlack = RGB(0, 0, 0)
lngYellow = RGB(255, 255, 0)
lngWhite = RGB(255, 255, 255)
Dt = Now

If DateDiff("d", Dt, Me.txtAssigned) > 15 Then
Me.txtAssigned.BackColor = lngYellow
Me.txtAssigned.ForeColor = lngBlue
End If

End Sub

Thank you for any help anyone can provide

 

[Stefan Hoffmann]

hi,

I have a form that displays records for a table. I have added some if
then statements to the form, but it appears that my date logic does
not work. In testing, it appears it is picking up the first record in
the form and using it for the logic.

I assume you are using an endless form? If so, use conditional formatting.


mfG
--> stefan <--

 

[bamrak]

hi,



I assume you are using an endless form? If so, use conditional formatting.

mfG
--> stefan <--

Using the above senario would work with conditional formatting, but
I'm also using the logic for automation within the form, which I don't
think conditional formatting will do. I provided that example as the
easiest to replicate.

This set is also failing, which I don't believe cannot be completed
with conditional formatting :

If Me.txtSTATUS = "OPEN" And DateDiff("d", Dt, Me.txtAssigned) > 60
And IsNull(Me.txtNote) And txtRej_Cd = "PENDING" Then
txtActionDt.Value = Date
Me.txtNote.Value = "AutoClose"
me.txtAction.Value = "CLS"
End If

If I take out 'And DateDiff("d", Dt, Me.txtAssigned) > 60' the If
statement completes where the other parts are true.

Thanks!

 

[Stefan Hoffmann]

hi,

If I take out 'And DateDiff("d", Dt, Me.txtAssigned) > 60' the If
statement completes where the other parts are true.

Try it with switching the dates:

DateDiff("d", CDate(Me.txtAssigned), Dt)

Use the CDate() function to ensure to use a date.

mfG
--> stefan <--

 

[bamrak]

hi,



Try it with switching the dates:

DateDiff("d", CDate(Me.txtAssigned), Dt)

Use the CDate() function to ensure to use a date.

mfG
--> stefan <--

still nothing. if you use msgbox (DateDiff("d", Dt, Me.txtAssigned))
it returns the same as DateDiff("d", CDate(Me.txtAssigned), Dt),
which to me means they are correctly executing the math part, but it
seems to be unable to traverse the rows. I'm really at a loss to why
it fails once the date logic is added.

 

[Douglas J. Steele]

You have no choice but to use Conditional Formatting.

While it may appear as though you've got, say, 200 text boxes named
txtAssigned on your form, in actual fact you've got one text box, repeated
200 times. Any change you make to the properties of one text box affects
them all.

 

[bamrak]

You have no choice but to use Conditional Formatting.

While it may appear as though you've got, say, 200 text boxes named
txtAssigned on your form, in actual fact you've got one text box, repeated
200 times. Any change you make to the properties of one text box affects
them all.

I guess my confusion comes from the following code working, whereas it
stops working when using the date in the if clause.

"If Me.txtSTATUS = "OPEN" And IsNull(Me.txtNote) And txtRej_Cd =
"PENDING" Then
txtActionDt.Value = Date
Me.txtNote.Value = "AutoClose"
me.txtAction.Value = "CLS"
End If"

Thank you for the replies and the help however.