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

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!)

"alex" <(E-Mail Removed)> wrote in message
news:eb0b4d0d-080b-4f94-b851-(E-Mail Removed)...
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

On Jul 9, 9:17*am, "Douglas J. Steele"
<NOSPAM_djsteele@NOSPAM_gmail.com> wrote:
> 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!)
>
> "alex" <sql_...@yahoo.com> wrote in message
>
> news:eb0b4d0d-080b-4f94-b851-(E-Mail Removed)...
> 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

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!)

"alex" <(E-Mail Removed)> wrote in message
news:15b00f65-3082-40ca-882d-(E-Mail Removed)...
On Jul 9, 9:17 am, "Douglas J. Steele"
<NOSPAM_djsteele@NOSPAM_gmail.com> wrote:
> 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

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

Douglas J. Steele wrote:
> What was the actual code you were using before?
>