Table all in uppercase when imported into Access

I am importing a Dbase III table into MS Access,  All of 
the text fields are in uppercase (Approximately 17,000 
records).  My question is how can I convert all of the 
text fields to lower case with only the first letter of 
the field in Upper case such as name, address, city and 
state fields?
0
sonnylja
9/24/2004 10:13:43 PM
access.conversion 3037 articles. 0 followers. Follow

2 Replies
727 Views

Similar Articles

[PageSpeed] 44

THERE'S NO EASY WAY
There's No EASY Way <g>

You can write a VBA routine or function using UCase or LCase (convert case),
and Left$, Mid$, Right$, Instr, or Split to pull the string apart, or
StrConv(string, vbProperCase) might be a good one.

The trouble with names and addresses, is that there's always exceptions.
Probably easiest to use StrConv and then scan the results for fix-ups.

Chris

"sonnylja@yahoo.com" <anonymous@discussions.microsoft.com> wrote in message
news:1c5301c4a283$c109ffd0$a301280a@phx.gbl...
> I am importing a Dbase III table into MS Access,  All of
> the text fields are in uppercase (Approximately 17,000
> records).  My question is how can I convert all of the
> text fields to lower case with only the first letter of
> the field in Upper case such as name, address, city and
> state fields?


0
Chris
9/24/2004 10:42:06 PM
Create a query into this table.
Type something like this into the Field row of the grid:
    StrConv([City], 3)

If that gives you the kind of results you want, change it to an update query 
(Update on Query menu). Access adds an Update row to the grid. Move the 
StrConv() expression into the Update row under the City field. Run the 
query, and it updates all 17k records for you.

You can convert several fields at once.
The numbers to use with StrConv() are:
    1 to convert to all upper case;
    2 to convert to all lower case;
    3 to convert to proper case (e.g. "This Is Proper Case")

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"sonnylja@yahoo.com" <anonymous@discussions.microsoft.com> wrote in message
news:1c5301c4a283$c109ffd0$a301280a@phx.gbl...
>I am importing a Dbase III table into MS Access,  All of
> the text fields are in uppercase (Approximately 17,000
> records).  My question is how can I convert all of the
> text fields to lower case with only the first letter of
> the field in Upper case such as name, address, city and
> state fields? 


0
Allen
9/25/2004 5:06:48 AM
Reply:

Similar Artilces:

Access 2000 to 97 Conversion
I built a library database for a client in 2000, it uses several check boxes and compb boxes to perform filtering and works well in 2000. In the 97 it does not first it does not like a method I use called application.name and then it does not like the Replace statement I use in my SQL. Everything else works well. The only difference I find in the two is basicall the Microsoft 8.0 objects in the 97 version and the Microsoft 9.0 objects in the 2000. Hen I try to change the 97 version to the 9.0 I am told there is a name conflict and will not permit the selection and when I try to d...

data in row didnt match import type 01-01-06
while importing almost 800 accounts , i got the following error for majority of accounts though i imported only 7 accounts. 'Data in row did not match the import type' what is the solution? How can i match it? whats is the way of comparision? is it possible you are trying to import accountnames into fields where crm is expecting the actual account guid rather than name? ========================= John O'Donnell Microsoft CRM MVP http://www.microsoft.com/businesssolutions/crm/community/microsoft_crm_community.mspx "Aami" <aamir34@hotmail.com> wrote in message ...

Multiple Consolidation Pivot table
I tried to generate a pivot table based on the following data but it does not come up with what I had in mind:- A B C D E F Code Ac-Debit Amt-Debit Code Ac-Credit Amt-Credit DL1001 ABC 100 GL 1001 Sale 100 When I use multiple consol: $B$2:$C$2 and $E$2:$F$2 I have pivot table ABC 100 Sale 100 Good enough, but I when I try $A$2:$C$2 and $D$2:$F$2 to get this DL 1001 ABC 100 GL 1001 Sale 100 it does not work Appreciate anyone can advise whether this can be done Thanks In a...

Surpressing rows and columns with zero values in a pivot table
Let's say I want a report showing the number of times that my employees, grouped in offices, markets and regions, makes a particular kind of error. At the end of the month, I'd like to create a pivot table that says, "This office had this number of errors of this type." I can create a pivot table that does that, but it includes all the employees that have zero errors, making the report unwieldy. How can I format the pivot table to hide any row or column that has no value in it? Your issue is that it has a value. Something adding up to zero is just as va...

Wen Services Uninstall failure
I get this error when I try and uninstall \ Repair WEb Serviices: Failed to read IIsWebs table (-2147023728).. Any ideas please? -- Thanks guys:) Neillin It seems your IIS metabase database (metabase.bin) is corrupted or you may not have enough priviledges to uninstall. If your metabase is corrupted, there is not much you can do without a backup. However, try the following: 1) Open the command line window and type in NET STOP IISADMIN /Y and execute. 2) Then go to Control Panel > Add/Remove Programs > Add/Remove Windows Components. Next time post in the Windows Community grou...

