By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 1,225 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 IT Pros & Developers. It's quick & easy.

Input Mask help in Access 2007

P: 6
When entering data in the table in datasheet mode I would like the first letter of first name to be automatically capitalized and also automatically capitalize the first letter of last name with a blank space between the first name and last name. Need help with the input mask in design mode.
Dec 31 '11 #1

✓ answered by NeoPa

This is a lot more complicated than you need. Input Masks can handle letters etc, but how would you allow space for variable-length names? A more sensible approach would be to store the two names in separate fields and simply display them as one full name when required.

Input Mask help can be found using Context-Sensitive Help. You will find there all you need to specify the data as you want it.

Expand|Select|Wrap|Line Numbers
  1. Character Description
  2.     0     Digit (0 to 9, entry required, plus [+] and minus [-] signs not allowed).
  3.     9     Digit or space (entry not required, plus and minus signs not allowed).
  4.     #     Digit or space (entry not required; spaces are displayed as blanks while in Edit mode, but blanks are removed when data is saved; plus and minus signs allowed).
  5.     L     Letter (A to Z, entry required).
  6.     ?     Letter (A to Z, entry optional).
  7.     A     Letter or digit (entry required).
  8.     a     Letter or digit (entry optional).
  9.     &     Any character or a space (entry required).
  10.     C     Any character or a space (entry optional).
  11.   .,:;-/  Decimal placeholder and thousand, date, and time separators. (The actual character used depends on the settings in the Regional Settings Properties dialog box in Windows Control Panel).
  12.     <     Causes all characters to be converted to lowercase.
  13.     >     Causes all characters to be converted to uppercase.
  14.     !     Causes the input mask to display from right to left, rather than from left to right. Characters typed into the mask always fill it from left to right. You can include the exclamation point anywhere in the input mask.
  15.     \     Causes the character that follows to be displayed as the literal character (for example, \A is displayed as just A).
Something like :
Expand|Select|Wrap|Line Numbers
  1. >L<??????????????????????
for the Given Name, and :
Expand|Select|Wrap|Line Numbers
  1. >L<CCCCCCCCCCCCCCCCCCCCCC
for the Family name. Of course, that doesn't allow for McDonald or O'Reilly, but it's what you have asked for.

Share this Question
Share on Google+
11 Replies


Expert Mod 2.5K+
P: 2,545
This cannot be done with input masks, which are really intended for numeric or date input. Input masks do not provide intelligent capitalistion and spacing of strings.

If you wish to pursue this you would have to take a programmed approach using VBA code in the After Update event of the control concerned.

-Stewart
Dec 31 '11 #2

NeoPa
Expert Mod 15k+
P: 31,186
This is a lot more complicated than you need. Input Masks can handle letters etc, but how would you allow space for variable-length names? A more sensible approach would be to store the two names in separate fields and simply display them as one full name when required.

Input Mask help can be found using Context-Sensitive Help. You will find there all you need to specify the data as you want it.

Expand|Select|Wrap|Line Numbers
  1. Character Description
  2.     0     Digit (0 to 9, entry required, plus [+] and minus [-] signs not allowed).
  3.     9     Digit or space (entry not required, plus and minus signs not allowed).
  4.     #     Digit or space (entry not required; spaces are displayed as blanks while in Edit mode, but blanks are removed when data is saved; plus and minus signs allowed).
  5.     L     Letter (A to Z, entry required).
  6.     ?     Letter (A to Z, entry optional).
  7.     A     Letter or digit (entry required).
  8.     a     Letter or digit (entry optional).
  9.     &     Any character or a space (entry required).
  10.     C     Any character or a space (entry optional).
  11.   .,:;-/  Decimal placeholder and thousand, date, and time separators. (The actual character used depends on the settings in the Regional Settings Properties dialog box in Windows Control Panel).
  12.     <     Causes all characters to be converted to lowercase.
  13.     >     Causes all characters to be converted to uppercase.
  14.     !     Causes the input mask to display from right to left, rather than from left to right. Characters typed into the mask always fill it from left to right. You can include the exclamation point anywhere in the input mask.
  15.     \     Causes the character that follows to be displayed as the literal character (for example, \A is displayed as just A).
Something like :
Expand|Select|Wrap|Line Numbers
  1. >L<??????????????????????
for the Given Name, and :
Expand|Select|Wrap|Line Numbers
  1. >L<CCCCCCCCCCCCCCCCCCCCCC
for the Family name. Of course, that doesn't allow for McDonald or O'Reilly, but it's what you have asked for.
Dec 31 '11 #3

P: 6
Dear Mr. NeoPa:

I agree with you in storing the first name and last name in two separate fields. Then combine the values of two fields in third field called Full_Name using expression of combing text values.

Thank you for your help!!
Jan 3 '12 #4

P: 6
Dear Mr. Stewart Ross:

I agree with Mr. NeoPa in storing the first name and last name in two separate fields. Then combine the values of two fields in third field called Full_Name using expression of combing text values. It worked well when I tried it.

Thank you for your response.
Jan 3 '12 #5

NeoPa
Expert Mod 15k+
P: 31,186
I'm glad that helped Amar, but don't forget to look out for names (especially Family Names) that are not all alphabetic ;-)
Jan 3 '12 #6

P: 6
Jan 03, 2012

Dear Mr. NeoPa:

I certainly agree with you, fortunately both the first and last names consist of all text values. If there were numeric values then I would substitute ( ? ) with ( 0 ) zero and ( 9 ).

Mr. Stewart Ross mentioned that using VBA code I could get the same results which I don’t doubt. But since I am not familiar with VBA script and coding I settled for your option.

Again thank for cautioning me for the numeric values.
Jan 3 '12 #7

NeoPa
Expert Mod 15k+
P: 31,186
Stewart warned that your original question could not be handled using Input Masks, and indeed it couldn't. Changing the requirement somewhat though, allows a more reliable use of them for your separated fields.
Jan 3 '12 #8

P: 6
Jan 03, 2012

Dear Mr. Stewart Ross:

You and Mr. NeoPa have raised my curiosity – can you recommend a book on VBA coding and script for a beginner.
Jan 3 '12 #9

NeoPa
Expert Mod 15k+
P: 31,186
Here are some tutorial links that may help you :
  1. Microsoft Access Tutorial.
  2. Microsoft Access Tutorials (Strive4Peace).
  3. Microsoft Office Tutorials.
  4. VBA Tutorial (Excel).

They may not all suit you but I expect you'll find some of them very helpful.
Jan 3 '12 #10

P: 6
Jan 03, 2012

Dear Mr. NeoPa:

I am pleased with your prompt response for help with my questions. I am extremely grateful to you for tutorial links. The first link has an error – page not available. The next three links are good and there is a ton of information for me to read and digest, this will take a while. Sometime in future I will write to you with my comments. Thank you very much!!
Jan 4 '12 #11

NeoPa
Expert Mod 15k+
P: 31,186
Thanks for the feedback. I've updated my records to exclude that link.

No need to reply later though. Just ask new questions when they come up.

PS. All posts are saved with their dates and times of posting so there is no need to include the date with your posts. There's no real problem with that either mind you.
Jan 4 '12 #12

Post your reply

Sign in to post your reply or Sign up for a free account.