query to check first letter capital

[a.mlw.walker]

Hi. I have a load of records, that some of the first letter of a field, are not capitals, otherwise they are ok. the are loads of them though, so would take too long to go through by hand, is there a query i can run, to check it and change it if not?
Thanks
Alex

 

[The_Doc_Man]

You are describing what Word calls "Sentence Case" (I think.) Unfortunately, Access doesn't have one of those. UCase$ wouldn't help because it wants to do the entire field. StrConv won't help either because it only addes "Proper Case" to the mix, but that capitalizes every word, not just the first one.

Since this is supposedly a one-time query, consider updating the field (call it [field]) with

UCase$( Left$( [field], 1 ) ) & Right$( [field], Len([field]) - 1 )

In effect, strip the left character from the field to upcase it, tack on the rightmost n-1 characters. THIS PRESUPPOSES YOU HAVE NO LEADING SPACES! If the assumption is not correct, you should FIRST update the field to contain the Trim$([field]) - and THEN run a second update query with the format I used.

Efficient it is not, but it has a chance to work as you wish. You asked to do the test for being upcased already, but it is far easier to just always upcase the first letter. UCase will do the test inside of itself, so you don't have to write an explicit test.

 

[a.mlw.walker]

THanks for reply. As it happens it thats what I want, every word to have first letter capital. I used this line of code to do it, as an after update.
Me.[Company_Name] = StrConv(Me.[Company_Name], vbProperCase)

But I have a lot of records, entered using a query, so they arent getting an "after update", which is where i put the code. I tried an "on enter" but only did it to some of them, so now i want a button (which i have put there) and "on click" it goes through and changes the field text first letter:

Private Sub Command39_Click()
New_Listings.Form.[Company_Name] = StrConv(New_Listings.Form.[Company_Name], vbProperCase)
End Sub

But it doesnt work. I'm sure its something to do with the New_Listings.Form.[Company_Name]
bit, but cant tell what, and also i am worried that it will also mean, I have to go through every record and click the button. Is there a way I can make a button, do it to every record.

Or I just though, can a query do this for me???

Thanks
alex

 

[Brianwarnock]

Yes you can use an update query

eg UPDATE Tablename SET Tablename.fldname = StrConv([fldname],3);

or UPDATE tablename SET tablename.fldname = propercase([fldname]);

where propercase is a function as noted below

but do search the forum for propercase as this subject has been covered before and there is code out there that gives better results than merely using the builtin function, coping for example with hyphenated names or O'Reilly and McDonald etc

Brian

 

[a.mlw.walker]

How do i use that. I cant put it in the criteria box, and also, do i make the query in design view, and enter the table that the update is happening to?

 

[The_Doc_Man]

In the query design grid...

1. Add the table containing this field.
2. Change the query type from SELECT to UPDATE using the menu bar or icon bar controls for query type.
3. Select the field you want to change.
4. Put your function using StrConvert in the UpdateTo slot for that field.
5. Save it and run it.

 

[Brianwarnock]

Hi Doc

why don't people take advice to search the forum, he would have found a number of threads including one in which I posted a sample database containing after update events and update queries. I've just checked that it's still there.

brian

 

[The_Doc_Man]

Brian, even I didn't know you had posted such a thing. I try to keep up, but there's a lot here. If I had known of your post, I surely would have passed along the reference.

But you are right. Searching the forum should almost be an automatic answer that occurs the moment someone posts a new thread.

 

[Brianwarnock]

Doc I would not have expected you to know about my post or to search, but I did suggest it to the person requiring help, mainly because of the more full functions that are out there.

Brian

 

[a.mlw.walker]

Hi Doc and Brian. the first thing i do is search the forums, because A. the chances are what ever i'm asking is unique and someone has had the same problem before, and B. its quicker, but sometimes a question is phrased in a different way, and things can be quite difficult to find. Also trying sometimes to follow someone elses line of thought, when you barely understand your own can be quite difficult. But thanks guys for helping out