Don't allow numbers/remove numbers from a field

[gavin]

Please forgive me for asking two questions in one post!

The first one I think is simple. I have a field in my table that I don't
want users to enter numbers into. I know I could set an input mask to
"???????????????" and that would do the job but is that the "recommended"
way? Is there a way of indicating to the user what the problem is, i.e like
validation text?

The second question I must admit I don't have much of a clue with. I have a
field which has data in it in the following format: number space letters. I
really want to have the numbers in a separate field. I was thinking that
some kind of update query would do this but I have no idea of the syntax. I
guess that the expression would need to FIND the space (" "), cut everything
before it and put it into the new field. Maybe an update query is not the
way to do it?

I would be very grateful if someone could give me a few pointers.


Many thanks,



Gavin

 

[John Spencer]

You probably would have been better off posting two separate questions.

First: You can set a validation rule on the field in the table and you can
set the Validation Text on the field in the table. OR you can do this on a
control in a form.
Validation Rule: Is Null or NOT Like "*#*"
Validation Text: No numbers allowed

Second question: Yes, you can use an update query. Do you want to update
all existing data? (BACKUP YOUR DATA FIRST).
The update query would look something like the following.
UPDATE [YourTableName]
SET [NewField] = Left([ExistingField],Instr(1,[ExistingField] & " "," "))
[ExistingField]=Mid(ExistingField],Instr(1,[ExistingField] & " ","
"))
WHERE [ExistingField] is Not Null

 

[gavin]

Hi John,
I really appreciate your help - thanks so much.


Best wishes,



Gavin

You probably would have been better off posting two separate questions.

First: You can set a validation rule on the field in the table and you can
set the Validation Text on the field in the table. OR you can do this on a
control in a form.
Validation Rule: Is Null or NOT Like "*#*"
Validation Text: No numbers allowed

Second question: Yes, you can use an update query. Do you want to update
all existing data? (BACKUP YOUR DATA FIRST).
The update query would look something like the following.
UPDATE [YourTableName]
SET [NewField] = Left([ExistingField],Instr(1,[ExistingField] & " "," "))
[ExistingField]=Mid(ExistingField],Instr(1,[ExistingField] & " ","
"))
WHERE [ExistingField] is Not Null

Please forgive me for asking two questions in one post!

The first one I think is simple. I have a field in my table that I don't
want users to enter numbers into. I know I could set an input mask to
"???????????????" and that would do the job but is that the "recommended"
way? Is there a way of indicating to the user what the problem is, i.e
like
validation text?

The second question I must admit I don't have much of a clue with. I have
a
field which has data in it in the following format: number space letters.
I
really want to have the numbers in a separate field. I was thinking that
some kind of update query would do this but I have no idea of the syntax.
I
guess that the expression would need to FIND the space (" "), cut
everything
before it and put it into the new field. Maybe an update query is not the
way to do it?

I would be very grateful if someone could give me a few pointers.


Many thanks,



Gavin