Add leading zeros

Hello,
I have a field of 1 to 3 digit numbers.  I'd like to add leading zeros to 
each to make them 6 digits.  For instance:
4 becomes 000004
10 becomes 000010
777 becomes 000777

Thanks in advance,
Ellen
0
Utf
11/30/2007 12:47:00 PM
access.queries 6343 articles. 1 followers. Follow

3 Replies
2106 Views

Similar Articles

[PageSpeed] 50

Hi -
Examples from the debug window:

x = 4
? format(x, "000000")
000004
y = 10
? format(y, "000000")
000010

HTH - Bob

EllenM wrote:
>Hello,
>I have a field of 1 to 3 digit numbers.  I'd like to add leading zeros to 
>each to make them 6 digits.  For instance:
>4 becomes 000004
>10 becomes 000010
>777 becomes 000777
>
>Thanks in advance,
>Ellen

-- 
Message posted via http://www.accessmonster.com

0
raskew
11/30/2007 1:02:32 PM
Ellen -
  Assuming that your numbers are stored as numbers, you may want to rethink
an update query.  Adding leading zeros returns a string -- true numbers don't
have leading zeros.  Perhaps better to just format your form/report fields as
shown in my previous post, leaving them stored as true numbers.

 Debug (immediate) window -  Ctrl-G

Bob


EllenM wrote:
>Thanks, but could you write an update query for me?  I'm not sure how to use 
>the debug window.
>
>Thanks,
>Ellen

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200711/1

0
raskew
11/30/2007 2:00:26 PM
What is the field Type?  If it is a number field, then you can't store 
leading zeroes.
If it is a text field with number characters then you could use an update 
query to permanently change the values.

UPDATE [YourTableName]
SET [YourFieldName] = Format([YourFieldName],"000000")
WHERE [YourFieldName] is not Null AND [YourFieldName] <> ""

If you need help doing this using the design view, please post back for 
instructions.

-- 
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"EllenM" <EllenM@discussions.microsoft.com> wrote in message 
news:08A5CEF4-07BE-42B1-910A-847FE89995EC@microsoft.com...
> Hello,
> I have a field of 1 to 3 digit numbers.  I'd like to add leading zeros to
> each to make them 6 digits.  For instance:
> 4 becomes 000004
> 10 becomes 000010
> 777 becomes 000777
>
> Thanks in advance,
> Ellen 


0
John
11/30/2007 4:53:47 PM
Reply:

Similar Artilces:

How can I add transaction while using the online version of money
Ok... when I first bought Money several years ago and installed it on my home computer, I could then login while at work via the passport and see the information that I had entered on my home computer. If I purchased something during the day I could go online and add the transaction which was great way to know at any given moment what my TRUE balance was in my account. Now when I check my Money account online from work I can see all the information I've entered at home but now there is no way to add a new transaction other then from my home computer where I have Money installed. ...

Chart won't show zero percent
I am doing a pie chart using a quick style design that shows the lines in between the different percentages. Some of the charts show a percentage with the lines when the value is zero and two of my charts erase all but one of the lines when a zero is placed in one of the cells. What's up with that? and How do I correct it? Since with zero value the lines of the pie would be on top of each other, I am not sure what one would expect to see! best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "PURVIANCE" <PURVIANCE...

