SomeField.oldvalue <> SomeField.value not working with null values

  • Thread starter Thread starter Rick
  • Start date Start date
R

Rick

I have a timestamp field that gets updated when a record is changed. I'd
like to know what type of change was made. For example, if they changed one
of the date fields in my record, I want to put a "d" in my timestamp. If
the change a money field, I would like to put an "M". etc.

In my module, I defined a string StTimeType.

In my 'before update' I have code like:

If Me.Dirty Then
StTimeType = ""

If (DateCompleted.OldValue <> DateCompleted.Value) Or
(InstallDate.OldValue <> InstallDate.Value) Then
StTimeType = StTimeType & "D"
End If

If (SalePrice.OldValue <> SalePrice.value) Or (Cost.OldValue <>
Cost.Value) Then
StTimeType = StTimeType & "M"
End If

If (Installer.OldValue <> Installer.Value) Then
StTimeType - StTimeType & "I"

Me.Timestamp = Format(CurrentUser, ">") & " " & Date & " " & Time

End If



This works very well except, if I have a blank (null) date when I enter the
form, and I enter a date, the 'D' does not appear. If I have a date, and
blank it our, the 'D' does not appear. Basically, it only works if there is
a value, and I change that value. Doesn't my code imply that if the field
is null (oldvalue) and I change it to a date (value), that the two are not
equal(<>).

Thanks for any advice.

Rick
 
I copied the old timestamp line. it should look like:

Me.Timestamp = Format(CurrentUser, ">") & " " & StTimeType & " " & Date & "
" & Time
 
You can't check for equality with Nulls. Because of the nature of a null,
Null does not equal Null.

Try using the Nz function to convert the Nulls to some other value for you.
 
Douglas:

I hate to be dense, but could you give me an example of how to use the Nz
function in the below example to compare a date field that was null to one
that is now not. Would I have to nest an 'if' statement, and if so can you
tell me what it would look like?

Thanks!
 
Since you seem to be using curent dates, you can have Nz default to some
extremely unlikely date. If you default to 0, it'll actually be 30 Dec,
1899: since that's unlikely to be a valid date, you could then use

If (Nz(DateCompleted.OldValue, 0) <> Nz(DateCompleted.Value,0)) Or
_
(Nz(InstallDate.OldValue, 0) <> Nz(InstallDate.Value, 0)) Then
 
Back
Top