<> does not detect Null changes

[thr33xx]

Hello,

I am working on a form which allows users to modify changes to a Study. This form contains values, which if changed or modified, have the potential to change the way the metrics are changed. For that reason, there are 6 values that must be checked before the data is changed.

I have the form setup which loads the original values (which are not visible). These are used to compare to the values which are modifiable by the user. Below is the code.

'Checks to see if start date enrollment metrics, date expected enrollment complete, date first subject enrolled, study start enrolled, total target enrollment, and metrics calculation have been modified. If yes, deletes records from monthly expectations if changes have been made.
If Me.St_Dt_Metrics <> Me.St_Dt_Metrics_History Or Me.Dt_Exp_Enroll_Complete <> Me.Dt_Exp_Enroll_Complete_History Or Me.Dt_First_Subject_Enrolled <> Me.Dt_First_Subject_Enrolled_History Or Me.Study_Start_Enrolled <> Me.Study_Start_Enrolled_History Or Me.Tot_Target_Enroll <> Me.Tot_Target_Enroll_History Or Me.Calculate <> Me.Calculate_History Then
    If MsgBox("Enrollment Metrics date(s) have been modified. Proceeding to save changes will delete ALL current Monthly Expectations & require entry of new Monthly Expectations. Proceed?", vbQuestion + vbYesNo) = vbNo Then
        Exit Sub
    Else
        sql1 = "DELETE FROM dbo_Monthly_Expectations WHERE Study_ID = '" & Me.Add_Study_ID & "'"
        cn.Execute sql1
        End If
End If

The problem I am experiencing, is say for example the value of 1/1/2012 exists in the Me.St_Dt_Metrics_History (this is the original value). The user modifies the value my clearing the value from the Me.St_Dt_Metrics. When the code executes, it should detect that the Me.St_Dt_Metrics <> Me.St_Dt_Metrics_History, or NULL <> 1/1/2012.

This is not the case. The code actually does not execute because none of the criteria is met. Can anyone please explain to me why this does not work, and if possible provide some solutions to work around this.

Thank you very much

 

[spikepl]

You cannot use comparators like <> or = to see whether a value is (or isn't) null. In SQL you have

MyField IS NULL

or

NOT MyField IS NULL

and in VBA there is the IsNULL function

In your case you could perhaps use the NZ function, and assign value of 0 or something way beyond your normal data normal range, if the field tested is null.

 

[thr33xx]

Great. Thanks.

 

[gemma-the-husky]

i would use nz, as an easy way to manage both the null, and no value position

if nz(sometextfield,"")="" then


if nz(somenumberfield,0)=0 then