Upper Case

[Ganley]

Any ideas how to Capitalise data in a table.

 

[spasticus]

say the text field is 3 charaters long, all 3 charters had to be entered and they all need to be capitals. in the input mask for that field put:

>LLL (i think its >, it could be <; i always forget which one)

 

[BlueBug]

It is >, for greater than. This is one of the few things I know about Access Like Spasticus said, all the data will be capital, I haven't come across any way to just make the first letter upper case. You can just insert > into the format box for the field and it won't matter how long the field is then. Hope this helps.

 

[tehNellie]

If the data already exists you can use

UPDATE table1 SET [column] = ucase([column])

 

[Mile-O]

Be aware that the formatting of data in a table to be Upper Case is no substitution for the data actually being in Upper Case. The formatting merely dresses the data up for viewing when you open the table (and why on Earth would you want to be doing that anyway?) so, were you to export the data to Excel, you would end up with the data being as it's always been.

As tehNellie says above, the way to properly change your data to Upper Case in a table is to use an Action Query, namely the UPDATE Query.

 

[Dennisk]

this question was answered yesterday. The function you require is call a Proper function as it capitalises the first letter of a word. Some are better than others. or you can create your own by setting the first letter of a word to UCASE

Oldstring = "abcde"
newstring = UCASE(left$(OldString,1,1)) & MID$(OldString,2,LEN(Oldstring)-1)

 

[Ganley]

Sorry for the confusion.

But what I now need to do is, currently in my table I have some names that are all Upper Case.

I just need to keep the first letter as upper case.

I’ve created an update query,
UPDATE table1 SET [column] = lcase([column])

I am not sure what to do.
If I get the length of the string & say -1.

Can anyone help me.

Thanks for your help guys really appreciated.

 

[Mile-O]

Use the StrConv() function in your Update Query and use the vb constant for vbProperCase. If you need further information on this function, it's in the help files.

 

[Brianwarnock]

Handling names so that they start with a capital letter has been answered ad nauseum on the forum recently with examples that allow for hyphenated names and Mac etc. A search on capitalisation or similar should find them

Brian

 

[lisacu]

Upper Case table text

I'm new to this site and want to say thanks to Mile-O (and tehNellie) for sharing their info re: the UCase function!

As a first time Access user, I created a table with individual first name/last name fields, but also wanted to concactenate them in an additional field of the same table. The concactenated field would be used later in a combo box that we would utilize as a "look-up" field of a form (lastname, firstname). To get the combo box results to appear in upper case text, I used the > format symbol in the design view of the table. It's worked perfectly for over a year, but now suddenly the concactenated names no longer appear in my combo box dropdown list. Even more confusing, I can logon to other computers and see the list just fine! When I finally removed the > format symbol from the table, everything worked fine, except now the text in my combo box dropdown list was no longer all upper case.

When I Googled the problem, I found your sight and located the UCase fuction! Since I had already used an UPDATE query to concactenate my individual fields, I simply included the new function and everything is as it should be...once again! Thanks for your great feedback!

Be aware that the formatting of data in a table to be Upper Case is no substitution for the data actually being in Upper Case. The formatting merely dresses the data up for viewing when you open the table (and why on Earth would you want to be doing that anyway?) so, were you to export the data to Excel, you would end up with the data being as it's always been.

As tehNellie says above, the way to properly change your data to Upper Case in a table is to use an Action Query, namely the UPDATE Query.

 

[boblarson]

I'm new to this site and want to say thanks to Mile-O (and tehNellie) for sharing their info re: the UCase function!

As a first time Access user, I created a table with individual first name/last name fields, but also wanted to concactenate them in an additional field of the same table. The concactenated field would be used later in a combo box that we would utilize as a "look-up" field of a form (lastname, firstname). To get the combo box results to appear in upper case text, I used the > format symbol in the design view of the table. It's worked perfectly for over a year, but now suddenly the concactenated names no longer appear in my combo box dropdown list. Even more confusing, I can logon to other computers and see the list just fine! When I finally removed the > format symbol from the table, everything worked fine, except now the text in my combo box dropdown list was no longer all upper case.

When I Googled the problem, I found your sight and located the UCase fuction! Since I had already used an UPDATE query to concactenate my individual fields, I simply included the new function and everything is as it should be...once again! Thanks for your great feedback!

Just a little information for you. You really didn't need to store the concatenated name in the table. You can just use a query as the basis for your report and you can create your own concatenated field in a query. So, for one that will save on potential errors if somone's name changes but the concatenation doesn't get changed in the table. That has to do with NORMALIZATION, which as a new Access user it would be wise to study up on. You can find some info about it here: http://support.microsoft.com/kb/283878

 

[lisacu]

Thanks Bob,
Your suggestion and link re: normalization were helpful!
Lisacu

 

[boblarson]

 

[lisacu]

I spent some time on your web site today and found it very informative, but didn't find anything that solved the problem I just added to the forum. See below...

During a client's stay with our agency, they are served among multiple contracts and are placed within multiple programs. Because clients move frequently between contracts and between programs, we have separate tables which have start and end date fields. It is common for any client to have multiple listings in these two tables, but with different event dates.

I am struggling to create a query that will capture each client's most CURRENT contract AND program. In the future, I'll want to create a parameter query that will allow the end user to enter a range of dates and capture all of the contract and program movement during a period of time.

Any suggestions? Lisa

 

[boblarson]

You need a Max Query to pull the most recent dates for the client and then you can join that to what ever else you need. So, the Max query will just have the client ID and the Max Starting date.

Does that help?

 

[raskew]

Lisa

Posting the same problem in two different threads tends to confuse the issue.
See: http://www.access-programmers.co.uk/forums/showthread.php?t=147931

Bob