Set character specificiations for field in a table

Hello,

I am working in a table in MS Access 2003 and one of my fields is store 
number. Stores are either  a 3- or a 4-digit number (ex. 111, 1234). I have 
the data type set as text, but I can change it to number if it needs to be. 
Is there a way that I can set the field properties for the store number field 
so that the store number will always appear as a 4-digit number and if the 
store has only 3-digits it has a leading zero? Basically, if it is 4-digits 
already it will appear as is (ex. 1234), but if it is only 3-digits it will 
look like this: 0111. 

Any help you can provide is appreciated. Thanks!

Becky
0
Utf
1/27/2010 7:19:01 PM
access 16762 articles. 3 followers. Follow

2 Replies
1013 Views

Similar Articles

[PageSpeed] 5

well if you have the data type as text, it will store leading zeros
and not remove them.  if you keep it as text, you would want to
actually enter the data as: "0111" and not "111".


if the data type is a number, you would just need to format the field
on reports and such so it appears to have that leading zero,

i.e.

Format([StoreNumber], "0000")



0
ghetto_banjo
1/27/2010 7:33:47 PM
Leave it as a number field.  Put something like below in a query or where 
needed in a form or report:

String(4-Len([FieldName]),"0") & [FieldName]

If the field is null, only one 0 will show.
-- 
Jerry Whittle, Microsoft Access MVP 
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"Becky N" wrote:

> Hello,
> 
> I am working in a table in MS Access 2003 and one of my fields is store 
> number. Stores are either  a 3- or a 4-digit number (ex. 111, 1234). I have 
> the data type set as text, but I can change it to number if it needs to be. 
> Is there a way that I can set the field properties for the store number field 
> so that the store number will always appear as a 4-digit number and if the 
> store has only 3-digits it has a leading zero? Basically, if it is 4-digits 
> already it will appear as is (ex. 1234), but if it is only 3-digits it will 
> look like this: 0111. 
> 
> Any help you can provide is appreciated. Thanks!
> 
> Becky
0
Utf
1/27/2010 8:17:01 PM
Reply:

Similar Artilces:

Pivot Table Question #2
Hi I use Excel 2000 I have a worksheet that has duplicate (or more) entries in some columns. When I generate the pivot table these items that are duplicated only display once on the table and there is a blank line/s under them. Example GREY 555 AAA 555 ZZZ BLUE 666 BBB 666 CCC I would like that if there is a duplicataion of an entry that that duplication also shows on the pivot table. In the case of the example above that the work GREY & BLUE shows up in every line of the pivot table. How do I format the pivot table not to hide the duplicates. Th...

can I set default screen saver on new users?
WS08-R2 Hi All, Can I change the default screen saver that new users get when I create a new user account? Many thanks, -T Hello ToddAndMargo, You can configure GPO settings for the screensaver under this path: User configuration, administrative templates, control panel, display Best regards Meinolf Weber Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights. ** Please do NOT email, only reply to Newsgroups ** HELP us help YOU!!! http://www.blakjak.demon.co.uk/mul_crss.htm > WS08-R2 > > Hi All, > ...

"Play Speed Settings"
Windows 7 Home, Windows Media Player 11, Playlist: I have created a picture/photo playlist in the Windows Media Player, but I cannot adjust the "Play Speed Settings" from the Enhancement area. The slider will move with the curser/mouse, but will not remain at a level other than "1.0"..., with or without the "Snap Slider to Common speeds" selected or de-selected. Also, the "Slow, Normal, or Fast" selections have no effect. Any idea how to force the slider scale to be adjusted? I wish to slow down the playback of the photos. Cal ...

Analytical Accounting Ordinals (AAG00102 table)
Hi All, I know that I have previously suggested that the AAG00102 should be kept at Company database level rather than at the Dynamics database level. I have found that the ordinals contained in AAG00102, for reasons unknown to me, get out of synch with the actual company table values from time to time. I have some code, supplied to me by MS, that rather brutally loops through and updates AAG00102 for the 10000, 20000 and 30000 key values to the correct ordinals for each company. I say 'brutally' because the code does not even check whether these key values exist or not, and f...

Functions greyed out when using Pivot tables
I have an end user who is using pivot tables, and they were working fine up until the end of Jan. Whe she added her data for Feb and went to sort using the privot table order function they were all greyed out. She was originally using Excel 2k. Think that something had just become corrupt we upgraded her to Office 2K3. This did not solve the issue. I have insured the Office has been activated and that the sheet is not protected. We can send the sheet to another user and it functions correctly, as should. Anyone see this before or have any ideas? ...

Remove Box or Squre Characte from Cells
After doing an export from Outlook/Contacts to Excel I notice a square or box like character in all my cells. Is there an easy way to remove it, find and replace does not work. Thanks Chip Pearson has a very nice addin that will help determine what that character(s) is: http://www.cpearson.com/excel/CellView.htm If you do see a box, then you can either fix it via a helper cell or a macro: =substitute(a1,char(9),"") or =substitute(a1,char(9)," ") or as a macro (after using Chip's CellView addin): Option Explicit Sub cleanEmUp() Dim myBadChars As Variant ...

