Solved Testing for a blank or null textbox (1 Viewer)

[chrisjames25]

Hi, I am creating mulitple forms with multiple textboxes. At times when i click an update cmd button I like to check all fields are correctly filled in and not left empty.

For a textbox I always use the following code:

If Nz(Me.Txt_Box1, "") = "" Then
Else
End IF

is this the best way to test or is it missing something i should be picking up on? Rather get it right before go too far.

Cheers in advance

 

[The_Doc_Man]

This method may or may not be the best method possible, but I have used it extensively and have had very few issues with it.

 

[chrisjames25]

Just found this as an alternative but not sure if second part is really adding anything:

If Nz(Me.Txt_ConfirmTier2, "") = "" Or Len(Trim(Me.Txt_ConfirmTier2) & vbNullString) = 0

 

[The_Doc_Man]

For text boxes, either part of the OR is usually enough. Unless you hare having specific problems, the longer version you posted would count as a bit of overkill. However, I'm sure my colleagues will eventually chime in on why one would or would not use the longer version of that test.

 

[theDBguy]

In some cases, I just use:

If Me.Textbox > "" Then

 

[MajP]

1. 98% of the time when a control is "blank" its value is NULL
so you can check that with

if isNull([ControlName])

2. but about 1.9% of the time it is an empty string ""
So to check for both null and empty string you can

if ([controlName] & "") = ""

This is because Null & "" = "" and "" & "" = "".
However be advised that NULL + "" = Null

3. .95% of the time it is a space character " " or multiple space characters " ". So you can check for Null, "", and " " with

If Trim([controlName] & "") = ""

This is because Trim(" " & "") = "", and trim( Null & "") = ""

4. About .05% of the time it is something else like a non printable characer vblf, vbcrlf, vbcr, tab, etc.
Then you have to roll your own to handle that.

In Access it is not that easy to get a "" in the control. I see it often when people think they are clearing the control in VBA
Me.somecontrol = ""
they should do
Me.somecontrol = NULL
A space,spaces, or other non printables is really hard even through code to get into a control. This usually happens from an import from Excel or some other system, but can happen.

If you want that full proof way and want to reuse it with out rewriting then make a UDF

Public Function IsVeryEmpty(ctrl as access.control) as boolean
  IsVeryEmpty = (trim(ctrl.value & "") = "")
end Function

But that is also why most of the conservative approaches work.
This will never work
if [controName] = NULL
and this hardly ever works as pointed out why
if [controlName] = ""

 

[chrisjames25]

Thanks very much for the contributions. Much appreciated and good insight gained.