Leading Spaces in Access Database

[LauraW]

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~

 

[dhookom]

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

 

[combo]

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

combo

 

[LauraW]

Not like " *" worked. Thank you!!

Laura~

 

[Darrylles]

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

 

[dhookom]

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

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

 

[Darrylles]

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 ;-)

 

[dhookom]

Oh yeah the Excel import issue. There shouldn't be spaces at the end of Excel values either ;-)

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

 

[Darrylles]

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 ;-)

 

[MajP]

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)

 

[LauraW]

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~

 

[Andrzejek]

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:[tt]
Update MyTable
Set lastName = Trim(lastName)[/tt]


Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[LauraW]

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~

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[LauraW]

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!"

 

[Andrzejek]

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 [tt]
cmdInsert.Enabled = Len(Trim(txtFName.Text)) * Len(Trim(txtLName.Text))
[/tt]
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.

 

[LauraW]

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!!

 

[dhookom]

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

 

[Andrzejek]

By "programming history" do you mean "age"? :-(
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

Have fun.

---- Andy

There is a great need for a sarcasm font.