A question About Proper Case

[NYFashionToGo]

I am not sure if this is the right board for my question, But I thought I would ask anyway. I am importing text files into access. is there a way to immediately make them proper case without going through queries or code....

I see the Validation rule, I dont know how to use it properly.
Any assistance would be appreciated, Thank you for looking (o;

 

[Golom]

Don't think so but conversion to proper case is fairly simple

myString = StrConv(myString, 3)

Even if you did come up with a validation rule that detected that the string was not proper case, you would still need to convert it. Validation rules detect if something is or is not valid. They don't convert it to something that is valid.

 

[rac2]

Probably not during the import step.
Are you using File->Get External Data->Import ?

The Validation Rule controls what data can be stored in a field, it does not reformat or change the data. If you were to create a rule that required proper case, any data that did not fit the rule would not be imported.

By "proper case", do you mean all lower case, all upper case, or initial capital? Golom shows the way to get initial capitals.

 

[NYFashionToGo]

Proper Case meaning as excel handles it, Each first letter of the sentence is capitalized.

Right now I am using import Method, However, I am leaning towards importing directly from excel.... I have nbeen saving file from excel and Import to access. I want to eliminate the save part and make excel go straight to access.

Just looking for some alternatives to put my text in the right format or case...

I still do not understand what golom meant. I do not know where to put that... I tried and tried..... I am looking in wrong place I think based on what you just said

 

[rac2]

Excel handles capitalizing the first word of a sentence? I didnt know that. I cant find that in my Excel functions or formatting. Where do you make that happen in Excel?

At any rate, the Access Text function, StrConv("Now is the time", 3) returns "Now Is The Time" which may not be what you are looking for.

Basically, if the data are messed up in Excel they will be messed up in Access, whether you import or link.

Golom suggests that you import the data then run an UPDATE query on the column which needs inital letters capitalized. But if the data in a column consists of many words, each word will be capitalized, not just the first word. The UPDATE query would look like this in SQL view.

UPDATE MyTable SET
  column_with_words = StrConv(column_with_words, 3);

That will capitalize the first letter of every word in every row in MyTable.

It is possible to write a SQL expression which achieves capitalization of the first word only, but it would be a confusing expression, easy to get wrong.

 

[NYFashionToGo]

Its all In the formulas, =proper(a1) or =upper(a1) or =lower(a1)

I wanted to know if there is a wa to do this in access or with VBA excel.............

 

[HarleyQuinn]

You can use the StrConv function within the Access query and Access or Excel VBA. It might be easier to understand this function if you use the named constants in the conversion portion of the function e.g.

StrConv(yourvalue,vbProperCase)

would achieve the same results as Golom's code.

Hope this helps

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Lilliabeth]

Proper Case meaning as excel handles it, Each first letter of the sentence is capitalized.

But that isn't quite how Excel handles proper case. The PROPER function capitalizes the first letter of each word without regard to sentences.

Lilliabeth
-Why use a big word when a diminutive one will do?-

 

[NYFashionToGo]

But you could do something like this: put a sentence without capitols in cell A1 and Put this formula in A2.

=LEFT(PROPER(A1),1)&RIGHT(A1,LEN(A1)-1)

Doing a paragraph is a bit more advanced than a sentence.

 

[Lilliabeth]

Yes. You could do the same thing with UPPER, but there is no built in function in Excel to do sentence case.

Lilliabeth
-Why use a big word when a diminutive one will do?-