?
Solved

How do you change CaSe in Access

Posted on 2005-03-10
13
Medium Priority
?
2,132 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
Lease-to-own eliminates the expenditure of hardware replacement and allows you to pay off the server over time. Usually, this is much cheaper than leasing servers. Think of lease-to-own as credit without interest.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

765 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