Test for one space

[mjones]

Hi,

I have a text field. If it has one space, I'd like to do something.
If it has anything else, I'd like to do something else.

How to I say If Me.ClassCode = " " Then?

The above gives me errors. After trying a zillion things for two
hours, I think I should ask for help.

It's a long story as to why there is a space, but basically, it fixed
another long story problem.

Thanks a bunch,

Michele

 

[John W. Vinson]

Hi,

I have a text field. If it has one space, I'd like to do something.
If it has anything else, I'd like to do something else.

How to I say If Me.ClassCode = " " Then?

The above gives me errors. After trying a zillion things for two
hours, I think I should ask for help.

It's a long story as to why there is a space, but basically, it fixed
another long story problem.

Thanks a bunch,

Michele

It's quite difficult indeed to get Access to store nothing but a space in a
field, since it truncates trailing spaces! Are you quite certain that the
field consists of a single space and nothing else?

That said... a criterion

SELECT <whatever> FROM yourtable
WHERE Len([ClassCode]) = 1 AND [ClassCode] LIKE "* *";

should do it.

If you're trying to find classcodes containing only one embedded space
anywhere within the field, surrounded by other characters, a criterion

LIKE "*[! ] [! ]*"

The [! ] wildcard means "anything but a blank in this position".
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[mjones]

Hi,

I have a text field.  If it has one space, I'd like to do something.
If it has anything else, I'd like to do something else.

How to I say If Me.ClassCode = " " Then?

The above gives me errors.  After trying a zillion things for two
hours, I think I should ask for help.

It's a long story as to why there is a space, but basically, it fixed
another long story problem.

Thanks a bunch,

Michele

It's quite difficult indeed to get Access to store nothing but a space ina
field, since it truncates trailing spaces! Are you quite certain that the
field consists of a single space and nothing else?

That said... a criterion

SELECT <whatever> FROM yourtable
WHERE Len([ClassCode]) = 1 AND [ClassCode] LIKE "* *";

should do it.

If you're trying to find classcodes containing only one embedded space
anywhere within the field, surrounded by other characters, a criterion

LIKE "*[! ] [! ]*"

The [! ] wildcard means "anything but a blank in this position".
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

Hi John,

Yes, there is just one space in the ClassCode field if there isn't an
actual classcode.

To explain better, I'm trying to do something like this:

If IsNull(Me.[ClassCode]) Then
Me.ClassLabel.Visible = False
Me.StudentLabel.Visible = False
Else
Me.ClassLabel.Visible = True
Me.StudentLabel.Visible = True
End If

I don't think a Select Statement will work here.

Thanks again,

Michele

P.S. - When you get the yellow highlight on code that gives an error,
what is the best way to get more information on the cause of the error?

 

[John W. Vinson]

Hi John,

Yes, there is just one space in the ClassCode field if there isn't an
actual classcode.

To explain better, I'm trying to do something like this:

If IsNull(Me.[ClassCode]) Then
Me.ClassLabel.Visible = False
Me.StudentLabel.Visible = False
Else
Me.ClassLabel.Visible = True
Me.StudentLabel.Visible = True
End If

I don't think a Select Statement will work here.

Thanks again,

Michele

P.S. - When you get the yellow highlight on code that gives an error,
what is the best way to get more information on the cause of the error?

Ummm... that's not what you asked. It's a very reasonable question but a
DIFFERENT question.

Try

Me.ClassLabel.Visible = (Len([ClassCode] & "") > 0)
Me.StudentLabel.Visible = (Len([ClassCode] & "") > 0)

Concatenating the value of ClassCode with a null string will give you a string
result; if ClassCode is NULL or a null string, the concatenation will be a
null string and its Length will be 0. If there is data in the classcode its
length will be greater than 0.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[mjones]

Hi John,

Yes, there is just one space in the ClassCode field if there isn't an
actual classcode.

To explain better, I'm trying to do something like this:

   If IsNull(Me.[ClassCode]) Then
       Me.ClassLabel.Visible = False
       Me.StudentLabel.Visible = False
   Else
       Me.ClassLabel.Visible = True
       Me.StudentLabel.Visible = True
   End If

I don't think a Select Statement will work here.

Thanks again,

P.S. - When you get the yellow highlight on code that gives an error,
what is the best way to get more information on the cause of the error?

Ummm... that's not what you asked. It's a very reasonable question but a
DIFFERENT question.

Try

Me.ClassLabel.Visible =  (Len([ClassCode] & "") > 0)
Me.StudentLabel.Visible = (Len([ClassCode] & "") > 0)

Concatenating the value of ClassCode with a null string will give you a string
result; if ClassCode is NULL or a null string, the concatenation will be a
null string and its Length will be 0. If there is data in the classcode its
length will be greater than 0.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

Hi Again,

This is driving me crazy. I'm getting an error on the first line -
Me.ClassLabel.Visible = (Len([ClassCode] & "") > 0), but only when
ClassCode is a space. The error is 2427; the expression has no
value. Checking the table, there is a space in the field.

I'm now getting an error on the first line of this (with either the
first or second line). Changing the code above might not have caused
it. These errors keep popping up but I'm having trouble determining
when they happen.

' If Me.Terms <> " " Then
If IsNull(Terms) Then
Me.TermsLabel.Visible = False
Else
Me.TermsLabel.Visible = True
End If

