Isnull not working

[stu_c]

Hi all
I cannot seem to get this to work any suggestions, the isnull is just being ignored

[Forms]![FRM_ALL_LoggedSplashPage]![DefaultExamLocation]
is a separate form which runs in the back ground,

I have tried

If Isnull ([Forms]![FRM_ALL_LoggedSplashPage]![DefaultExamLocation]) Then
Me.SubFormHR.Visible = True

Else
Me.SubFormHR.Visible = false

-------------------------------
If [Forms]![FRM_ALL_LoggedSplashPage]![DefaultExamLocation] =" " Then
Me.SubFormHR.Visible = True

Else
Me.SubFormHR.Visible = false


both seem to ignore a blank field but work fine when it has something in it

 

[Gasman]

Not Null then?, likely a zls zero length string?

 

[Pat Hartman]

Null, ZLS, and blank all look the same but they are different. You checked for null and then for blank but not for a ZLS

One way to check for Null and ZLS in a single expression is:

If [Forms]![FRM_ALL_LoggedSplashPage]![DefaultExamLocation] & "" = "" Then
    Me.SubFormHR.Visible = True
Else
    Me.SubFormHR.Visible = false
EndIf

The best solution is to never allow ZLS in text fields at all. MS vacillated between having this property set to Yes or No and it is currently Yes which is what is causing your confusion. You have to run an update query to change all the ZLS to Null. Then change the AllowZeroLengthStrings property to No for each text field.

 

[moke123]

Another way to check for null and ZLS is nz(YourFieldName,"") = ""

 

[Isaac]

A bit similar to moke's solution I have often used something like this

If len(Me.Value & "")=0 then

   'it's empty

else

   'it's not empty

end if

The concatenation between a potentially null & the ZLS forces the whole thing to string, in which case you can either check Len or simply "" if I recall correctly.
Just the opposite in SQL Server in the case where you concatenate a potentially null column with something else, it will come back all null if any concatenated columns are null

 

[Galaxiom]

The concatenation between a potentially null & the ZLS forces the whole thing to string

Depends on which Concat operator is used in Access. The plus sign used with strings concatenates, propagating Nulls but the ampersand just skips the Nulls.

For T-SQL there is the CONCAT() function which doesn't propagate Nulls.

Concat_WS() was introduced in SQL Server 2018 (IIRC). It is Concat with a specified separator character string.

 

[Isaac]

Depends on which Concat operator is used in Access. The plus sign used with strings concatenates, propagating Nulls but the ampersand just skips the Nulls.

For T-SQL there is the CONCAT() function which doesn't propagate Nulls.

Concat_WS() was introduced in SQL Server 2018 (IIRC). It is Concat with a specified separator character string.

Access is &

 

[ebs17]

If Me.Value > vbNullstring  Then    'it's not empty

 

[Galaxiom]

Access is &

You only have half the story. The plus sign operator is "overloaded" in Access. Of course it just means addition if applied to numeric variables but it will also concatenate strings, just as it does in T-SQL.

Combine text values by using an expression - Microsoft Support

Use the ampersand and plus expression operators in Access to combine the values in two or more text fields.

support.microsoft.com

The last couple of paragraphs explains how its behaviour differs from the ampersand concatenator and how to make use of its Null propagation feature.

 

[MajP]

Although it is rare you can also get an empty space or spaces. Normally an import from Excel or somewhere else. This works for all three cases:
Null
ZLS
Space or spaces

If trim(controlName.Value & "") = ""