basic caps control format problem

[antonyx]

this code

Me.txtincflightfrom = UCase(Left(Me.txtincflightfrom, 1)) & LCase(Mid(Me.txtincflightfrom, 2))

turns this

tel aviv

into this..

Tel aviv..

i have tried to fiddle with it without success.. basically i want all the first letters of each word to be a capital

so tel aviv, becomes Tel Aviv..

what shall i change on that code..

 

[Moniker]

Me.txtincflightfrom = StrConv(Me.txtincflightfrom,vbProperCase)

This is completely weird since I'm going through a person's code (who is no longer with the company), and that person had written an 18 line Function to do what is built-in to Access. I came across that person's code not 20 minutes before looking in here and seeing someone with the exact same problem. Is that called synchronicity?

 

[antonyx]

thats great.. the problem is that if my user enters.. this..

london sheraton hotel, knightsbridge, WC1 6YQ

then it becomes

London Sheraton Hotel, Knightsbridge, Wc1 6yq

i dont want the postcode to be affected.. so how can i edit that rule so that if the letters are already in capitals then leave them alone.

so i get this..

London Sheraton Hotel, Knightsbridge, WC1 6YQ

 

[Moniker]

Err, you are breaking the foundations of normalization by having everything in one field. You'd really want:

Field1: "london sheraton hotel"
Field2: "knightsbridge"
Field3: "WC1 6YQ"

You'd then do a StrConv on the first two fields. If for whatever reason your adamant about keeping it all in one field (but you won't like it later, trust me), then the way to handle that is like this (assuming the postal code is always at the end):

Result = StrConv("london sheraton hotel, knightsbridge, WC1 6YQ",vbProperCase)

which returns this: "London Sheraton Hotel, Knightsbridge, Wc1 6yq"

Follow that with this (which will work but is hard to read):

Result = Left(Result,InStrRev(Result,",")-1) & UCase(Right(Result,Len(Result)-InStrRev(Result,",")+1))

which returns this: "London Sheraton Hotel, Knightsbridge, WC1 6YQ"

So, all together with your variable names:

Me.txtincflightfrom = StrConv(Me.txtincflightfrom,vbProperCase)
Me.txtincflightfrom = Left(Me.txtincflightfrom,InStrRev(Me.txtincflightfrom,",")-1) & _
UCase(Right(Me.txtincflightfrom,Len(Me.txtincflightfrom)-InStrRev(Me.txtincflightfrom,",")+1))

While that will work, please seriously consider separating each data element into its own field. Search for Normalization on these forums for at least a dozen reasons why that's important.

 

[antonyx]

thank you..

i completely understand regarding the normalization comment..

for now though.. it will have to be like this due to time constraints.. thanks for the code..

 

[missinglinq]

You talking to deaf ears, Moniker! antonyx's been having trouble with this address field for weeks because of having his entire address in a single memo field, and refurses to listen when he's told to break it up into component fields.

 

[antonyx]

ok.. ok..

look.. i dont have the address in a memo field anymore.. i have it in a text field..

also the database i am making is short term.. when we have more work then we can handle.. we wont be using this system..

my user (1 person) copies addresses straight from websites.. he does not wish to separate the address in the correct manor..

i do listen to what you experts tell me.. believe me.. i have created a system that is 'good enough' for the moment.. i have just been in a mad rush because i am actually running the business myself and i have sooo much to do..

anyway.. enough of my problems.. we all have them..

i realise i am not making the best system.. but i appreciate your help in coding the temporary features i am looking for...

 

[Moniker]

You can handle it however you like. It appears as though you've been "warned" so to speak. I'm not sure if you looked at the code I provided you, but note that it works with two caveats:

1) The postal code is always at the end of the string.
2) The postal code has a comma preceding it.