Proper way to both display and save text data in consistent format

  • Thread starter Thread starter J SCHWARTZ
  • Start date Start date
J

J SCHWARTZ

As an Access neophyte (but a longtime dbms developer), I would like to
experiment with improvements to my personal address list database (my one
and only Access database), which couldn't possibly be more primitive. For
starters, I'd like to know how to force field data (like names/addresses) to
be both displayed on the data entry form and saved in its table in a given
case, most likely Initial cap for each "word" but possibly all caps for a
couple of fields. Some time ago I believe I played with form field
properties and got the display correct, but don't think it filtered back to
the table (correct me if I'm misremembering) and I didn't feel I was
approaching it correctly regardless.

I do understand that the stored format of the data can be massaged for
report display or wherever else it is later presented, so I kinda expect
some may say "who cares how it's stored". I'm not trying to be difficult,
but I'd like to skip any theoretical debates for now because I'm disabled
and have very limited use of my hands and would like to channel what time
I have at a keyboard into bite sized learning sessions that I can
immediately play with and build upon. I do have valid reasons (to me) for
wanting to understand how to do what I'm describing. . I'm really not a
stubborn grouch, just in a lot of pain. Thanks for understanding

jo
 
As an Access neophyte (but a longtime dbms developer), I would like to
experiment with improvements to my personal address list database (my one
and only Access database), which couldn't possibly be more primitive. For
starters, I'd like to know how to force field data (like names/addresses) to
be both displayed on the data entry form and saved in its table in a given
case, most likely Initial cap for each "word" but possibly all caps for a
couple of fields. Some time ago I believe I played with form field
properties and got the display correct, but don't think it filtered back to
the table (correct me if I'm misremembering) and I didn't feel I was
approaching it correctly regardless.

I do understand that the stored format of the data can be massaged for
report display or wherever else it is later presented, so I kinda expect
some may say "who cares how it's stored". I'm not trying to be difficult,
but I'd like to skip any theoretical debates for now because I'm disabled
and have very limited use of my hands and would like to channel what time
I have at a keyboard into bite sized learning sessions that I can
immediately play with and build upon. I do have valid reasons (to me) for
wanting to understand how to do what I'm describing. . I'm really not a
stubborn grouch, just in a lot of pain. Thanks for understanding

jo
Click to expand...

You can store your data in the table in the format you wish by using
the AfterUpdate event of the appropriate control on a form.

To convert entered text to all caps, code the control's AfterUpdate
event:
Me![LastName] = UCase([LastName])

To convert text to Proper Case, i.e. john smith to John Smith, use:
Me![CombinedNames] = StrConv([CombinedNames],3)
Be aware that the above will not correctly store some names, i.e.
MacDonald will become Macdonald, O'Brien becomes O'brien, etc.

To change existing data, you would use the above functions in an
update query.
 
Some time ago I believe I played with form field
properties and got the display correct, but don't think it filtered back
to
the table (correct me if I'm misremembering) and I didn't feel I was
approaching it correctly regardless.
Click to expand...


You likely were playing with the input mask settings. To force a text box to
upper case, you can use the ">". Thus, a input mask that forces all caps
could be:
CCCCCCCCCC
Click to expand...

The above would restrict the text box to 10 characters, and also force upper
case. You memories about the upper/lower case being stored are incorrect, as
the above does force the input to upper case..and that is what will get
stored. However, this does not effect EXISTING DATA. So, data already in the
table will show as upper case..but is still stored as lower (that is likely
what you were trying to remember!).

However, when you use input masks for things like phone numbers, such as:

(999) 999-9999

The above input mask will NOT store the "(", nor the space, nor the "-".

So, turns out your memory likely was referring to the above two issues.
Further, you can force ms-access to store the mask chars, and if you use:

(999) 999-9999;1

The format actually has 3 parts, and are separated by ; Put your cursor in
the input mask (on the data tab of the properties for the text box) in
design mode and hit help to get a run down of how this works.

So, you could use a input mask of >CCCCCCC, and this would force your input
to be saved as uppper case.
 
Fred,

I have two questions about your technique:

1) will the screen also update to show the converted text?

2) I didn't understand your usage of "combinednames". Were you assuming that
the entire name of the person was in one field in this example?

I do understand about the update query requirement for existing data and I
understand that there are names which won't convert as I would like them to
using this scheme. Since this is a constant problem in name databases, I
would think that someone has developed an approach to this. Do you have any
suggestions on that problem?

Thanks for replying. My hands have gotten worse so I haven't been able to
tinker at all. This is going to be one slow learning curve <g>.

[Dictated by Dragon NaturallySpeaking; may not be completely corrected]

Jo

fredg said:
On Sat, 08 Jan 2005 01:16:04 GMT, J SCHWARTZ wrote:

snip>
Click to expand...
You can store your data in the table in the format you wish by using
the AfterUpdate event of the appropriate control on a form.

To convert entered text to all caps, code the control's AfterUpdate
event:
Me![LastName] = UCase([LastName])

To convert text to Proper Case, i.e. john smith to John Smith, use:
Me![CombinedNames] = StrConv([CombinedNames],3)
Be aware that the above will not correctly store some names, i.e.
MacDonald will become Macdonald, O'Brien becomes O'brien, etc.

To change existing data, you would use the above functions in an
update query.
Click to expand...
 
Back
Top