Excel 2003 adds extra quotes to CSV upon resaving
I have a CSV that when viewed in notepad has the following 2 lines "R1","J","3","","N","4",":4","0" "R1","L","2","","N","4",":4","0" but no matter if i open in by double-clicking or importing the data into a blank worksheet, then resaving, when i open the CSV in notepad, it comes out looking like this, with extra quotes, rather than just the quoting following along with the original 2 lines. """R1"","...

Problem in permanent type add-in toolbar
Hi, I have been facing a issue with dealing with my add-in toolbar. I want to make my toolbar permanent so that use can hide and reposition it permanently(even after restarting Outlook). At the same time, I want to support my add-in toolbar for all opened Outlook explorer. I want to make sure that at any point of time there must be one and only one toolbar on any Outlook window. I have tried below methods but none worked: 1. Tried deleting toolbar on shutdown. This doesn't delete the toolbar and each restarting outlook adds new toolbar. Private Sub AddinInstance_OnBegi...

VC++ .NET 2002: How To Use the Class View's Properties window to add a message handler for the WN_KEYDOWN message
Hi all I used "The EX05c Example: Scrolling" of the book "Programming With Microsoft Visual C++ .NET Core Reference" (6th Edition) as my 3rd example of MFC exercises to catch up my VC++ .NET 2002 (VC++ 7.0) programming. I started my 'Eg05c' project in the MFC Application Wizard and followed the 1 thru 7 steps instructed in the book (pages 82-85) I believed I did the steps 1, 2 and 3 correctly: 1 - In MFC Application Wizard -Eg05c, I setthe CEg05cView to "CScrollView" 2 - I added the m_rectEllipse and m_nColor data members in Eg05cView.h by typing in "...

Excel Add-In for SQL Server Analysis Services Error -- Need Help
I have the add-in installed and it was working. However, lately I keep getting an error stating: Excel Add-in for Analysis Services has not been initialized I have uninstalled and reinstalled but no luck. Anyone help out here? Alan ...

Leading Zero #3
A value in text format, e.g. 07654 in an export csv file looks just fine in Notpad. But when the file is opened in Excel, the leading zero is gone. Is there a way to add the zero back to all the values under the same column within Excel? Thanks, Eli ...

Ignore Zero's
Hi I have used the following to get the average value from a set of data whilst ignoring zero values within the data set. =SUM(K4:AP4)/COUNTIF(K4:AP4,">0") I would like to use the percentile command on the same set of data. Is there a way that I can use Percentile and also ignore zero values ? Many Thanks Chris Hi, with an ARRAY formula =PERCENTILE(IF(K4:AP4>0,K4:AP4),0.1) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formu...

Charting with zeros or DIV/0 values
Hi - this might have an easy solution, but I'm a bit stuck. I've created a spreadsheet for data entry by another party. This data will be entered on a monthly basis and I only want to update it occasionally. So, I have control charts set up with the basic formulae (percent, mean, upper and lower control limits) in there. I was wondering if there was any way to have all of this data in the range of the chart without having the chart bottom out (i.e. plotting zeros). For example, my columns _might_ look something like this: Date.........Total...Defects...%ofDefects...Mean....UCL....

How do I stop "Do you want to add this person to messenger" popups
Several times a week I'm getting popups, asking if I want to "add this person to messenger". This is getting very, very annoying. How do I turn this "feature" off? I know when someone I actually care about is going to add me to messenger, I can turn it on when that time comes. Several times a week? I get it many times a day, and it's driving me crazy! What you are supposed to do is go to Tools -> Options -> Privacy. Here, under Contact Lists you will find a check box saying "Alert me when other people add me to their contact list". Y...

add to Runas command
I tried adding some more functionality to the batch file below with no success. Is there a way that I can add to this batch file? For example, color 1f and loading some macros would be great too. Thanks. runas.exe /user:Administrator cmd.exe Below is an example batch file (incorporating the command you quoted ) I have working on my system to add some helpful stuff to a new "cmd" window.... *NB Make sure the batch-file is named ADMCMD.BAT and is in the system's "path" (i.e.; in a folder included in the PATH variable, usually C:\WINDOWS is one) and...

Inserting zero
In excel suppose I have 6Y,2M,3D &6Y,10M,10D &10Y,2M,9D in three different cell.If I will perform sort on these data in ascending /descending order it doesn't come properly ,but if I will insert one zero to the left of each single digit figure it is coming properly i.e. 6Y,2M,3D becomes 06Y,02M,03D and 6Y,10M,10D becomes 06Y,10M,10D . Hence I want to know how can I insert zero to each single digit figure by using functions or any other way so that my large data can be sorted automatically.Because If I will inser zero to each figure including double digit/three digit then a...

Excel and ADD INS
Using Automation, I'm trying to startup an Add-In to Excel called Crystal Ball. Does anyone know if there is an API or how I can go about doing this within Excel programically ? ...

I am trying to get access to display leading zeros and auto numbe
we use a sequential number system that begins new each month but we need it to hold 4 places 0001, 0002. etc. I would like to know if Access will do this. "Donna" <Donna@discussions.microsoft.com> wrote in message news:484176CC-EFDE-48BB-BF18-C5BEFD695710@microsoft.com... > we use a sequential number system that begins new each month but we need > it > to hold 4 places 0001, 0002. etc. I would like to know if Access will do > this. Yes, but ... You can't use the built-in autonumber field to do what you want. Therefore you'll need so...

How can I set the padding to zero if the document is pdf?
<li><a href="Doc1.pdf" target="DocFrame">Doc1</a></li> <li><a href="Doc2.htm" target="DocFrame">Doc2</a></li> I have statements like the above. DocFrame is an iFrame element that is inside a Div with nonzero padding set. For an html document that looks like a letter this put white space around the text that looks like the margin you normally would see in a letter. But for a pdf document it does not look good. How can I set the padding to zero if the document is pdf? I have no idea how...

zeros in the report
Reports-Sales-SalesAnalysis-AccountDue This report is showing item where the invoices are paid and the balance due is zero, making it a very long report. I have tried remove paid transactions and and reconcilling but neither works to handle this problem. Any ideas? Thanks Leslie May we assume that you selected to exclude zero balance accounts in the Sales Analysis Report Options window? "LASM" wrote: > Reports-Sales-SalesAnalysis-AccountDue > > This report is showing item where the invoices are paid and the balance due > is zero, making it a very long repor...

formula to add a number to a long string **
I'm not quite sure how to best describe this: I need a formula that will take the example below and return the "final result" Col. A = 24.43.234.555 and Col B= 23 making Final Result = 24.43.234.578 Is there a way to do this? This seems to work: =LEFT(A1,LEN(A1)-3)&(RIGHT(TEXT(A1,"0"),3))+23 HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "MMangen" wrote: > I'm not quite sure how to best describe this: > > I need a formula that will take the e...

How do i add " to the beginning of all cells
I have a login script that has lost its quote marks. example: DisableChangePassword=dword:00000001 It should read: ::\"::DisableChangePassword::\"::=dword:00000001 I have already added the quotes to the far side: DisableChangePassword::\"::=dword:00000001 But i am having trouble adding the first quote mark. Oh by the way there are over 500 entries to be made, so one at a time is not what i want to do. Also not all rows need the quote marks. Thank you for your help!! -- yvanblo ------------------------------------------------------------------------ yvanblo's Profile...

HELP: Add textbox to a frame at run time
Hi All, How can I add text boxes on the form at run time based on what user inputs in a textbox? For eg: I have a textbox "Total Students" , so Once I input a number in that textbox, I want to have that many textboxes on the form during run time. If I input 4 I want four textboxes to be displayed on the form. Thanks in advance ...

i need to add more options to a formula but i don't know how
hi, on one of my worksheets i use this formula =SUMIF(G126:G242,"UNPAID",F126:F242) this tells excel to add up all our unpaid invoices and tell me how muc we are owed. I now want the formula to add up all the boxes with unpaid next t them, plus all boxes with these phrases next to them: received-not banked yet banked-not cleared yet so i have tried things like thi =SUMIF(G126:G242,"UNPAID(or)received-not banked yet(or)banked-no cleared yet",F126:F242) but it does not work. as you will have guessed i am not too great wit excel. :confused: incidentally, unpaid, re...

linear charting with zeros
Have a line chart covering many cells. Data is added to each cell daily. The data cells use a formula. Problem is, the formula evaluates to zero without a value in the formula's source cell. Thus, the line chart, for future values, goes to zero. Is there a way to have the line on the chart simply "end" without showing zero out to the end of the data range? Thx. -- Bill Hi, You need to adjust your formula so it evaluates to #N/A rather than zero. =IF( <test> , <value> , NA() ) Line charts will not display markers for #N/A and the line is interpolated ...

add contacts to email addressbook, when option greyed out?
if i goto contacts/properties/outlook address book , the option to "show this folder as an address book is greyed out. i cannot use the conternts folder or any othe folder i have created as a adress boook it always remains greyed out. any ideas please? Create a new mail profile - you have given precious little information on your setup but I would bet dollars to donuts that you attempted to transfer your data using Windows Easy Transfer. That never works for OUtlook data without creating a corrupt profile. -- Milly Staples [MVP - Outlook] Post all replies to the group to keep ...

Any way to add tables and have the sync out?
Is there any way to add a table to the CRM db and have it sync out to the MSDE databases properly? Am I wishing? If I used a separate database on the MSDE, how difficult is remoting? You can add a table to the CRM db but it will not sync to the remote one. guess your looking at writing a custom solution "Jake Horn" <jhorn@sequelnow.com> wrote in message news:eQDqYM%23pDHA.2216@TK2MSFTNGP12.phx.gbl... > Is there any way to add a table to the CRM db and have it sync out to the > MSDE databases properly? Am I wishing? > > If I used a separate database on the MS...

Is it possible to add Credit Memos to Check Distribution Report?
In GP Check Distribution Report (Reports - Purchasing - Check Information - Check Distribution) is it possible to add Credit Memos that are applied to Invoices on a check? Currently this report by default only shows the Invoices the Check is paying off, but if possible we'd like to show any credit memos associated with invoices on this report. Thanks very much!!! Definitely NOT without a Customization. -- Regards, Vaidy "GPLoveHateRelationship" wrote: > In GP Check Distribution Report (Reports - Purchasing - Check Information - > Check Distribution) is it poss...

Add an item in outlook
How can i add a folder under inbox remotely for all my users.I want to create this folder for all my users with his inbox charcterstics(is special for each user). Then i want to send a message with special content to go inside this folder. Thanks ...