Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-20-2019, 12:45 PM   #1
peskywinnets
Newly Registered User
 
Join Date: Feb 2014
Location: London, UK
Posts: 517
Thanks: 278
Thanked 5 Times in 5 Posts
peskywinnets is on a distinguished road
Validation rule for NO spaces

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!

peskywinnets is offline   Reply With Quote
Old 05-20-2019, 12:55 PM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,036
Thanks: 45
Thanked 953 Times in 935 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Validation rule for NO spaces

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

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 05-20-2019, 01:06 PM   #3
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 1,997
Thanks: 20
Thanked 372 Times in 365 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: Validation rule for NO spaces

BeforeUpdate Field=TRIM(Field)

Mark_ is offline   Reply With Quote
Old 05-20-2019, 01:37 PM   #4
peskywinnets
Newly Registered User
 
Join Date: Feb 2014
Location: London, UK
Posts: 517
Thanks: 278
Thanked 5 Times in 5 Posts
peskywinnets is on a distinguished road
Re: Validation rule for NO spaces

Quote:
Originally Posted by theDBguy View Post
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.

Quote:
Originally Posted by Mark_ View Post
BeforeUpdate Field=TRIM(Field)
Thanks but where does that go...in the validation rule?
peskywinnets is offline   Reply With Quote
Old 05-20-2019, 01:40 PM   #5
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,036
Thanks: 45
Thanked 953 Times in 935 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Validation rule for NO spaces

Quote:
Originally Posted by peskywinnets View Post
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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 05-20-2019, 02:13 PM   #6
peskywinnets
Newly Registered User
 
Join Date: Feb 2014
Location: London, UK
Posts: 517
Thanks: 278
Thanked 5 Times in 5 Posts
peskywinnets is on a distinguished road
Re: Validation rule for NO spaces

Quote:
Originally Posted by theDBguy View Post
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.
peskywinnets is offline   Reply With Quote
Old 05-20-2019, 03:12 PM   #7
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 1,997
Thanks: 20
Thanked 372 Times in 365 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: Validation rule for NO spaces

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

Mark_ is offline   Reply With Quote
Old 05-20-2019, 05:12 PM   #8
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,036
Thanks: 45
Thanked 953 Times in 935 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Validation rule for NO spaces

Quote:
Originally Posted by peskywinnets View Post
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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 05-21-2019, 01:26 AM   #9
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,769
Thanks: 55
Thanked 1,022 Times in 988 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: Validation rule for NO spaces

Quote:
Originally Posted by Mark_ View Post
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.
__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the bottom left of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 05-21-2019, 01:28 AM   #10
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,769
Thanks: 55
Thanked 1,022 Times in 988 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: Validation rule for NO spaces

if she picked an item from a drop-down list, I would check the drop-down list for trailing spaces.
__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the bottom left of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 05-21-2019, 01:30 AM   #11
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,349
Thanks: 159
Thanked 1,706 Times in 1,676 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: Validation rule for NO spaces

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.
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 05-21-2019, 01:34 AM   #12
peskywinnets
Newly Registered User
 
Join Date: Feb 2014
Location: London, UK
Posts: 517
Thanks: 278
Thanked 5 Times in 5 Posts
peskywinnets is on a distinguished road
Re: Validation rule for NO spaces

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.
peskywinnets is offline   Reply With Quote
The Following User Says Thank You to peskywinnets For This Useful Post:
Tera (05-21-2019)
Old 05-21-2019, 07:22 AM   #13
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 1,997
Thanks: 20
Thanked 372 Times in 365 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: Validation rule for NO spaces

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

Mark_ is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Validation - No Spaces iankerry Forms 19 03-04-2016 10:16 AM
Validation rule that allows any number of digits and spaces? sirkistova Tables 4 06-13-2011 12:13 PM
Help with validation rule wglmb Tables 2 05-08-2005 01:53 AM
Validation rule morlan Forms 5 05-08-2003 08:52 AM
Validation Rule snowsride Modules & VBA 1 01-06-2002 08:55 AM




All times are GMT -8. The time now is 02:37 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World