Validation rule for NO spaces

[peskywinnets]

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]

Hi. By default, Access trims out trailing spaces, if entered manually into a field. Can you try it again to be sure?

 

[Mark_]

BeforeUpdate Field=TRIM(Field)

 

[peskywinnets]

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]

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]

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_]

Any chance it was a non-printable character? Say a TAB or such?

 

[theDBguy]

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]

BeforeUpdate Field=TRIM(Field)

I am not sure you can do that. I think you would have to do it in after_update, although it would fire the before update again.

 

[gemma-the-husky]

if she picked an item from a drop-down list, I would check the drop-down list for trailing spaces.

 

[Minty]

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]

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_]

Watch out, if you decide to fire her, she may decide to TERMINATE you!