Filter a Reports calculated field
I have a report that draws data from various tables, and makes some calculations with information from the tables. I need to run a report that will automatically filter the calculated field eg. The report will only show the records > 10% Is this possible using SQL and if so how? Andy Gibson wrote: >I have a report that draws data from various tables, and makes some >calculations with information from the tables. > >I need to run a report that will automatically filter the calculated field >eg. The report will only show the records > 10% > >Is this possible ...

tables
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Power PC How do I create a simple data table in excel - with two input cell/variables? Thks Written for Excel 2003 but is basically the same: <http://office.microsoft.com/en-us/excel/HP051995861033.aspx?pid=CH010004561 033> HTH |:>) Bob Jones [MVP] Office:Mac On 5/15/10 9:00 AM, in article 59bb8809.-1@webcrossing.JaKIaxP2ac0, "MPJ@officeformac.com" <MPJ@officeformac.com> wrote: > Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Power PC > How do I c...

Conversion of Cross-Tab Formatted data to qualify for Pivot Table
It's amazing how one can take pivot table data -- double-click in the data section and it "converts" the PT format to a data-table format (fit for a database environment, say access)... Well any way, I receive files that are in a sort of a crosstab format, which I'd love to be able to get to respond to (the creation) of a Pivot table. Is there a way to make this "conversion"? Thanks in advance for any direction offered.. Do you mean something like this: http://j-walk.com/ss/excel/usertips/tip068.htm from John Walkenbach's site. Jim May wrote: > > It&#...

Excel 2000 Pivot table using offline OLAP CUB as the data source
Hello professtionals, I need to use Pivot table using offline OLAP CUB as the data source to analys a large amount of data. I would like to know: What is the maximum size for a CUB file in Excel 2000? What is the maximum size of data that Excel 2000 pivot table support? Thank you very much. Not sure what the maximum size is for an external data source, or even if it's restricted. Maximum size for a pivot based on a worksheet is 65,536 records (maxm. worksheet length). I regularly use pivots reading from Access databases of 2-300,000 records without a problem. "Samson Tang&quo...

exporting table
I have a table with just one column with 3000 rows. I need to export into a CSV file so that it looks like this: data1,data2,data3,data4, etc. Is this possible? I've attmpeted using the Export Wizard but can't get it. Thanks! brian wrote: >I have a table with just one column with 3000 rows. I need to export into a >CSV file so that it looks like this: > >data1,data2,data3,data4, etc. Exporting rows means you will get lines in the text file, so you need to export a single row with all the values. This can be done using a function like http://www.rogersaccesslib...

junction table query
I need a query that will populate ‘tbl_rep_region_junction’ (example below) with IDs from ‘tbl_rep_location’ and ‘tbl_city’ based on the entries in the 4 code_x columns in ‘tbl_rep_location’. Skip all blank code_x without region names. Thanks for the help.. tbl_rep_location rep_loc_id repname code_1 code_2 code_3 code_4 1 smith west east midwest south 2 james west south 3 phillips east midwest tbl_city city_id name region_id 1 Los angeles 2 2 New York 1 3 chicago 3 4 dallas 4 tbl_region region_id region_names 1 east ...

criteria field to auto filter
ok that seems to work but the problem now is that the screen is blank, if i select the filter the date in the field is correct with the right criteria but i have to select OK for it to work, how can i get it to select OK automatically so that will display all the transaction dates... thanks for this >>>> The < operator should be enclosed in quotation marks, and followed by an ampersand: Selection.AutoFilter field:=4, Criteria1:="<" & lessdays dhbyrne wrote: > Hi, > I am trying to write a macro that will use the autofilter function in Excel. I ha...

printing banners set up in Publisher
I would like to print a banner set up in Publisher in color book setting...only prints outline of font. Is this possible? I've done it before in Print Shop but can't find a setting in Publisher Use WordArt. -- Mary Sauer http://msauer.mvps.org/ "ble" <ble@discussions.microsoft.com> wrote in message news:D82DECA3-7BC2-4ED7-935D-CEE8C3F8AB47@microsoft.com... >I would like to print a banner set up in Publisher in color book > setting...only prints outline of font. Is this possible? I've done it before > in Print Shop but can't find a setting in Publ...

Times Tables
I am just starting to use Excell 2000. I want to make a times tables eg 1,2,3-10 at the top of column B - K, and 1,2,3-10 at the beginning of row 2 - 11. What would be the formula I type into B2 that I could then fill into the rest of my table and I would have correct multiplication in each cell. Please e-mail me an answer. It has been driving me crazy. I know it must be simple. Cheers On Mon, 11 Aug 2003 03:18:04 -0700, "Simon" <sleal@xmbox.com> wrote: >I am just starting to use Excell 2000. I want to make a >times tables eg 1,2,3-10 at the top of column B - K, ...

