Problem with Lcase and Ucase data in table

[MsLady]

Hello friends,
I have data in my table field as "Orange" and some other entries in as all lower cases "orange", which really should be representing the same values.

Now when i use SQL in my modules to query and process this data, or create my report, it collects them as different values i.e. "Orange" is selected as one category and "orange" as another category, when really it should be getting them all (Orange, orange, ORAnge) as one category.

Perhaps this is a basic question, but everything i have tried doesn't seem to work. Can anyone tell me the best way to handle this?

 

[JoeCruse]

You could run an update query on the table field and make them ALL the exact same. The find a way to enforce typing "orange" in the same way, every time from now on (combo box with slection limited to the list).

 

[MStCyr]

or turn off the Autocorrect

 

[Robert88]

Hi MSLady,

I have come across this problem before.

Solution, restrict the input of the data to either UPPER or lower case, in my case I made all input entries UPPER CASE.

Here is an example of conversion to UPPER CASE.

Dim LowerCase, UpperCase
LowerCase = "Hello World 1234"   ' String to convert.
UpperCase = UCase(LowerCase)   ' Returns "HELLO WORLD 1234".

from link;

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbenlr98/html/vafctucase.asp

Not sure what control you have over data that users are inputting but if you make it one or the other, then when comparing data it is all the same case.

Good Luck with it.

Robert88

 

[MsLady]

Hi Joe and Robert: Thanks guys
But the thing is, I have absolutely no control over the data table and what goes in there. It's a global employee database, where users enter and update their info or whatever. The username of each person is automatically retrieved from their machine logon, and for some reason i have mixture of different cases for some people. Now it's hard to accurately select the info of one person cos of all these cases issue. I cannot run any updates on the table. I can only select.

@Robert, thanks for that link. I'll follow it up.

Hi MStCyr, Thanks and that's the first thing i tried. Still no luck. Thanks again.

~~~~
While I follow up on Robert's link, if anyone has any more ideas, please share. thanks, and i hope everyone had a nice easter

 

[Lee Pearce]

What I have done is put ">" (Without Quotes) in to the Format Option, under the General Tab when viewing the Table in Table Design View.

This converts any lower case entries into Upper case.

Im in the middle of doing a database & it was decided that all entries would be in upper case & as there would be multiple people entering data, the option above was my answer to ensuring that whatever was input, the data would be saved in Upper Case.

Lee

 

[Robert88]

Hi MSLady,

I hope you had a lovely Easter as mine was great.

I suppose with the conversion from lower case to upper case, you could always convert both table data required and entry comparison string so it does not matter what is in the table and/or what is entered to compare both will end up in UPPER to compare.

It is a lot of work having to do both but still possible.

Good Luck

Robert88