update data in table for new Format

I have a table with several thousand records. currently i have serial numbers 
stored as number but i now need to change the format. currently it is 4-8 
digits. the new format should be xx-xxxxxx (2 digits a "-" and 6 more 
digits). I need this new value to store the "-" in the field. I know i can 
switch the field to a text field and this works but i need a way to convert 
existing data to the new format. I tried a mask format but it starts at the 
beinging. so if i put in a ##-###### and my data is 4021 it gives me 40-21 
and i need 00-004021.

Any ideas on this one? Thanks.
0
Utf
2/23/2010 5:14:01 AM
access.formscoding 7493 articles. 0 followers. Follow

1 Replies
820 Views

Similar Articles

[PageSpeed] 2

On Mon, 22 Feb 2010 21:14:01 -0800, Daniel M
<DanielM@discussions.microsoft.com> wrote:

>I have a table with several thousand records. currently i have serial numbers 
>stored as number but i now need to change the format. currently it is 4-8 
>digits. the new format should be xx-xxxxxx (2 digits a "-" and 6 more 
>digits). I need this new value to store the "-" in the field. I know i can 
>switch the field to a text field and this works but i need a way to convert 
>existing data to the new format. I tried a mask format but it starts at the 
>beinging. so if i put in a ##-###### and my data is 4021 it gives me 40-21 
>and i need 00-004021.
>
>Any ideas on this one? Thanks.

I'd suggest adding a new 9-character Text field; run an Update query updating
it to

Format([numberfield], "00-000000")

If this serial number is in multiple table you'll need to change it in all of
them; and if it's involved in any relationships, you'll need to delete the
relationship between the Number fields (select the join line in the
relationships window and press the Delete key) and reestablish it between the
text fields.
-- 

             John W. Vinson [MVP]
0
John
2/23/2010 6:15:49 AM
Reply:

Similar Artilces:

error in importing data
Sorry the error is 0x8004032d ...

Re: Extracting Exchange User Data From AD 02-24-10
If you do not like scripting, you can try GAL Exporter or Fast User Manager & Reports from IMIBO - http://www.imibo.com > > "Ringholz, Blake" <bringholz@nospam.com> wrote in message > news:76AEFC2F-85A7-4666-8262-27FB0737D09A@microsoft.com... >> Hello All - >> >> I need to get an Excel Spreadsheet that lists everyone first name, last >> name, email address, job title, etc pulled from Active Directory. Is >> there an easy way to do this? >> >> Thanks, >> Blake > > > > > ...

Pivot Table Cycling Through Page Fields Automatically
Hi. I am trying to cycle through a complete set of data in one of the parameters in the "Page" field. For example, there are 500 investments, and I want to compute the internal rate of return (IRR) for each investment based on a series of cashflows for each investment. The IRR is a function that is placed outside the pivot table. As each investment number is chosen, the underlying pivot table cashflow data changes, allow the IRR function to pick up these cashflows and compute the IRR. However, if there are 500 investments, this becomes very time consuming - especially if the...

Add a specific Record to a Table based on a check box
I have a Table called ServiceTypes. Based on a User's input on a ProposalForm, ServiceTypes need to be added to a ProposalServicesTable. For instance, I have a Check Box on the ProposalForm. When a Check Box is clicked Yes, Access must search the ServiceTypes Table, select a specific ServiceTypeID, and add the ServiceType to the ProposalServicesTable. How can I add the proper Service record from the ServiceTable to the ProposalServicesTable based on the Check Box? I wouldn't do it that way. I'd use a listbox (with multi-select set to YES) that was sourced to the ServiceTab...

pivot table #21
I have a question on Excel. When I update a pivot table, I used to be able to hold down the shift and ctrl keys and highlight the area, but lately I found that I cannot use this short cut method. Is there another short cut method? Thanks for your help. In step two of the Pivot Wizard, you should be able to select a starting cell on the worksheet, then hold the Shift key, and tap the End key, then the Down or Right arrow key, to select a range of cells. Or, base the pivot table on a dynamic range, which will expand automatically as new records are added. There are instructions here:...

Can I delete my switchboard and make a new one?
I do not like my switchboard and would like to delete this one and start all over. But when I did delete it I couldn't make another one, because I couldn't delete the default. What should I do?Thanks,Dustin Just delete the form and the table that were created by the wizard, then start over.-- Rick B"Dustin" <Dustin@discussions.microsoft.com> wrote in message news:42F94A44-9352-4D02-9104-65593869B271@microsoft.com...>I do not like my switchboard and would like to delete this one and start >all> over. But when I did delete it I couldn't make another one, beca...