Run make table query from Excel with Access closed
I have an make table query in Access that I want to be able to run from Excel. Ultimately I would like to be able to run the make table with Access closed and no messages appearing until the table is made...then a notice saying the make table is complete. Is there any way to run a make table access query from Excel without opening Access? Thanks. Kick this around. Should help Sub makeTable() 'Needs reference the Axtive X Library 2.0 or higher Dim cn As ADODB.Connection Dim sCnn As String Dim sTableName As String Dim sMakeTableName As String sTableName = "...

Does INSTR() work on memo fields
Does INSTR() work on memo fields ? It seems like I'm not finding text that is there. On Wed, 25 Apr 2007 14:52:02 -0700, mscertified wrote: > Does INSTR() work on memo fields ? > > It seems like I'm not finding text that is there. Yes it does. Why not post exactly what you are trying to do and how you are doing it? -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail ...

Adjusting field data with update query
I run an import where the date is formated as follows: Wed Aug 22 11:47:43 -0500 2007 In excel I created the following macro to adjust the format to mm/dd/yy hh:mm:ss For Each Cell In Range("e2:h" & Range("e65535").End(xlUp).Row) If Cell.Value <> "" Then Y = Right(Cell, 4) M = Month(Mid(Cell, 5, 6)) D = Mid(Cell, 9, 2) T = TimeValue(Mid(Cell, 12, 8)) Cell = DateSerial(Y, M, D) + T End If Next Can I create something similar with an update query? I tried placing the follo...

Changing Address Card fields displayed Outlook 2007
I want to change the view of my contacts to only display the fields which contain data, display all of those fields, and without empty space where empty fields have been hidden. Is there a way to do this in Outlook 2007 Business Card View, without manually filtering each field separately? -- Str8Mitch;110184 Wrote: > I want to change the view of my contacts to only display the fields > which > contain data, display all of those fields, and without empty space > where > empty fields have been hidden. Is there a way to do this in Outlook > 2007 > Busine...

setting up word for sending emails
New Microsoft Office-Microsoft Internet Explorer has been installed but I don't know how to set up forward emails to send SEt up Outlook using your ISP instructions for incoming/outgoing server. Test Account Settings in the Account setup will tell you if you are successful. If not, then post the error message here and someone can help. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After furious head scratching, skm...

Table Expert
When a case is resolved, there is a dropdown to select the amount of "Billable Time" for the case. I have searched tables but cannot find where this data is stored. Is there anyone that know the schema where this data is kept? Bryon, When you resolve a case, the resolution screen fields are added into the activity table as a resolution type of activity. The Billable Time value appears in the TimeSpent field. (While building my auditing application, VAST, I've had to become much more of a CRM schema expert that I ever wanted to be - by the way, you can see these reso...

zeros in pivot tables
Hi everybody, I am working on an exell pivot table and I would like the pivot table not to show the zeros on columns when the total for the line is actually zero. I would like to supress this rows. Do you have any input? Thanks to all Have a great day Tomas, You can set "Zero values" off in Tools - Options - View. It affects the whole sheet. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Tomasmexicali@hotmail.com" <Tomasmexicali@hotmail.com@discussions.microsoft.com> wrote in message news:7BA8CE12-...

updating the data range for a pivot table
Hello, I am using pivot tables in a sheet to summarize data, and I added few rows to the original table that the pivot tables are drawing from. How can I update the data range that the tables pull from withou having to redo all the pivot tables?? Thanks a lot! Dav -- Message posted from http://www.ExcelForum.com > I am using pivot tables in a sheet to summarize data, and I added a > few rows to the original table that the pivot tables are drawing from. > How can I update the data range that the tables pull from without > having to redo all the pivot tables?? Use a dynamic n...

Strip Flat File Table into less entries
I have a table I found that is essentially a 'hierarchical' table but laid out in such a way I can't really use it w/o further processing. Essentially it is currently something like this Type | Type Code | Subtype | SubType Code | SubSubType | SubSubType Code I really just want to end up with Type | Type Code | Parent Code So I might have Fruit | 100000 | Citrus | 100100 | Orange | 1001001 Fruit | 100000 | Citrus | 100100 | Lemon | 1001002 Fruit | 100000 | Melon | 100200 | Cantelope | 100201 Fruit | 100000 | Melon | 100200 | Honeydew | 100202 Meat ...

Link field
Hi all I am trying to creat a form where if i select the site name from the combo box the address box is populated automaticaly from the master table -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200708/1 Forgot to mention i am using access 2003 Rkapoor wrote: >Hi all I am trying to creat a form where if i select the site name from the >combo box the address box is populated automaticaly from the master table -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200708/1 Have the Row ...