Import cc:mail cca files to Outlook
Hi all - I am looking for a program that will import some old cc:mail archives (.cca files) into Outlook. I use Outlook to do SMTP/POP mail, so it's all stored in a PST file. I have found some converter programs but they require that I am running Outlook with an Exchange mailbox. Any help? thx -Mike ...

I lost access to my money files. How can I regain access?
I've been using Money 2003 since 2003. I decided to upgrade and I did, but my PDA couldn't connect to Money, so I decided to go back to Money 2003. But now I cannot access my files. The window says that my password is incorrect. How can I get access to my 3 years of financial information? TIA, Angela Money, like most software, is not backwards compatible. ie you cannot use a data file made with a newer version of the program than the one you are using. When you upgraded, part of the upgrade procedure should have been to create a backup of the data file for your previous ver...

import price list
I have the necessity to import the price lists in the CRM, not only contact and leads. Exists a method? Moreover, installations Sales for outlook "standard" and "Professional" can coexist ? Thanks. Paola 3 options: Data Migration Framewrok tool - download from microsoft.com Scribe - scribesoft.com SDK - download from microsoft.com and create your own routibes to update the product tables using the web services. Sorry - no simple wizard here..... Guy. "paola70" <paola70@discussions.microsoft.com> wrote in message news:F3C667EF-A03C-47FA-AB63-D8B1A01794B...

Rules imported from Apple Mail
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Email Client: pop I have just imported my apple mail into Entourage 2008. I have about 400 rules. They are all the same and that is when I apply rules....all my 'sent' emails get filed into various client folders. <br><br>The rules were imported from mail into the rule section called POP. when in Pop, it handles only outgoing emails and the rules need to be in the section called &quot;Outgoing&quot; <br><br>is there anyway to somehow move or transfer the rules? I cann...

Access unwanted automatic data entry
when entering data in my Access table, if i use the tab ket to advance to the next field, it often enters a number in that field. Why does it do that nad how can I make it stop! Are entering data direcctly into Access table? If so, stop doing that, unless you only do it occasionally to repair data in database and you know database design and manage it. "Pat the biologist" <Pat the biologist@discussions.microsoft.com> wrote in message news:6B724974-C937-4C5C-BDBA-1A086C0712A1@microsoft.com... > when entering data in my Access table, if i use the tab ket to advance to...

Saving Formatting in Pivot Tables
When I changed formatting in a pivot table, it returns to the default format when I make a change or close the file. Generally, in formats like font size, column width, etc. Is there a way to save/retain format changes in pivot tables? Thank you. right click in the table and go to the bottom to select "table options". It's there. >-----Original Message----- >When I changed formatting in a pivot table, it returns to >the default format when I make a change or close the >file. Generally, in formats like font size, column >width, etc. Is there a way to...

Pivot table and it's macro
I am creating a table and from the table a pivot table using a macro. When I run the macro the table gets created without a problem but when it goes to create the pivot table I get an error and the debugger highlights this section: ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ "Table8", Version:=xlPivotTableVersion12).CreatePivotTable TableDestination _ :="Perf Tables!R2C8", TableName:="PivotTable1", DefaultVersion:= _ xlPivotTableVersion12 Can anyone see where the problem is happening? My ...

Outlook blocked access to attacments
Help! How can I fix this? Thanks! See http://www.slipstick.com/outlook/esecup/getexe.htm for ways to get the attachments off. For more information on the security features, see http://www.slipstick.com/outlook/esecup.htm If you use OE, hit forward on a message or disable it in tools | options | security. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours http://www.poremsky.com - http://www.cdolive.com Outlook Tips: http://www.outlook-tips.net/ Expert Zone http://www.microsoft.com/windowsxp/expertzone Search for answers: http://groups.google.com Most rec...

Give s.o. access rights to all accounts
Hello, I develop an Application, that synchronizes calendaring data of a third party system to MS-Exchange. For installing it, I need an Exchange account that has access rights to all acount to be synchronized. Can this be achived for all accounts by a central administrative procedure (e.g. via the System Manager) or do I have to advise all users to give access rights to their indiviual account? Max You can create a single user and apply full rights to this one user. Try this: http://support.microsoft.com/kb/821897/en-us Nue "Max Kubierschky" <max@knirz.de> wrote in me...

Access 2007 03-15-07
Hi,Back-end database is located at server. There are 10 clients with 10front-end databases. I want to restrict clients to reach form and reportdesigns and their codes. How can I do it in Access 2007 ?Thx for help! I believe this answer is covered in your previous posted titled "Restricting Front-ends".-- David CongerSoftware Development Engineer in TestMicrosoft Office Access** This posting is provided "AS IS" with no warranties, and confers no rights. **"Art Vandaley" <kestafir@hotmail.com> wrote in message news:e$ixVG0ZHHA.4420@TK2MSFTNGP02.phx.gbl...> ...

