editing to force at least one space in the field

J

JSH20120710

Registered User.
Local time
Today, 11:23
Joined
Jul 10, 2013
Messages
30
Not = " " And InStr(1,"seat_person"," ") <> 0

I have a field into which I wish to place a person's name and I want to ensure that the field has a space in it but is not a blank field,

so
all blank - illegal
"Bob" - Illegal
"Bob Smith" - legal

the edit at the top is my attempt to code it but it isn't working.
 
is this your validation rule?

you need to put the control name in square brackets

InStr([seat_person]," ")>0 And [seat_person]<>" "
 
CJ_London said:
InStr([seat_person]," ")>0 And [seat_person]<>" "
Click to expand...

The same test could be expressed as:
InStr([seat_person]," ")>1

However either expression would permit a string entirely made of spaces or allow the space to be the last character.

This will avoid both those issues:
InStr(Trim([seat_person])," ")>0
 
thank you for the solutions.
It would have taken me a year to find the "[" requirement.
And the nice change to force the " " into the middle of a non-space string, is excellent. While I HOPE I might have eventually thought of that it would have taken me another week and you save it.

Thanks again.
 
this was posted:
you need to put the control name in square brackets

What is the difference between a field name and a control name?

When must names be in square brackets?
 
JSH20120710 said:
What is the difference between a field name and a control name?
Click to expand...

In Access fields are in tables. Textboxes, combos, buttons etc are all called Controls. Unfortunately many developers use the term "fields" incorrectly.

Fields are what they are called on html forms so that helps to cause the confusion.

When must names be in square brackets?
Click to expand...

The square brackets are required for expressions in Conditional Formatting and Validation Rules and probably many other circumstances that use expression otherwise Access will consider the name to be a string.

This is aside from the usual requirement in queries and other references to include the square brackets where the names have special characters or spaces.
 
Thank you.

if I were to purchase a book to answer all my questions, which book would you recommend.
 
I am now attempting/failing to use the suggestion
I have placed the suggested solution

InStr(Trim([seat_person])," ")>0

into the "validation rule"

I did it wrong as it is not working and rejects everything.

Sorry, I do not understand.

Could you tell me what I am doing wrong.
 
Most experienced developers don't use validation rules per se but validate using VBA code in the AfterUpdate of the control on the form because it allows a greater diversity of validation and better management of the error message and subsequent execution.

An actual Validation Rule only allows an expression that refers directly to the value itself.

So in the AfterUpdate Event procedure of the control use something like this:

Code: 
If InStr(Trim([seat_person])," ")= 0 Then
   MsgBox "whatever"
   Me.seat_person.SetFocus
   etc

If there are more complex validations that depend on conditions relating to multiple controls we use the BeforeUpdate event of the form.
 
another way would be to split the name into two fields, for surname and forename - and just make both fields mandatory.

no difficulties then.
 
A very interesting thought. Unfortunately it makes handling SOME names more difficult.

Robert N. Jones
R. Norman Jones
R. P. Jones
Rev. Robert N. M. I Jones VI (the name of a brother of a friend of mine who's father didn't like the military rules and named his kid with two middle names and a middle initial without a name (hence the lack of a period) [the term NMI in the military was used to indicate "no middle initial"] and the suffix "VI" (even though there weren't any before that (e.g., 5,4,3,2) ... The son went off and became a minister further confusing the issue. So I just remember him whenever I work with systems using "names".)
 
Strange name structures is a specialty of the USA.
If recording the complete set of names is important then perhpas consider a related table for the names to fully normalize them.

One record for each name and a field for NameSequence. Another field wold indicate Surname, ForeName, Appendage or Title.

Rev. Sir Robert N. M. I Smith Jones VI Esq

Code: 
FK  | Part  | Seq | TheName 
1   | Title | 1   | Rev.
1   | Title | 2   | Sir
1   | Fore  | 1   | Robert
1   | Fore  | 2   | N.
1   | Fore  | 3   | M.  
1   | Fore  | 4   | I
1   | Sur   | 1   | Smith 
1   | Sur   | 2   | Jones
1   | App   | 1   | VI
1   | App   | 2   | Esq.

Use a number to store the Part and have a lookup for what to actually call it. For simplicity I have shown word.
 
LOL
I love technoids.
Give em a problem, even a silly one, and they will find a solution.
Thank you.

All this just because I had a silly (crazy?) idea to ensure that there was at least one embedded space in the name.

But as someone pointed out to me, that would also mean that I could not enter Madonna.

Oh well.

Thank you for allowing my brain to wander a bit today (more than it normally does, which is already a large amount).
 
You must log in or register to reply here.

Similar threads

jwcolby54
Using clsMsgPD - The Big Edit
2 3
Replies
43
Views
1,060
jwcolby54
jwcolby54
M
Textbox used to specify delimiter, can't use space in procedure
Replies
7
Views
319
mreinsmith
M
Isaac
Getting to the truth
Replies
11
Views
293
Isaac
Isaac
N
Solved How to force MS Access display a primary key on the forms after entering data in the first field
Replies
6
Views
712
Pat Hartman
P
Steve R.
What Happened to the Pen and Phone?
Replies
1
Views
447
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom