Text Lettering in Access 2003

[Guest]

I know that in the format field of a table you can make the letters of that
field be either upper case or lower case by using the greater than or less
than signs. Is there a way to format the field to Only allow the first
letter of each word in the field to be capitlized. I import several
different talbes on a daily basis from many different sources, I was
wondering if this was possible, because it would assist in the cleanup on my
database standards.

Somebody, Please help

 

[Guest]

Set the Input Mask property --
L Letter (A through Z, entry required).
? Letter (A through Z, entry optional).
A Letter or digit (entry required).
a Letter or digit (entry optional).
& Any character or a space (entry required).
C Any character or a space (entry optional).
.. , : ; - / Decimal placeholder and thousands, date, and time separators.
(The actual character used depends on the regional settings specified in
Microsoft Windows Control Panel.)
< Causes all characters that follow to be converted to lowercase.

 

[tina]

formatting changes only the way values are displayed, it does not affect the
values themselves. there is no built-in Format setting for ProperCase (which
is "the first letter of each word in a string is Uppercase"). you could
*display* field data as ProperCase, without actually changing the data, by
using a small VBA function in a query to return the field values as
ProperCase.

if you want to actually *change* the data to ProperCase, you can't change it
at the time that you import the data. but you *can* change the data values
to ProperCase by running an Update query on the fields you want to change,
again using a simple VBA function to return the field values as ProperCase.

the following function should work in both a Select query (for display
purposes) and an Update query (for changing the data):

Public Function isProperCase(ByVal strText As String) As String

isProperCase = StrConv(strText, vbProperCase)

End Function

just paste the function into a public module in your database. in your
query, supply the name of your field for the function's argument, as

isProperCase([MyFieldName])

hth

 

[Guest]

Tina,
Thank you so much. I will let you know if I get it to work.
Would you happen to know if this can be done in SQL as well and if so do you
know how?


Again thanks for the help
Tara

formatting changes only the way values are displayed, it does not affect the
values themselves. there is no built-in Format setting for ProperCase (which
is "the first letter of each word in a string is Uppercase"). you could
*display* field data as ProperCase, without actually changing the data, by
using a small VBA function in a query to return the field values as
ProperCase.

if you want to actually *change* the data to ProperCase, you can't change it
at the time that you import the data. but you *can* change the data values
to ProperCase by running an Update query on the fields you want to change,
again using a simple VBA function to return the field values as ProperCase.

the following function should work in both a Select query (for display
purposes) and an Update query (for changing the data):

Public Function isProperCase(ByVal strText As String) As String

isProperCase = StrConv(strText, vbProperCase)

End Function

just paste the function into a public module in your database. in your
query, supply the name of your field for the function's argument, as

isProperCase([MyFieldName])

hth

I know that in the format field of a table you can make the letters of that
field be either upper case or lower case by using the greater than or less
than signs. Is there a way to format the field to Only allow the first
letter of each word in the field to be capitlized. I import several
different talbes on a daily basis from many different sources, I was
wondering if this was possible, because it would assist in the cleanup on my
database standards.

Somebody, Please help

 

[fredg]

Tina,
Thank you so much. I will let you know if I get it to work.
Would you happen to know if this can be done in SQL as well and if so do you
know how?

Again thanks for the help
Tara

formatting changes only the way values are displayed, it does not affect the
values themselves. there is no built-in Format setting for ProperCase (which
is "the first letter of each word in a string is Uppercase"). you could
*display* field data as ProperCase, without actually changing the data, by
using a small VBA function in a query to return the field values as
ProperCase.

if you want to actually *change* the data to ProperCase, you can't change it
at the time that you import the data. but you *can* change the data values
to ProperCase by running an Update query on the fields you want to change,
again using a simple VBA function to return the field values as ProperCase.

the following function should work in both a Select query (for display
purposes) and an Update query (for changing the data):

Public Function isProperCase(ByVal strText As String) As String

isProperCase = StrConv(strText, vbProperCase)

End Function

just paste the function into a public module in your database. in your
query, supply the name of your field for the function's argument, as

isProperCase([MyFieldName])

hth

I know that in the format field of a table you can make the letters of that
field be either upper case or lower case by using the greater than or less
than signs. Is there a way to format the field to Only allow the first
letter of each word in the field to be capitlized. I import several
different talbes on a daily basis from many different sources, I was
wondering if this was possible, because it would assist in the cleanup on my
database standards.

Somebody, Please help

The User Defined function tina gave you is for use in VBA.
You don't need to use VBA to accomplish this.

In SQL (as well as in Access) you use the vbProper value (3), not the
VBA constant.

Update YourTable Set YourTable.[Paragraph] = StrConv([Paragraph],3);

Look up StrConv() in VBA help.

 

[tina]

much better, fred, thanks for catching my back - and teaching me something,
too!

Tina,
Thank you so much. I will let you know if I get it to work.
Would you happen to know if this can be done in SQL as well and if so do you
know how?

Again thanks for the help
Tara

formatting changes only the way values are displayed, it does not affect the
values themselves. there is no built-in Format setting for ProperCase (which
is "the first letter of each word in a string is Uppercase"). you could
*display* field data as ProperCase, without actually changing the data, by
using a small VBA function in a query to return the field values as
ProperCase.

if you want to actually *change* the data to ProperCase, you can't change it
at the time that you import the data. but you *can* change the data values
to ProperCase by running an Update query on the fields you want to change,
again using a simple VBA function to return the field values as ProperCase.

the following function should work in both a Select query (for display
purposes) and an Update query (for changing the data):

Public Function isProperCase(ByVal strText As String) As String

isProperCase = StrConv(strText, vbProperCase)

End Function

just paste the function into a public module in your database. in your
query, supply the name of your field for the function's argument, as

isProperCase([MyFieldName])

hth

I know that in the format field of a table you can make the letters of
that
field be either upper case or lower case by using the greater than or less
than signs. Is there a way to format the field to Only allow the first
letter of each word in the field to be capitlized. I import several
different talbes on a daily basis from many different sources, I was
wondering if this was possible, because it would assist in the cleanup on
my
database standards.

Somebody, Please help

The User Defined function tina gave you is for use in VBA.
You don't need to use VBA to accomplish this.

In SQL (as well as in Access) you use the vbProper value (3), not the
VBA constant.

Update YourTable Set YourTable.[Paragraph] = StrConv([Paragraph],3);

Look up StrConv() in VBA help.

 

[Guest]

Fred and Tina,
I thank you both for your input, but I do not understand where I write this
code you have given me. Is there a way to do this in an existing table as
well?

much better, fred, thanks for catching my back - and teaching me something,
too!

Tina,
Thank you so much. I will let you know if I get it to work.
Would you happen to know if this can be done in SQL as well and if so do you
know how?

Again thanks for the help
Tara

:

formatting changes only the way values are displayed, it does not affect the
values themselves. there is no built-in Format setting for ProperCase (which
is "the first letter of each word in a string is Uppercase"). you could
*display* field data as ProperCase, without actually changing the data, by
using a small VBA function in a query to return the field values as
ProperCase.

if you want to actually *change* the data to ProperCase, you can't change it
at the time that you import the data. but you *can* change the data values
to ProperCase by running an Update query on the fields you want to change,
again using a simple VBA function to return the field values as ProperCase.

the following function should work in both a Select query (for display
purposes) and an Update query (for changing the data):

Public Function isProperCase(ByVal strText As String) As String

isProperCase = StrConv(strText, vbProperCase)

End Function

just paste the function into a public module in your database. in your
query, supply the name of your field for the function's argument, as

isProperCase([MyFieldName])

hth

I know that in the format field of a table you can make the letters of
that
field be either upper case or lower case by using the greater than or less
than signs. Is there a way to format the field to Only allow the first
letter of each word in the field to be capitlized. I import several
different talbes on a daily basis from many different sources, I was
wondering if this was possible, because it would assist in the cleanup on
my
database standards.

Somebody, Please help

The User Defined function tina gave you is for use in VBA.
You don't need to use VBA to accomplish this.

In SQL (as well as in Access) you use the vbProper value (3), not the
VBA constant.

Update YourTable Set YourTable.[Paragraph] = StrConv([Paragraph],3);

Look up StrConv() in VBA help.

 

[tina]

a query is simply a SQL statement, a set of instructions to the system. you
can use the StrConv() function in a query to either display or change the
table data to ProperCase. to *display* the values as ProperCase, use a
Select query, as

SELECT StrConv([MyField],3) AS Expr1
FROM MyTable;

just replace the field and table names with the correct names in your
database; and name the calculated query field whatever you want by replacing
"Expr1".

to *change* the data values in a table, use an Update query, as

UPDATE MyTable SET MyTable.MyField = StrConv([MyField],3);

again, replace the table and field names. recommend you back up the table
before running an Update query - just in case.

hth

Fred and Tina,
I thank you both for your input, but I do not understand where I write this
code you have given me. Is there a way to do this in an existing table as
well?

much better, fred, thanks for catching my back - and teaching me something,
too!

On Thu, 14 Jul 2005 14:58:01 -0700, ktfrubel wrote:

Tina,
Thank you so much. I will let you know if I get it to work.
Would you happen to know if this can be done in SQL as well and if

so do
you

know how?

Again thanks for the help
Tara

:

formatting changes only the way values are displayed, it does not affect the
values themselves. there is no built-in Format setting for

ProperCase
(which

is "the first letter of each word in a string is Uppercase"). you could
*display* field data as ProperCase, without actually changing the

data,
by

using a small VBA function in a query to return the field values as
ProperCase.

if you want to actually *change* the data to ProperCase, you can't change it
at the time that you import the data. but you *can* change the data values
to ProperCase by running an Update query on the fields you want to change,
again using a simple VBA function to return the field values as ProperCase.

the following function should work in both a Select query (for display
purposes) and an Update query (for changing the data):

Public Function isProperCase(ByVal strText As String) As String

isProperCase = StrConv(strText, vbProperCase)

End Function

just paste the function into a public module in your database. in your
query, supply the name of your field for the function's argument, as

isProperCase([MyFieldName])

hth

I know that in the format field of a table you can make the letters of
that
field be either upper case or lower case by using the greater than

or
less

than signs. Is there a way to format the field to Only allow the first
letter of each word in the field to be capitlized. I import several
different talbes on a daily basis from many different sources, I was
wondering if this was possible, because it would assist in the

cleanup
on

my
database standards.

Somebody, Please help



The User Defined function tina gave you is for use in VBA.
You don't need to use VBA to accomplish this.

In SQL (as well as in Access) you use the vbProper value (3), not the
VBA constant.

Update YourTable Set YourTable.[Paragraph] = StrConv([Paragraph],3);

Look up StrConv() in VBA help.

 

[Guest]

I know how to do queries and all of that, but I guess I was not clear in my
question. When I run the update query, and choose the "fields" I want to
update, where in the query do I type in the codes. I guess that is what I am
finding confusing.


Thanks again
Tara

a query is simply a SQL statement, a set of instructions to the system. you
can use the StrConv() function in a query to either display or change the
table data to ProperCase. to *display* the values as ProperCase, use a
Select query, as

SELECT StrConv([MyField],3) AS Expr1
FROM MyTable;

just replace the field and table names with the correct names in your
database; and name the calculated query field whatever you want by replacing
"Expr1".

to *change* the data values in a table, use an Update query, as

UPDATE MyTable SET MyTable.MyField = StrConv([MyField],3);

again, replace the table and field names. recommend you back up the table
before running an Update query - just in case.

hth

Fred and Tina,
I thank you both for your input, but I do not understand where I write this
code you have given me. Is there a way to do this in an existing table as
well?

much better, fred, thanks for catching my back - and teaching me something,
too!


On Thu, 14 Jul 2005 14:58:01 -0700, ktfrubel wrote:

Tina,
Thank you so much. I will let you know if I get it to work.
Would you happen to know if this can be done in SQL as well and if so do
you
know how?

Again thanks for the help
Tara

:

formatting changes only the way values are displayed, it does not
affect the
values themselves. there is no built-in Format setting for ProperCase
(which
is "the first letter of each word in a string is Uppercase"). you could
*display* field data as ProperCase, without actually changing the data,
by
using a small VBA function in a query to return the field values as
ProperCase.

if you want to actually *change* the data to ProperCase, you can't
change it
at the time that you import the data. but you *can* change the data
values
to ProperCase by running an Update query on the fields you want to
change,
again using a simple VBA function to return the field values as
ProperCase.

the following function should work in both a Select query (for display
purposes) and an Update query (for changing the data):

Public Function isProperCase(ByVal strText As String) As String

isProperCase = StrConv(strText, vbProperCase)

End Function

just paste the function into a public module in your database. in your
query, supply the name of your field for the function's argument, as

isProperCase([MyFieldName])

hth

I know that in the format field of a table you can make the letters of
that
field be either upper case or lower case by using the greater than or
less
than signs. Is there a way to format the field to Only allow the
first
letter of each word in the field to be capitlized. I import several
different talbes on a daily basis from many different sources, I was
wondering if this was possible, because it would assist in the cleanup
on
my
database standards.

Somebody, Please help



The User Defined function tina gave you is for use in VBA.
You don't need to use VBA to accomplish this.

In SQL (as well as in Access) you use the vbProper value (3), not the
VBA constant.

Update YourTable Set YourTable.[Paragraph] = StrConv([Paragraph],3);

Look up StrConv() in VBA help.

 

[tina]

in the query design grid of an Update query, at the left side you have
labels for each of the first five "rows".
Field:
Table:
UpdateTo:
Criteria:
Or:

when you drag a field from a table into the grid, the Field and Table are
filled in for that column in the grid. enter the Update expression into the
UpdateTo "row" in the grid, in that same column, as

StrConv([MyField], 3)

make sure that [MyField], in the expression, is the same field name that
shows in the Field space above. btw, don't forget to back up your table
first, just in case.

hth

I know how to do queries and all of that, but I guess I was not clear in my
question. When I run the update query, and choose the "fields" I want to
update, where in the query do I type in the codes. I guess that is what I am
finding confusing.


Thanks again
Tara

a query is simply a SQL statement, a set of instructions to the system. you
can use the StrConv() function in a query to either display or change the
table data to ProperCase. to *display* the values as ProperCase, use a
Select query, as

SELECT StrConv([MyField],3) AS Expr1
FROM MyTable;

just replace the field and table names with the correct names in your
database; and name the calculated query field whatever you want by replacing
"Expr1".

to *change* the data values in a table, use an Update query, as

UPDATE MyTable SET MyTable.MyField = StrConv([MyField],3);

again, replace the table and field names. recommend you back up the table
before running an Update query - just in case.

hth

Fred and Tina,
I thank you both for your input, but I do not understand where I write this
code you have given me. Is there a way to do this in an existing table as
well?

:

much better, fred, thanks for catching my back - and teaching me something,
too!


On Thu, 14 Jul 2005 14:58:01 -0700, ktfrubel wrote:

Tina,
Thank you so much. I will let you know if I get it to work.
Would you happen to know if this can be done in SQL as well and

if
so do

you
know how?

Again thanks for the help
Tara

:

formatting changes only the way values are displayed, it does not
affect the
values themselves. there is no built-in Format setting for ProperCase
(which
is "the first letter of each word in a string is Uppercase").

you
could

*display* field data as ProperCase, without actually changing

the
data,

by
using a small VBA function in a query to return the field values as
ProperCase.

if you want to actually *change* the data to ProperCase, you can't
change it
at the time that you import the data. but you *can* change the data
values
to ProperCase by running an Update query on the fields you want to
change,
again using a simple VBA function to return the field values as
ProperCase.

the following function should work in both a Select query (for display
purposes) and an Update query (for changing the data):

Public Function isProperCase(ByVal strText As String) As String

isProperCase = StrConv(strText, vbProperCase)

End Function

just paste the function into a public module in your database.

in
your

query, supply the name of your field for the function's

argument,
as

isProperCase([MyFieldName])

hth

I know that in the format field of a table you can make the letters of
that
field be either upper case or lower case by using the greater

than
or

less
than signs. Is there a way to format the field to Only allow the
first
letter of each word in the field to be capitlized. I import several
different talbes on a daily basis from many different sources,

I
was

wondering if this was possible, because it would assist in the cleanup
on
my
database standards.

Somebody, Please help



The User Defined function tina gave you is for use in VBA.
You don't need to use VBA to accomplish this.

In SQL (as well as in Access) you use the vbProper value (3), not the
VBA constant.

Update YourTable Set YourTable.[Paragraph] = StrConv([Paragraph],3);

Look up StrConv() in VBA help.