asked on

VBA code in MS Access - testing bound text box for Null

Hi Experts,

I'm using VBA code in MS Access.

I've got a bound text box, Organization, that is on a subform.

I'm referencing it by Forms!Clients!Organization.

It works fine, as long as it is not Null.

How can I test if it is Null in an If statement?

I've got this code now:

If Forms!Clients!Organization = Null Then
   MsgBox "You have not specified Company/Organization."
   Exit Sub
End If

Open in new window

But when Forms!Clients!Organization is Null, the If evaluates to False.

Regards,
Leigh

ASKER CERTIFIED SOLUTION

John Tsioumpris

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

LeighWardle

ASKER

Thanks, John.

You are worth your weight in Gold!

Regards,
Leigh

John Tsioumpris

This is rather dangerous statement since we are talking about a lot of weight....lets say the scale has a hard time when i visit her..:)

LeighWardle

ASKER

Hi John,

Just for the record:

I needed to change > 0 to = 0 in the If statement:

If Len(Nz(Forms!Clients!Organization,"")) =  0  then

Open in new window

That works for the text box when it is Null, also when it consists of spaces...

John Tsioumpris

If you have spaces then you need one more command...to clear the spaces

If Len(Nz(Trim(Forms!Clients!Organization),"")) =  0  then

Open in new window

Dale Fye

except if it is NULL, Trim will raise an error:

I use a function which makes it very clear what you are trying to test for, and has the added benefit of having less key strokes than the other options.

If IsNullOrBlank(Forms!Clients!Organization) then

Public Function IsNullOrBlank(SomeValue as Variant) as boolean

    IsNullOrBlank = Trim(SomeValue & " ") = ""

End Function

Open in new window

John Tsioumpris

Good point Dale..so my code should be

If Len(Trim(Nz(Forms!Clients!Organization,""))) =  0  then

Open in new window