help with datediff()

[mrbboy]

I have a form with two text fields, txt_FollowUpDate and txt_ApproveDate. In the After Update event of txt_ApproveDate, I placed the code below to check if the date enetred in it is before the follow up date.

If DateDiff("d", Date, [txt_FollowUpDate]) <= 0 Then
Me.txt_ApproveDate = Date
Else
MsgBox "You can not approve the CAR before the follow up is completed.", vbExclamation
Me.txt_ApproveDate = Null
End If

This works great when the date in txt_FollowUpDate is before the Approve Date. However, when txt_FollowUpDate is null, I get the message box message. Shouldn't it still set txt_ApproveDate to Date if txt_FollowUpDate = 0?

 

[jrbarnett]

No, because Null and 0 are different things as far as the database is concerned.
0 is a numeric value, whereas null is the absence of data in a particular field. The meaning of this is dependent on your application.

Easiest way is to code around it:

If Not IsNull ([txt_FollowUpDate]) Then
  If DateDiff("d", Date, [txt_FollowUpDate]) <= 0 Then
    Me.txt_ApproveDate = Date
  Else
    MsgBox "You can not approve the CAR before the follow up is completed.", vbExclamation
  Me.txt_ApproveDate = Null
  End If
End If

This will just bypass the check if there's a null value in that date field.

John

 

[TheAceMan1]

. . . and this:

[blue]   If DateDiff("d", Date, [purple][b]CDate(Nz([/b][/purple][txt_FollowUpDate][purple][b], 0))[/b][/purple]) <= 0 Then[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997