When to use Is Null and =""

[sandybam]

Hi All,

I have a form where I do not want the user to be able to close it if the LetterAlert check box has been checked but no details have been added to the LetterAlertDetails memo field.

So I have placed the following code before all of the other code on my close button:

If Me.LetterAlert = -1 And Me.LetterAlertDetails Is Null Then
DoCmd.RunMacro "mcroMissingLetterAlertDetails"
Else

...the rest of my close button code.

This works if I remove previously saved data. So I'm guessing that I am not using Is Null and ="" properly. Does anyone know what I am doing wrong?

Thank you!

 

[Trendsetter]

Probably should be:

If Me.LetterAlert = -1 AND IsNull([Me].[LetterAlertDetails] Then
DoCmd.RunMacro "mcroMissingLetterAlertDetails"
Else

Let me know

 

[sandybam]

This worked perfectly. Thank you for your quick response!

Sandy

 

[PeteJohnston]

If you're having a problem with nulls versus zero length strings, I think you could use

If Iif(IsNull(LetterAlertDetails), "", LetterAlertDetails) = "" Then

which substitutes "" if the field is Null. It always used to catch me out when I checked if a field was Null but it was actually "", or the other way around. PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

 

[KornGeek]

The Nz command is another approach. It operates similar to the IIF command. For instance,

Iif(IsNull(LetterAlertDetails), "", LetterAlertDetails)

is equivalent to

Nz(LetterAlertDetails, "&quot