New mail appearing as old deleted item
When new mail arrives in inbox an old long deleted item appears in place of the new mail showing in bold as new mail. The real new mail is not visible at all and is therefore lost. This problem also used to happen in Windows Mail so I bought Outlook 2007 but it still happens intermittantly. -- Kate What do you see in your Unread Mail folder? "Kate B" <KateB@discussions.microsoft.com> wrote in message news:D9A95DD7-CFC4-423F-9B2B-F8B72177E2CE@microsoft.com... > When new mail arrives in inbox an old long deleted item appears in place of > the new mail...

Doing Analysis from large amounts of DATA
Hi all, I have an attachment that's apart of this message & within it I have 3 tabs 1) Revenue 2) Expenses & 3) Net Position. I would like to do some Analysis on the above three thou a way where I can manipulate the Data. How would I go about doing this? I've tried a Pivot Table thou it didn�t work out in the sense that it was messy & just failed in calculating stuff, not sure if I did it right thou.. Would really appreciate all the help as I actually have about 20 of these sheets which from that fall into 4 groups being 5 sheets per group. I hope this all makes sense, wo...

transfer inbox in table format to word
In my older version I could cut and paste the table format in Outlook to a word file. Now I don't seem to be able to do it although I can print the file in that format within Outlook. When I export the inbox to word I get the whole text not just the headings. Is it possible to transfer the table format to a word file? ...

lost data when opening excel workbooks ; text import wizard popup
When opening many of my excel files ,which all have the same modification date, I come across the text import wizard which states that my text in these files is 'delimited'. All of the files ,including a few word doc.s have had their data changed to show all " y " with two dots above the letter for as far as the eye can see. No import or export has been done with the files and no modifications were done on that date, as far as I know. Is this a corruption problem or is their some 'fix' that I am overlooking. Thanks for any ideas. ...

Convert Column to row with variable data
Hi I'm using Excel 2k and I have a spreadsheet that looks like this Name Address Fred 21 Blah St London Sue Tower 50 London EC2 and need it to look like this Name Address 1 Address2 Address3 Fred 21 Blah St London Sue Tower 50 London EC2 I have a macro that can convert from column to row but only for a set number of columns. Is there any way to account for the variable amount of data for each address ? Any help much appreciated Thanks David David: I suggest the following formulae - copied down as necessary: C2: =IF(...

about new operator
hi group here is my second problem i am allocating a buffer char *p = new char[1024]; p = regBuffer.GetBuffer(regBuffer.GetLength()); and after doing all the work i am trying to free that memory with 1)delete []p; p=NULL; 2)delete p; p=NULL; but in both the cases i get error as debug assertion failed File dbgheap.c Line: 1011 Expression: _CrtIsValidHeapPointer(pUserData) can anybody tell me what's wrong with this code harshalshete@gmail.com wrote: > hi group here is my second problem > > i am allocating a buffer > > char *p = new char[1024]; > > p = re...

Excel 2002
Have several spreadsheet files I use routinely. Three have recently crashed after I added another sheet. In each case the document recovery created a file missing all the color and text formats that the file contained before the crash. Not sure what other changes may have occurred. Is there something wrong with the copy of Excel on my PC? Could these three files be corrupt? Is there a procedure to "clean-up" these files? Thanks in advance for any suggestions. Mark Hi sounds like they are corrupted. I would suggest to copy the data + formats to a new, 'fresh' workbook. ...

Do you need Access to use an Access Database for data entry only?
We have an Access database completed. Now we will use it for reading as well as for data entry. Do we need to install Access for every workstation needing the database for data entry or reading? No you don't necessarily need Access on every computer. You CAN use the Access Runtime and if it is in Access 2007 you can use it for free. If you are using another version then you would need to purchase the developer's edition to be able to legally distribute the associated Access runtime. If you do have 2007 and want to use the runtime, make sure your Full version of Access doe...

pivot table -repeating "months or days" after grouping
I have data that includes the month, day, hour, min. When I run th pivot table function to reduce the data to hourly I need the date t repeat in the date column. Example: 9/19/2002 0:00 2.7 9/19/2002 0:10 3.7 9/19/2002 0:20 3.8 9/19/2002 0:30 4.6 9/19/2002 0:40 4.8 9/19/2002 0:50 4.8 9/19/2002 1:00 5.6 9/19/2002 1:10 5.2 What I get when I group the Pivot table: Sep 19-Sep 12 AM 2.7 - - 1 AM 3.7 - - 2 AM 3.8 - - 3 AM 4.6 - - 4 AM 4.8 What I want is: Sep 19-Sep 12 AM 2.7 - 19-Sep 1 AM 3.7...

