?
Solved

How do you change CaSe in Access

Posted on 2005-03-10
13
Medium Priority
?
2,135 Views
Last Modified: 2013-12-24
I have a MS Access DB with a column of data in all CAPS and I need it in Title case.

Suggestions?
0
Comment
Question by:jollymon6672
13 Comments
 
LVL 9

Expert Comment

by:CFDevHead
ID: 13511581
for output or testing against case
if you just want to change the case  when you are displaying use this <font style="text-transform:capitalize; ">MATT</font>
you can use lowercase or uppercase

or you can use this in your query
select lcase(firstname) from table
0
 
LVL 21

Expert Comment

by:pinaldave
ID: 13513519
Ucase(left(firstname,1))Lcase(right(firstname,len(firstname)-1))

0
 

Author Comment

by:jollymon6672
ID: 13516778
All my data in one column is in all CAPS. I need to change it to Title case (e.g. MATT => Matt).
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
LVL 25

Expert Comment

by:James Rodgers
ID: 13517374
here

UPDATE [tablename] Set [FieldName] = StrConv([FieldName],vbProperCase)
0
 

Author Comment

by:jollymon6672
ID: 13520295
Here is what I tried, but it failed. Is there a way to skip 3 letter abbreveations?

<cftransaction action="begin">
<cflock scope="session" timeout="30">
<cfset recordupdate = 1>
      <cftry>
      <cfquery name="updateState" datasource="datasource">
            UPDATE tbl_Zipcodes Set City = StrConv([City],vbProperCase)
      </cfquery>
      <cfcatch type="database">
            <cfset recordupdate = 0>
      </cfcatch>
      </cftry>
      
      <cfif recordupdate IS 1>
            <cftransaction action="commit"/>
            <!--- Success --->
            SUCCESS: UPDATE COMPLETE:<br>
            <!--- Return Page --->
      <cfelse>
            <cftransaction action="rollback"/>
            <!--- Failure --->
            ERROR: UPDATE FAILED:<br>
      </cfif>
</cflock>
</cftransaction>
0
 
LVL 25

Expert Comment

by:James Rodgers
ID: 13520500
<cfquery name="updateState" datasource="datasource">
          UPDATE tbl_Zipcodes Set City = iif(len(CITY) > 3,StrConv([City],vbProperCase))
     </cfquery>
0
 

Author Comment

by:jollymon6672
ID: 13528819
I continue to get an error:

 Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
 
The error occurred in D:\Inetpub\wwwroot\website.org\utl\utl_updateCities.cfm: line 5

3 : <cfset recordupdate = 1>
4 :       <cftry>
5 :       <cfquery name="updateCity" datasource="datasource">
6 :             UPDATE tbl_Zipcodes Set City = iif(len(CITY) > 3,StrConv([City],vbProperCase))      
7 :       </cfquery>

SQL          UPDATE tbl_Zipcodes Set City = iif(len(CITY) > 3,StrConv([City],vbProperCase))
DATASOURCE         singlesscene
VENDORERRORCODE         -3010
SQLSTATE         07002
0
 
LVL 25

Expert Comment

by:James Rodgers
ID: 13534125
sorry, needs the 'else'

<cfquery name="updateState" datasource="datasource">
          UPDATE tbl_Zipcodes Set City = iif(len(CITY) > 3,StrConv([City],vbProperCase),[City])
     </cfquery>

0
 

Author Comment

by:jollymon6672
ID: 13537044
Nope, stll getting errors, here is everything:

CODE:
<cftransaction action="begin">
<cflock scope="session" timeout="30">
<cfset recordupdate = 1>
      <cftry>
      <cfquery name="updateCities" datasource="datasource">
            UPDATE tbl_Zipcodes Set City = iif(len(CITY) > 3,StrConv([City],vbProperCase),[City])
      </cfquery>
      <cfcatch type="SHOWERROR">
            <cfset recordupdate = 0>
      </cfcatch>
      </cftry>
      
      <cfif recordupdate IS 1>
            <cftransaction action="commit"/>
            <!--- Success --->
            SUCCESS: UPDATE COMPLETE:<br>
            <!--- Return Page --->
      <cfelse>
            <cftransaction action="rollback"/>
            <!--- Failure --->
            ERROR: UPDATE FAILED:<br>
      </cfif>
</cflock>
</cftransaction>

ERROR:
ODBC Error Code = 07001 (Wrong number of parameters)

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

Hint: The cause of this error is usually that your query contains a reference to a field which does not exist. You should verify that the fields included in your query exist and that you have specified their names correctly.

SQL = "UPDATE tbl_Zipcodes Set City = iif(len(CITY) > 3,StrConv([City],vbProperCase),[City])"

Data Source = "Datasource"

The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (8:2) to (8:56) in the template file F:\website\utl\utl_updateCities.cfm.
0
 
LVL 25

Accepted Solution

by:
James Rodgers earned 200 total points
ID: 13537117
try this as the SQl statement

UPDATE tbl_Zipcodes Set City = iif(len(CITY) > 3,StrConv([City],3),[City])
0
 

Author Comment

by:jollymon6672
ID: 13537891
BINGO!

Thank you so much !!!
0
 

Author Comment

by:jollymon6672
ID: 13537952
Here is the source code for everyone:

NOTE: Replace CAPITALS with your values

<cftransaction action="begin">
<cflock scope="session" timeout="30">
<cfset recordupdate = 1>
      <cftry>
      <cfquery name="NAME" datasource="DATASOURCE">
            Update TABLE Set COLUMN= iif(len(COLUMN) > 3,StrConv([COLUMN],3),[COLUMN])
      </cfquery>
      <cfcatch type="database">
            <cfset recordupdate = 0>
      </cfcatch>
      </cftry>
      
      <cfif recordupdate IS 1>
            <cftransaction action="commit"/>
            <!--- Success --->
            Success: Update Complete:<br>
            <!--- Return Page --->
      <cfelse>
            <cftransaction action="rollback"/>
            <!--- Failure --->
            Error: Update Failed:<br>
      </cfif>
</cflock>
</cftransaction>
0
 
LVL 25

Expert Comment

by:James Rodgers
ID: 13543730
glad i could help

thanks for the points
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
Loops Section Overview
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses
Course of the Month9 days, 19 hours left to enroll

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question