Pivot table row header
Hi, In a pivot table, I have 2 fields in the row section. How can I have the value on the first fields to repeat in every row instead of just showing once? Thanks!! Try Debra Dalgleish's page at: http://www.contextures.com/xlDataEntry02.html Fill Blank Cells Fill Blank Cells Programmatically* [*Sub FillColBlanks() 'by Dave Peterson 2004-01-06] -- Rgds Max xl 97 --- GMT+8, 1� 22' N 103� 45' E xdemechanik <at>yahoo<dot>com ---- "Jason" <Jason@discussions.microsoft.com> wrote in message news:08549CBC-68A7-442E-BABF-D48B11E29479@microsoft.co...

Pivot Table % of
I have a pivot table like the followng: Joe Trans Ct A 5 B 3 C 2 Total Joe 10 Kelly A 6 B 1 C 4 Total Kelly 11 I added another Count of Trans shown as a "% of column", but it gives me the percent of the entire column when what I really want is the % of Total Joe, % of Total Kelly, etc. In this instance I simply made the names go across instead of down which solved the problem, but there are instances where I don't want to do that. Any suggestions for getting the percent per item rather than pe...

SOP Distribution Tables Question
We use eConnect to send SOP transactions with a custom distribution. The distribution can involve transferring some funds from a reserve account, so the distribution can be split, one debit and credit to transfer funds from the reserve account, and the second debit and credit to different accounts, to biill for the remainder. All works fine, and the distributions are correct in the Sales Transaction Distributions window. But we'd like to report off of the tables, and I can only see the one distribution in the SOP10102 table. The second amount and account pair for the reserve amount...

Pivot Table Updates Issue
My Data had a coloum containing stuff like Marketing Department Sales Department Shipping Department I built a bunch of pivot tables on this data. Then I changed the data to read: Marketing Dept Sales Dept Shipping Dept and performed a Refresh Data. Now, on the filter pulldowns on any of the Pivot tables, I see Marketing Department Sales Department Shipping Department Marketing Dept Sales Dept Shipping Dept The first three option no longer exist in any of the data, and I do not want them in the list. How to fix this problem without recreating and reformatting 38 seperate Pivot table...

I have a question with repeat open and close table
hello,everybody: I write one thread with use Vc6 IDE and I need repeat read data from table , but it had throw error message when my application is runing. Error message is: This application has requested the Runtime to terminate it in an unusual way. please contact the application's support team for mor information�� My segment of code fellow: /* return false if empty data */ bool CReadData::OpenTable() { ASSERT(m_pConn != NULL); //ado connection ASSERT(m_pTable_SMO != NULL); // table try { if (m_pTable_SMO->GetState() & adStateOp...

pivot table : formula to absolute reference a subtotal
hi all. ive been an avid reader of the forums for some time now; time to register so i can post my question, which is rather urgent. (aren't they all ?) the attachment is a simplified version of the pivot table i have going on. the sourcedata is set so the production amount is reported under 'manufacturing', although there are costs in other types. i want to have a 3rd column that would, for each product, do cost of that type / production amount subtotal at each row. so it would basically be cost breakdown at each row, with the total cost at the subtotal level. i cant link to...

Client Access / privelage rights
Have CRM 1.2 in SBS2003 environment. Client machine logs onto the Domain as a CRM users with AD, Client has administrator rights on the server, can read and write to the server. User Exchange etc. BUT as soon as one tries to log onto the CRM website or with Client outlook (CRM integration) client gets the message "contact sys admin as you don't have enough privileges to access this website" any know what I am missing in the 2003 environment? ps. had no problem in sbs 2000? Make sure a license is granted to the user in CRM. Yes that is done as the user is part of a BU an...

Email randomly not accessible thru OWA
Hello. Using Exchange 2003, OWA 2003, approx. 3,000 outside users with mostly Windwos 2000 or XP Pro. Most users get everything, but some often get "page cannot be found" and these messges contain attachments sometimes, but not always: usually PDF or Word attachments. Downloading mail using Outook or Express seems to help alot, but not with all users. It is happening more more frequently. We run Symantec Antivirus 9.1 and I have removed all the filters for mail within our domain, thinking that would help, but no. Any suggestions are appreciated, as always. MG ...

Importing Excel file without server access
I'm automating the process of emailing query results as an Excel file attachment to one recipeint. The recipient will complete data in the Excel file. My problem is that our company will not give this person access to our server, therefore he cannot upload his updated Excel file to one of our drives where I can then access it, import and update my table. There is nobody that he can email the file to either. I believe, however, that he'll have access to unpload a file to our company's SIFT site. Is there a way, through automation, that I can then import the file from ...

Access Calendar to Julian Conversions
I have a database with dates in the MM/DD/YYYY format and I need to convert them to julian dates. Is there a built in function in Access that will do this? Depends what you mean by "Julian Date". If you'll looking to represent the date as the day number in the year, (yyyy.ddd, so that today is 2004.177), try: Format(MyDate, "yyyy") & "." & Format(Format(MyDate, "y"), "000") -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Dave" <gatorsteele@cs.com> wrote in message news:217...