Validation rule for NO spaces (1 Viewer)

peskywinnets

Registered User.
Local time
Today, 05:14
Joined
Feb 4, 2014
Messages
576
Just has a situation today where my wife entered a product code into a field...but she had added a trailing space (which caused a few problems).

How can I construct a field validation rule to disallow spaces? (either leading or trailing? Would it be something like this...

Is Null OR Not Like "*Chr(32)*"

Thanks!
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:14
Joined
Oct 29, 2018
Messages
21,358
Hi. By default, Access trims out trailing spaces, if entered manually into a field. Can you try it again to be sure?
 

Mark_

Longboard on the internet
Local time
Yesterday, 22:14
Joined
Sep 12, 2017
Messages
2,111
BeforeUpdate Field=TRIM(Field)
 

peskywinnets

Registered User.
Local time
Today, 05:14
Joined
Feb 4, 2014
Messages
576
Hi. By default, Access trims out trailing spaces, if entered manually into a field. Can you try it again to be sure?

This is a field with a lookup (user-selectable drop down list of product names)...or it can be entered manually ...I'm not sure how she managed it, but for sure there was a trailing space once she'd finished.

BeforeUpdate Field=TRIM(Field)

Thanks but where does that go...in the validation rule?
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:14
Joined
Oct 29, 2018
Messages
21,358
This is a field with a lookup (drop down list)...or can be entered manual ...I'm not sure how she magaed it, but for sure there was a trailing space.
But still, are you able to intentionally duplicate the situation? For example, were you able to enter any data with a trailing space? You could try adding spaces at the end of the data when you're entering it into the field, but as soon as you save the data/record, you could go back and check if the trailing spaces are still there. I would just be curious if you were able to do it intentionally.
 

peskywinnets

Registered User.
Local time
Today, 05:14
Joined
Feb 4, 2014
Messages
576
But still, are you able to intentionally duplicate the situation? For example, were you able to enter any data with a trailing space? You could try adding spaces at the end of the data when you're entering it into the field, but as soon as you save the data/record, you could go back and check if the trailing spaces are still there. I would just be curious if you were able to do it intentionally.

You are right...when I put a space at the end of the field, Access removed it...heaven knows how she managed it.

In actual fact the table she added to is sort of a 'scratch' table, I then take data from that table to append to another table ....in the append query I had a trim() for that field, but somehow it still got appended to the destination table with a trailing space...very weird.
 

Mark_

Longboard on the internet
Local time
Yesterday, 22:14
Joined
Sep 12, 2017
Messages
2,111
Any chance it was a non-printable character? Say a TAB or such?
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:14
Joined
Oct 29, 2018
Messages
21,358
You are right...when I put a space at the end of the field, Access removed it...heaven knows how she managed it.

In actual fact the table she added to is sort of a 'scratch' table, I then take data from that table to append to another table ....in the append query I had a trim() for that field, but somehow it still got appended to the destination table with a trailing space...very weird.
Hi. Mark is correct. The Trim() function only removes space characters. There are plenty more characters available that looks like a "space" to our eyes, but they're actually not.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 05:14
Joined
Sep 12, 2006
Messages
15,614
if she picked an item from a drop-down list, I would check the drop-down list for trailing spaces.
 

Minty

AWF VIP
Local time
Today, 05:14
Joined
Jul 26, 2013
Messages
10,355
As others have said, I suspect it was a cut and paste from something and it had a "special" character in it.

Copy the offending item into a proper text editor, and see if you can see it.
 

peskywinnets

Registered User.
Local time
Today, 05:14
Joined
Feb 4, 2014
Messages
576
Thank for all your comments ...alas, I corrected the issue earlier in the day yesterday, so it's gone (I can't examine whether it was a special character now)

As I say, whatever she put at the end of the text, was not removed by a trim() when the data was appended from the table she entered the text, onto another table...so perhaps it was indeed a special character not a space - I'll simply fire her & be done with it ;-)

Thanks once again.
 

Mark_

Longboard on the internet
Local time
Yesterday, 22:14
Joined
Sep 12, 2017
Messages
2,111
Watch out, if you decide to fire her, she may decide to TERMINATE you!
 

Users who are viewing this thread

Top Bottom