help with a multiple IF statement

[lorenambrose]

I have this multiple in the code for a form but it seems like it is ignoring the last if statement.

If IsNull(Me.Name_Cancel) Or IsNull(Me.[Date Cancelled]) Or IsNull(Me.Cancel_Reason) Then

Please help me figure this out.

 

[lorenambrose]

ah, you figured it out as I was typing my reply

so for your next question
null is not the same as ""

null means undefined, nothing

"" is not undefined, it is an empty string

its confusing to a lot of people, but think of it as a "middle initial" field

there is a difference between null (nothing was ever entered, so we don't know if they have a middle initial or not) and a "" (no middle initial)

I'm not sure how your field became "", maybe somebody entered something and then backspaced it out
I'm not sure that would cause a "", but maybe ?

I usually do this
if Len(trim(Nz(Me.FirstName,""))) = 0 then

there may be better ways, but that's how I do it

Is that code checking for both Null and ""?

If so how would I get my code to do that?

 

[james_lankford]

nz substitutes a value you specify if the value is null

so nz( date_field_to_test, #1/1//1990# )

would return jan 1 1990 everytime that date is null, otherwise it just returns whatever date is stored in there

nz( number_field_to_test, -1 )

would return -1 everytime that number is null, otherwise it just returns whatever number is stored in there

nz( char_field_to_test, "" )

would return "" everytime that char is null, otherwise it just returns whatever chars are stored in there

but what if the char field contains "                  " 

then you can trim it to remove space and make it "" 

trim( nz( char_field_to_test, "" ) ) 

would return "" if the field is null 
otherwise it returns whatever is in the field 
then it trims it 
so "" stays ""
"bruce wayne     "
becomes "bruce wayne"
"           bruce wayne     "
becomes "bruce wayne"
and "                   " 
becomes "" 

  you could say 
if trim( nz( char_field_to_test, "" ) )  = "" then 

but I just prefer to test the length 
if len ( trim( nz( char_field_to_test, "" ) ) ) = 0 then

 

[lorenambrose]

Would this be correct:

WHAT I HAVE NOW.....

If IsNull(Me.Name_Cancel) Or IsNull(Me.[Date Cancelled]) Or IsNull(Me.Cancel_Reason) Then


CHANGE TO THIS?????????

If Len(trim(Nz(Me.Name_Cancel,""))) = 0 OR Len(trim(Nz(Me.[Date Cancelled],""))) = 0 OR Len(trim(Nz(Me.Cancel_Reason,""))) = 0 Then


Trying to accomplish this:

IF any of the three fields / controls are "" / Null / empty then it returns a TRUE value.