INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Leading Spaces in Access Database

Leading Spaces in Access Database

(OP)
Good Morning,

I have an access database that multiple users do data entry intro. I have both first and last name fields that are required. However, I am getting "blanks" in that fields because they are using the space bar and access considers that a character. Is there a validation rule that does not allow for leading spaces but will allow for spaces within the name such as De Rosa?

Thank you for your help.

Laura~

RE: Leading Spaces in Access Database

Are you sure you don't want leading spaces? You could set the field to required and Allow Zero Length to No.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Leading Spaces in Access Database

You can set validation rule for this field:
Not like " *"
and message text in case of failing validation.

combo

RE: Leading Spaces in Access Database

(OP)
Not like " *" worked. Thank you!!

Laura~

RE: Leading Spaces in Access Database

Combo,

Your post got me thinking - would " * " not be more thorough?

I tested this in MS Access 2007, and it will not allow me to add trailing spaces (with NO validation rule).
I have no other validation (this was entered directly into the table).

Have MS really decided to dictate what we can and can't save?


ATB,

Darrylle

RE: Leading Spaces in Access Database

To my knowledge, trailing spaces have never been saved in Access tables.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Leading Spaces in Access Database

dhookum,

Create an Excel spreadsheet, add a couple of cell values (with trailing spaces).
Import this data by using the import wizard into a table created by the wizard.

Hey presto: trailing spaces in Access, and your knowledge has now been suitably expanded.

I've had 30 years of this stupid problem from users that don't 'get' data integrity.
(And yes, of course - neither should they in a perfect world, where only the I.T. literate should be let loose on business-critical data).
However; neither would I ever condone a vendor that dictated what I could or could not save in my database, as Microsoft seems to have done. Unbelievable arrogance!

Trim( ) was the God-send.

Darrylle winky smile

RE: Leading Spaces in Access Database

Oh yeah the Excel import issue. There shouldn't be spaces at the end of Excel values either winky smile

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Leading Spaces in Access Database

dhookum,

That's why databases were introduced - because there IS no data-integrity control in Excel. Yet, 99% of the biggest companies in the world still manage business-critical data in Excel.

And (again) - that's where we come in (and make a living from managerial ignorance / arrogance) and fix their silly mistaken belief that "anyone can DO I.T.".

Lucky for us eh?

Darrylle winky smile

RE: Leading Spaces in Access Database

Although it sounds like you have a working solution you could also just correct the problem instead of the validation rule
In the after update of those fields you could remove the spaces. Trim funtion removes from both the front and back.
me.LastName = trim(me.lastName)

RE: Leading Spaces in Access Database

(OP)
I've tried correcting the user error until I was blue in the face. I get the "deer caught in the headlights" look and end up correcting all the records myself (which is very annoying to say the least lol). Thanks for your help everyone. I will make sure to eliminate both leading AND trailing spaces!

Laura~

RE: Leading Spaces in Access Database

"end up correcting all the records myself (which is very annoying "
How did you do it?
Annoying, yes, but that's just one simple statement:
Update MyTable
Set lastName = Trim(lastName)

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Leading Spaces in Access Database

(OP)
Hi Andy,

I usually fix them because they are completely missing first names or last names because the fields are left completely blank because they use spaces. I play Sherlock Holmes and figure out whose record it is by the other fields. When I ask them to fix it, it makes matters worse. Unfortunately, one of my users likes to try and break the database (she gets errors neither myself or my IT counterparts have ever seen before) because she doesn't like working in Access. Yup, I have one of those lol.

L~

RE: Leading Spaces in Access Database

Quote (LauraW)

Unfortunately, one of my users likes to try and break the database (she gets errors neither myself or my IT counterparts have ever seen before) because she doesn't like working in Access.

Sign her up as a tester! There's gold in them there hills!

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Leading Spaces in Access Database

(OP)
Trust me I would if I could get her to replicate what she did. I just get "I don't know. I did what you told me!" tonguepc2

RE: Leading Spaces in Access Database

"completely missing first names or last names because the fields are left completely blank because they use spaces"
I hope she does NOT do that straight in Access by hand, I hope you gave her a Form to use for data entry (right?)

If so, do not allow "missing first names or last names" in the 2 text boxes, and do not allow just spaces. I sometimes mark the required fields on the Form, and do not allow to click on Update or Insert button (it is not Enabled) until all required fields are filled in.

Something like
cmdInsert.Enabled = Len(Trim(txtFName.Text)) * Len(Trim(txtLName.Text))

So if any of the 2 text boxes are either blank or just have Spaces, cmdInsert is grayed out.

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Leading Spaces in Access Database

(OP)
Good Lord no - she doesn't enter anything directly into the table. I make all my users do their data entry via forms (not even datasheets) and I've hidden the navigation pane so they can't get to the tables, macros, etc. I've written code so that if they try to save a record without the required information, they get an error message with a popup dialog box and it won't allow them to save the record until all the required fields are populated. I've split the database so they only have the front end to work with. First names and last names are required fields but she got around that with the spaces, which I've now put the cabash on!

She really keeps me on my toes. I guess you can call it job security!

Laura

PS - I agree, there should be a sarcasm font!!

RE: Leading Spaces in Access Database

Andy,
Your programming history is showing. IN Access the ".Text" property is only available when the control has the focus. Access likes the ".Value" property or no property since Value is the default.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Leading Spaces in Access Database

By "programming history" do you mean "age"? sad
Mostly I do VB 6, with some VBA in Excel and Word.

"or no property since Value is the default" - I avoid it as plague, default properties that is. Is a sign of very sloppy coding, IMHO smile

Have fun.

---- Andy

There is a great need for a sarcasm font.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close