Create interactive pivot table chart based on item selected
I'm trying to remember how to drag a chart object to the top left cell of a pivot table thus displaying a charted image of the detail item selected. Any suggestions? ...

Formulas not updating ?
I have a problem with my spreadsheet under Excel XP. The problem has to do with cell references not updating when a row is inserted into the worksheet. The formulas in the cells are relatively simple: =B13 and =DATE(YEAR(B10),MONTH(B10),DAY(B10)+7) I can copy a row and insert it elsewhere or just insert a blank row and the cell references in rows below are not updated in all cases. It seems that rows that are separated from the inserted rows by a blank row are updated. Is this a known issue or does anyone have any suggestions on how to fix this? The spreadsheet is intended to p...

Importing data into Outlook #2
Is there a process or program with which I can transfer my ACT! data into Outlook contacts? Thanks! ...

problem in changing the text of sentences before tables
I am developing a word automation application. In a method of mine, I change the text of some sentences of an opened word file, but the problem is when I change the text of a sentence which located before a table, it will be moved to the first cell of the table. My code is as follow: void myMethod( long startingSentenceNumber, const char *toBeSearched, const char *replacement, bool replace ) { Range currentSentenceRange; Selection sentenceSelection; Sentences sentencesList = m_document.GetSentences(); long sentencesCount = sentencesList.GetCount(); CString replacementCStr(...

Question Regarding Excel 2007 Formatting Corruption
Hi Folks, I am having a problem with Excel 2007 files losing all formatting (merged cells, colors, borders, and data formatting (99% turns into 0.99)) when I open a file on our office server make edits and then save the new file on the server. Each sheet usually has a mix of locked and unlocked cells and I unprotect the sheet to make edits. Also, something is fundamentally changed with the file as its size doubles or triples. If I reopen the corrupted file and redo any of the formatting and try to save it none of the new formatting is retained either. Has anyone else ever experienced a pr...

Is store procedure always fast than Access linked table via ODBC?
I was assigned to upgrade one program from Access(using ODBC to connect to SQL 2000) to ASP.NET(using store procedure in SQL 2000). Finally, I tested them and found that ASP.NET is slower than Access. The mojority job of program is select some data from SQL 2000 tables, modify and then insert into some tables. Is store procedure always fast than Access linked table via ODBC? -- Message posted via http://www.sqlmonster.com Stored procedures don’t add any overhead and they can save compile time. It's the code in the stored procedure and the underlying tables / indexes that ...

windows update 12-25-09
I believe ninety nine percent of the updates failed on my computer. In fact I am unable to download IE8, and several other programs. I am running an IE7 in my computer and I am getting the warning that my os does not support it. What is my operating system? Windows vista basic home, is'nt funny!... Harry Dupre wrote: > I believe ninety nine percent of the updates failed on my computer. > In fact I am unable to download IE8, and several other programs. I > am running an IE7 in my computer and I am getting the warning that > my os does not support it. > > Wh...

Update another CPropertySheet page
I have a program which contains 3 CPropertySheet pages. When I add the change the value in Page 1, then it should automatically update a corresponding field in the Page 2. How can I access from one page to another? Thanks. Yan yan wrote: > I have a program which contains 3 CPropertySheet pages. When I add the > change the value in Page 1, then it should automatically update a > corresponding field in the Page 2. How can I access from one page to > another? > > Thanks. > > Yan > > You cannot do that. When you change a value in Page 1 there are cases ...

Outlook 2007: When i Save a New Rule outlook Change it automatically...
Hi people! I have this problem with Outlook 2007. I have 15 e-mails addresses in my outlook, but i can=B4t create the rules for send all the received mails to each emails folder. Example: I want to create a simple rule that move all the e-mail that i get from "example1@example1.com" to the folder "Inbox/ example1@example1.com". When i create the rule, all works fine, i select the correct e-mail account name, the correct destination folder, etc... but when I hit the SAVE button in rules window, Outlook 2007 changes the account automatically to another one. Not automatically...

How do I show a data table with legend keys in a line chart?
I am having trouble inserting a data table with legend keys into a line chart that contains a horizontal target line. Can anyone help me? Hi, I tried it in both 2007 and 2003 and had no problems. We need more infomation, can you post a sample of the chart somewhere. The only issue I have is that the target line is displayed on the data table, which I would prefer that it not be. -- Thanks, Shane Devenshire "excelbanker" wrote: > I am having trouble inserting a data table with legend keys into a line chart > that contains a horizontal target line. Can anyone he...