Any ideas would be really appreciated. All the hard parts of the
application are done and it's these little things that don't display
labels when their corresponding objects are blank that I'm having
trouble with.

Thanks again,

Michele

 

[mjones]

Hi John,
Yes, there is just one space in the ClassCode field if there isn't an
actual classcode.
To explain better, I'm trying to do something like this:
   If IsNull(Me.[ClassCode]) Then
       Me.ClassLabel.Visible = False
       Me.StudentLabel.Visible = False
   Else
       Me.ClassLabel.Visible = True
       Me.StudentLabel.Visible = True
   End If
I don't think a Select Statement will work here.
Thanks again,
Michele
P.S. - When you get the yellow highlight on code that gives an error,
what is the best way to get more information on the cause of the error?

Ummm... that's not what you asked. It's a very reasonable question but a
DIFFERENT question.

Try

Me.ClassLabel.Visible =  (Len([ClassCode] & "") > 0)
Me.StudentLabel.Visible = (Len([ClassCode] & "") > 0)

Concatenating the value of ClassCode with a null string will give you astring
result; if ClassCode is NULL or a null string, the concatenation will be a
null string and its Length will be 0. If there is data in the classcodeits
length will be greater than 0.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

Hi Again,

This is driving me crazy.  I'm getting an error on the first line -
Me.ClassLabel.Visible =  (Len([ClassCode] & "") > 0), but only when
ClassCode is a space.  The error is 2427; the expression has no
value.  Checking the table, there is a space in the field.

I'm now getting an error on the first line of this (with either the
first or second line).  Changing the code above might not have caused
it.  These errors keep popping up but I'm having trouble determining
when they happen.

'    If Me.Terms <> " " Then
    If IsNull(Terms) Then
        Me.TermsLabel.Visible = False
    Else
        Me.TermsLabel.Visible = True
    End If

Any ideas would be really appreciated.  All the hard parts of the
application are done and it's these little things that don't display
labels when their corresponding objects are blank that I'm having
trouble with.

Thanks again,

Michele

I realized I didn't give enough detail above. Terms is also a field
where I space is to be checked for. It's a text field with default "
" value in the table. However, in the form, the default property is
[Terms].[ItemData](0) and the row source is " ";"Net 30";"See note".
In other words, if the default space hasn't changed, we need the label
to not show beside the blank looking field with really a space in it.

For more information on ClassCode, in the form, the record source is
SELECT tClass.ClassCode FROM tClass ORDER BY tClass.ClassCode DESC;.
In the table, it's just a regular text field without a default value
and field size 30.

Also, I use Excel 2003.

I hope this helps. I've been struggling with these label visibilities
for several months.

Thank you,

Michele

 

[John W. Vinson]

On Tue, 13 Dec 2011 05:20:49 -0800 (PST), mjones

I think you're making a big mistake trying to store a meaningful " " in a
field.

As I've said repeatedly, ACCESS WILL NOT STORE SUCH A FIELD.

You CANNOT - in an Access table Text field - make a distinction between " ",
"", and NULL. They'll all be changed to NULL before the value is stored.

You say you're using Excel 2003 - do you mean that this data is being stored
in an Excel spreadsheet, rather than an Access table?
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[mjones]

On Tue, 13 Dec 2011 05:20:49 -0800 (PST), mjones


I think you're making a big mistake trying to store a meaningful " " in a
field.

As I've said repeatedly, ACCESS WILL NOT STORE SUCH A FIELD.

You CANNOT - in an Access table Text field - make a distinction between "",
"", and NULL. They'll all be changed to NULL before the value is stored.

You say you're using Excel 2003 - do you mean that this data is being stored
in an Excel spreadsheet, rather than an Access table?
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

Really sorry John. I think I'm tired. Meant to say Access 2003; not
Excel (I use Excel a lot).

A big mistake it seems to be using a space since I can't get it to
work. I'll give it a shot changing those fields to blank and checking
for If IsNull(Terms). I think that's what you're saying is best and
let you know how it goes.

Thanks again,

Michele

 

[mjones]

Really sorry John.  I think I'm tired.  Meant to say Access 2003; not
Excel (I use Excel a lot).

A big mistake it seems to be using a space since I can't get it to
work.  I'll give it a shot changing those fields to blank and checking
for If IsNull(Terms).  I think that's what you're saying is best and
let you know how it goes.

Thanks again,

Michele

Yeah! It worked. No spaces anymore for me after the long struggle to
learn the proper way to do things.

Thanks John!!

 

[John W. Vinson]

Yeah! It worked. No spaces anymore for me after the long struggle to
learn the proper way to do things.

Thanks John!!

You're most welcome! Glad it's working for you, and I hope that NULL doesn't
cause any issues.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Tony Toews]

It's quite difficult indeed to get Access to store nothing but a space in a
field, since it truncates trailing spaces!

If you are using bound forms correct. But if you are doing
Append/Update queries then you can store trailing spaces. Although
that might come under your qualifier of "quite difficult indeed."

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/

 

[John W. Vinson]

If you are using bound forms correct. But if you are doing
Append/Update queries then you can store trailing spaces. Although
that might come under your qualifier of "quite difficult indeed."

Tony

Huh. No, that's not difficult - just not something I realized could be done!
Thanks, Tony.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com