auto convert all text to uppercase when entering a record 3

[excession]

Hi,

Is there a way to force all text entered via a form into uppercase?

If not is there a way to make the where clause case insensitive?

I'm searching through a list of locations eg bristol, bath, cardiff. However some are entered with uppercase and some lower case, so when I do a select distinct I end up with both the lowercase and the uppercase entry

Thanks in advance

 

[dhulbert]

If you use the Ucase function in your query then that should help

Select Distinct UCase(location)
from ......

I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[excession]

I've just tried this:

SELECT DISTINCT UCase(people.location) FROM people ORDER BY people.location;

But I get an error now:
ORDER BY clause <clause> conflicts with DISTINCT. (Error 3093)

A sample of some locations would be:
bristol
Bristol
BRISTOL
etc

I only want Bristol to come up once in the search result.

Any ideas what I'm doing wrong?

 

[dhulbert]

Try adding order by Ucase(people.location)



I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[earthandfire]

You could try:

SELECT DISTINCT UCase(people.location) FROM people ORDER BY UCase(people.location)


Hope this helps.

 

[JerryKlmns]

Maybe trying something like this before storing values to your table, would save much trouble thread702-928808

 

[excession]

I've broken it completely now lol

This is for a combo box on a form. The idea is it allows the user to search by location.

I've set the Row Source to:

SELECT DISTINCT UCase(people.location) FROM people ORDER BY UCase(people.location)

now for some reason I get nothing in the combo box, so I tried putting it back to what I had (i.e. without the UCase commands) but I still get nothing.

I'm only a novice with access so this is probably a simple mistake.

I have tried the query:
SELECT DISTINCT UCase(people.location) FROM people ORDER BY UCase(people.location)
on it's own and it works fine, the problem seems to be displaying the results in my combo box.

Any clues what I've done?

Jerry - will investigate your suggestion. This look like it will be the ideal solution. I still want to solve this problem with the combo box for future reference though.

 

[excession]

Got it, all sorted - it was something daft (me) Thanks guys, both solutions worked a treat.

 

[PHV]

For me, the simplest way to sort the result of a query is to use the ORDER BY OrdinalPosition syntax:
SELECT DISTINCT UCase([location]) FROM people ORDER BY 1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[excession]

Thanks, I will try that.

It would still be easier if I could do a case-insensitive search or find a way to auto convert case - I have used the code suggested by Jerry but a few problems have surfaced. I have set up the 'proper case' code to run when someone exits a text box, however I started getting errors if the text box was left empty (something about NULL - sorry can't remember the exact error now) so I added a few lines to check the text box wasn't null before calling the proper case function. Now I get a type mismatch error (13) when you exit a text box after editing an existing record.

My event handler code looks like:

Private Sub forname_Exit(Cancel As Integer)
    If Not Me.forname Then
        Me.forname = MyProperCase(Me.forname, 1)
    End If
End Sub

Any ideas?

Thanks in advance

 

[PHV]

Private Sub forname_Exit(Cancel As Integer)
If Trim(Me!forname & "") <> "" Then
Me!forname = MyProperCase(Me!forname, 1)
End If
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[excession]

Thanks.

What is the difference then between Me.forname and Me!forname? (This is the first VB script I've ever written)

 

[earthandfire]

Additionally you could run an Update query to propercase all the existing data in your table, you could easily do this using the QBE grid:

Field:Location
Tableeople
Update To:MyProperCase([Location])

This coupled with PHV's last suggestion would simplify your select query to:

SELECT DISTINCT location FROM people ORDER BY location

or using OrdinalPosition as per PHV:

SELECT DISTINCT location FROM people ORDER BY 1

Hope this helps.

 

[excession]

What I've done now is add the propercase code to the data entry form so that all data is converted. Since I only have a small sample database at the moment (6 entries) It was easy enough to manually update them.

I've never used the QBE aspect of Access,in fact this is the first ever Access database I've ever built (usually use Java and mySQL).

Anyhow, thanks again guys for the help. The combination of PIV's version of the eventhandler, the MyProperCase VB script and the SQL query suggested by several of you have solved the problem.

 

[PHV]

Have a look here:

http://support.microsoft.com/kb/129287/en-us

http://blogs.msdn.com/frice/archive/2004/02/18/75685.aspx

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886