Altering people's data input from lower to upper case

[Troubled]

As a complete novice to Access 2003 I am finding it hard going. I have a database containing a mixture of upper and lower case input. I can make upper case appear on the screen by using > in the format box in the property section for each field. This works great but the same old uncorrected stuff remains on the database. How can I get the database to store the corrected upper case data after I have entered it in lower case? Thanks for your help

[June7]

That will require code (macro or VBA) to save the formatted text. Example:

Private Sub tbxFieldNum_AfterUpdate()
Me.tbxFieldNum = UCase(Me.tbxFieldNum)
End Sub

Use an UPDATE sql action to correct the existing data.

[CJ_London]

does it matter? - users should only be using forms and reports so you can just use the > for formatting. Access isn't case sensitive so Mr=mr=MR

[Micron]

does it matter? - users should only be using forms and reports so you can just use the > for formatting. Access isn't case sensitive so Mr=mr=MR

If Option Compare is set to Option Compare Binary, Access is case sensitive. If it is not specified, the default is binary.

[CJ_London]

Sorry, thought you were using Access, not VB

If you are using access, you might want to look at this link

http://www.fmsinc.com/microsoftacces...ons/index.html

[Micron]

Sorry, thought you were using Access, not VB

If you are referring to me, I am using Access and Access VBA. I think the original poster is using Access too, according to their profile.

[CJ_London]

@Micron, I was referring to your comment

If it is not specified, the default is binary.

which is true for VB but not access

[Micron]

these indicate otherwise, unless I have mis-interpreted the articles. While the first may appear to be applicable to Visual Studio, the title and article text refer to Access and VBA. In fact, tip #3 in the link you sent confirms this. Please explain why then, you say Access is not case sensitive? Or did you mean that it is not "usually" used in a case sensitive context?

https://msdn.microsoft.com/en-us/lib...=vs.60%29.aspx
https://support.microsoft.com/en-us/kb/98227
http://www.fmsinc.com/microsoftacces...ons/index.html

[CJ_London]

I never said Access is not case sensitive and I never said that it couldn't be case sensitive, I just don't agree that that it's default position is case sensitive. Whether you have misinterpreted the articles, that is for you to decide, it is an easy enough thing to test.

whenever you create a new module in access, the default is Option Compare Database (i.e. not case sensitive) - although don't forget this only affects vba code, SQL remains case insensitive - unless you use something like the StrComp('myText1', 'MYTeXt1', 0) function where 0 instructs a binary (i.e. case sensitive) comparison

[Micron]

Sorry, I thought it was you that said "Access isn't case sensitive so Mr=mr=MR"
I can definitely say I did not say it was case sensitive by default. I said if "it" (Option Statement) is not specified, the comparison is case sensitive by default. To quote Microsoft: Function FindEm (mystr$)
FindEm = InStr(1, mystr$, "C")
End Function
If mystr$="1<space>abc<tab>ABC", the function will return the following results, depending on which Option Compare statement is used: Option Compare Database: 5
Option Compare Text: 5
Option Compare Binary: 9

BTW, I believe it was a Microsoft page that stated it defaults to binary if the statement is missing. I've never tested it.

[CJ_London]

Sorry, I thought it was you that said "Access isn't case sensitive so Mr=mr=MR"

I did - but was talking about the default position - I was not talking about ways round it

it defaults to binary if the statement is missing

I agree with that, but the default position is that Option Compare Database is used in access vba (nothing in vb)

I think we are really talking about what is the default position - I'm talking about the position if the user does nothing, you are talking about the position if there is no 'option compare' statement.

[Micron]

I did - but was talking about the default position - I was not talking about ways round it

I agree with that, but the default position is that Option Compare Database is used in access vba (nothing in vb)

I think we are really talking about what is the default position - I'm talking about the position if the user does nothing, you are talking about the position if there is no 'option compare' statement.

10-4! we seem to agree that Access and VBA for Access can be made to compare as binary (hence upper case / lower case) if one controls the settings.
Thanks for everything!