Check for spaces

[alex]

Check for spaces

Hi,

Using Access ’03…

I have some code behind a Form that checks the value of several
unbound text/combo boxes.

If the control = Null or “” then the code doesn’t run (pretty simple).

My problem, however, is that my code will run (allow) when a user
simply enters a space(s) (hitting spacebar once or multiple times).

How can I avoid this?

The code I referenced is mostly SQL code, so when I DIM the variables,
I usually set them to zls to avoid invalid use of null errors; e.g.,:
nz(myUnboundControl,””). But I need the code to be smart enough to
ignore nulls, zls, or spaces.

Any Thoughts?

Thanks,
alex

 

[Douglas J. Steele]

Try:

If Len(Trim(Forms![NameOfForm]![NameOfControl] & vbNullString)) > 0 Then

' Run your code

End If

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
Co-author: Access 2010 Solutions, published by Wiley
(no e-mails, please!)

Check for spaces

Hi,

Using Access ’03…

I have some code behind a Form that checks the value of several
unbound text/combo boxes.

If the control = Null or “” then the code doesn’t run (pretty simple).

My problem, however, is that my code will run (allow) when a user
simply enters a space(s) (hitting spacebar once or multiple times).

How can I avoid this?

The code I referenced is mostly SQL code, so when I DIM the variables,
I usually set them to zls to avoid invalid use of null errors; e.g.,:
nz(myUnboundControl,””). But I need the code to be smart enough to
ignore nulls, zls, or spaces.

Any Thoughts?

Thanks,
alex

 

[alex]

Try:

If Len(Trim(Forms![NameOfForm]![NameOfControl] & vbNullString)) > 0 Then

' Run your code

End If

--
Doug Steele, Microsoft Access MVPhttp://www.AccessMVP.com/DJSteele
Co-author: Access 2010 Solutions, published by Wiley
(no e-mails, please!)


Check for spaces

Hi,

Using Access ’03…

I have some code behind a Form that checks the value of several
unbound text/combo boxes.

If the control = Null or “” then the code doesn’t run (pretty simple).

My problem, however, is that my code will run (allow) when a user
simply enters a space(s) (hitting spacebar once or multiple times).

How can I avoid this?

The code I referenced is mostly SQL code, so when I DIM the variables,
I usually set them to zls to avoid invalid use of null errors; e.g.,:
nz(myUnboundControl,””).  But I need the code to be smart enough to
ignore nulls, zls, or spaces.

Any Thoughts?

Thanks,
alex

Doug, that worked perfectly!
I don't really understand the code, however.

Why will an IIF(test for "") function ignore multiple spaces (just
spaces);
and
If (test for "")
End If
will not?
Seems strange.
alex

 

[Douglas J. Steele]

What was the actual code you were using before?

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
Co-author: Access 2010 Solutions, published by Wiley
(no e-mails, please!)

Try:

If Len(Trim(Forms![NameOfForm]![NameOfControl] & vbNullString)) > 0 Then

' Run your code

End If

Doug, that worked perfectly!
I don't really understand the code, however.

Why will an IIF(test for "") function ignore multiple spaces (just
spaces);
and
If (test for "")
End If
will not?
Seems strange.
alex

 

[John Spencer]

If Len(Trim(Forms![NameOfForm]![NameOfControl] & vbNullString)) > 0 THEN

Break it down into its parts starting from the center expression and working out.

---Forms![NameOfForm]![NameOfControl] & vbNullString
Concatenates (adds) a zero-length string to whatever is in the control turning
nulls into ""
"" into ""
" " into " "
"Abcdef" into "Abcdef"

-- TRIM ( ... ) removes any leading or trailing spaces, so the first three
examples above all become ""

-- Len( ... ) determines the character length. "" is zero characters long. If
you failed to trim the expression, then example number 3 above it would be 4
characters long (space is a character - it is just not visible to the human
eye other than when there are characters surrounding the white space.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County