VBA Format Append Leading Zeros

I have a subform with a text field that I am trying to append leading zeros.

Table = [tblSampleLogIn]
Table = [tblMycoData]
Form = [frmSampleLogIn01]
Form = [subMycoData] ; Field = [SampleID] Text

Here is my code so far:

Code: Private Sub SampleID_AfterUpdate() Dim srtSampleID As String Set srtSampleID = Forms![frmSampleLogIn01]![subMycoData].Form![SampleID].Value Forms!frmSampleLogIn01!subMycoData!SampleID.Value = Format(Forms!frmSampleLogIn01!subMycoData!SampleID.Value, "000000") End Sub The text values will be less than 6 characters in length. I can get the immediate window to return the value in the field by entering the following into the immediate window:
Code: Forms![frmSampleLogIn01]![subMycoData].Form![SampleID].Value But then, nothing else happens when the code runs.

Ultimately, I need to have leading zeros in front of the values to sort the text values. The reason is because sometimes a numerical character is unknown, and will be substituted with a "?". Having leading zeros will help to properly sort the text values.


Post your answer or comment

comments powered by Disqus
I am pulling data out of a new system and need to import it into a really old legacy system. The format this old system needs looks like this (notice the leading space in the first row):
12,116050,4523, 000000253177
12,160310,0680,-000000030000

The last field is a number field that comes in as 2531.77 and -300. I import data into a table and then am using a query to format an export. Getting the leading zeros is not a problem but I need to get a space in front of any number that does not have a "-". Any ideas on how I can get a space in front of the leading zeros but no space if there is a "-"?
Thanks.

Hello All, I have seen some posts regarding this topic, and so far it's not working for me. Hoping someone can help me. Here's the roadblock(s) I'm running into...

Ultimate Goal:
I need to take an 11 digit numeric code, calculate a MOD10 check digit on it, and store it in the database. (Parts of this code need to increment as well which is why I have this as numeric instead of text.)

I have broken the number down into it's meaningful parts on a table which I am using to calculate, then using an append query it to add the finished code to a 'master' table for storage.

Here's the breakdown:
FinalField: 11 Digit value up to 99999999999
Field1: 2 digit field, valid values are 97 or 99
Field2: 5 digit field, valid values from 00000-99999
Field3: 3 digit field, valid values from 000-999
Field4: 1 digit field, this is the MOD10 check digit calculated using Fields 2 and 3 only.

So far, I have two issues I can't get around:

Problem #1 Field Size:
These are all Long Integer fields, including FinalField. When I attempt to concatenate the 4 fields that make up FinalField, I get an error since Long Integer's max size is a value of 2147483647...lower than the 99999999999 I need. Attempts to make this into another type of field result in it flipping scientific and/or zeroing out the last several digits.
Surely there has to be a way to store an 11 digit numeric in the database...what's the secret?

Problem #2 Leading Zeros:
I have seen the recommendations on formatting, for example:
Field1: 97
Field2: 00123
Field3: 001
Field4: 4
Concatenate to FinalField: 97001230014

=Format([Field1],"00") & Format([Field2],"00000") & Format([Field3],"000") & Format([Field4],"0")

When I try this, I'm getting a Compile Error.

Specifically, I'm doing this in Access 2007, Query Design screen, and I'm placing my cursor in the "Field" field (top row). When I type the above expression in there, it defaults to:

Expr1: Format([Field1],"00") & Format([Field2],"00000") & Format([Field3],"000") & Format([Field4],"0")

...and...I get that pesky Compile Error when I try to run it.

(Should also mention that this is an Append Query, it's supposed to append the concatenated value to a field (Long Integer) on a table. I have tried pulling out Field2 so that the concatenated value is only 6 digits (small enough to fit in the destination field), but I still get that Compile error.)

Any suggestions for either of these problems?
Thanks,
Cindy

I need to preserve leading zeros in a field when I export a table to txt.
The "hours" field needs to contain 5 digits for example 40 hours should look like 04000. I am using a query for the export, the dynaset for the query contains the right amount of zeros but when the export is performed I lose the leading zeros.
I searched the forum and found what I thought was the fix

Select Format(Date,"00000") as Date1

but I can not seem to get it to work.

any help would be greatly appreciated.




I know I have (sort of) posted this question twice, the other time I accidently used Quote and it ended up appending my question to the thread I was trying to quote from......Sorry

I have a number, say 10000, but I want it to be 6 digits with leading zeroes.
This did not produce an error, but it did not work either. Searching these forums and Access help did not help me.


	Code:
	dim l as long
l=10000
l=format(l,"000000")

l is still 10000 not 010000.

Thank you.

Ok, so i've tried to search the forum for this issue, becuase i know leading zeros cause problems, but i hadn't found any answers that work for my situation.

I have code that is updating a table with ID numbers. I tried using an auto field but the update sql wouldn't work correctly, so this is where i'm at.

Code:
Dim db As DAO.Database
Dim Prospect As DAO.Recordset
Dim sql As String
Dim q As QueryDef
Dim r As DAO.Recordset
Dim ID As String
Dim adjID As String

ID = Format(Nz(DMax("[ID]", "tbl"), 0), "0000") + 1
sql = "SELECT [ID] FROM [tbl] ORDER BY [Date]"
Set r = CurrentDb.OpenRecordset(sql)
r.MoveFirst
Set db = CurrentDb
Set q = db.CreateQueryDef("")
DoCmd.OpenForm "Standby ID", acNormal
Do While Not r.EOF
adjID = Right("000000000" & ID, 6)
sql = "UPDATE [tbl]"
sql = sql & " SET [tbl].[ID] = " & adjID & ";"
q.sql = sql
q.Execute
On Error Resume Next
Forms("Standby ID")!Status = ID
Forms("Standby ID").Repaint
ID = ID + 1
r.MoveNext
Loop
DoCmd.Close acForm, "Standby ID"

When i run the code i get the correct string, with leading zeros for the 'adjID' variable. But for some reason i can't get the leading zero's to stay in the table field. I have the ID field set as text and have even tried to format the field to "0000".

Help....Thanks.

I have a form with a field ProjectNumber_StudyNumber. This field gets it data from a query in which data are combined from 2 fields: "ProjectNumber" and "StudyNumber". Format of the combined field is 1.10/005, 1.10 os the projectNumber, 005 is the StudyNumber.

This all works good but when you create a new record it requires the user to look up the last used studynumber, add +1 and fill in the new studynumber. It works but I'd like to get that automated. I've almost achieved that now but am stuck at the format of the numbering. I'm using this code I found on this forum to generate the new studynumber and that works well:

Me!txtNewStudyNumber.DefaultValue = Nz(DMax("[StudyNumber]", "tblStudyPlans"), 2) + 1

The problem is that if I have a leading zero in the studynumber (and I have for all of the first 99 studies in each project), as in e.g. 015 or 001, these zeroes are not included in the next number and that throws my sorting on studynumber, 2 is seen as a higher number than 011. The 2 of course should be 002. How do I get the leading zeroes?
In the table the field is numerical with field size 3. Input mask is 000;;

What am I doing wrong?

Thanks for your help,

Walter

Hi Excel Gurus,

i have question, how to automated leading zero in my excel ? example ;-

1
12
123
1234
12345

i would like the result to be :-

0000000001
0000000012
0000000123
0000001234
0000012345

i don't want to use format cell /custom as the solution, because after i add leading zero i need to perform vlookup to other worksheet.

kindly help ?

Hi,

I am battling with the access sorting on a combo box. I know that on a text field access sorts on the first number rather than the whole number.

As a result on my products combo box the results are displayed as:

Solid American White Oak Flooring - Nature Grade - 100mm x 19mm
Solid American White Oak Flooring - Nature Grade - 115mm x 19mm
Solid American White Oak Flooring - Nature Grade - 130mm x 19mm
Solid American White Oak Flooring - Nature Grade - 75mm x 19mm


I want the products to be sorting according to the width of the boards which is the first number 100, 115, 130, 75 etc....


I have looked at various solutions which dont work. The only solution I can think of is to put leading zeros on the first number detected so that the fields sort correctly.


Obviously I need to ensure that all numbers end up the same length so some number will end up longer than others if I just use a stand 3 leading zeros.


I will also have to have this as another field in my table as I dont want those leading zeros actually appearing in the combo box.



This is the end result I am after:



Solid American White Oak Flooring - Nature Grade - 75mm x 19mm
Solid American White Oak Flooring - Nature Grade - 100mm x 19mm
Solid American White Oak Flooring - Nature Grade - 115mm x 19mm
Solid American White Oak Flooring - Nature Grade - 130mm x 19mm




I am not great with VBA code and I have no idea were to start with coding the solution!


Any help is much appreciated.

Ok this one is driving me crazy! I have had this issue before and it seemed to aleyas be just a matter of setting the correct data type (i.e., text) vs. trying to import the value as a number...

I am downloading data from an application. If I try to DL the data as an excel spreadsheet or csv for mat, the zeros get chopped off. So my only option is txt. When I DL the data in tab delimited .txt format, the download works.

However, when I push it into Access... Access drops the leading zeros, even if I manually set the datatype for the field as text.

How can I get Access to import the data without trying to think for me and assuming this value doesn't need leading zeros?!?! Is it possible I have some setting checked that is doing that for me and I don't know about it? I am currently at a new job and they have some backwards equipment here.

BTW: I am simplifying this example just to make it easier to digest... I actually have several fields and several imports/DLs where this is the case and some other currency fields I need to set by hand as well...

I have access97 database with table(mytable) and have a columns(amount, name,ta, da).

I want to perform calculations only on "AMOUNT" column.

Amount column type=text (i.e. string)
Size=12 (maximum length is 12characters/digits)

In this amount field user enters data like(1234, 50000, 3547446,9868686 or more entries). I want to update all entries on a single button click with leading zeros(0) so that total digits/characters become 12.
for example if user enters "5874689" then
clicking on the button, this entry should become "000005874689".

All entries in this column should become of 12 characters/digits in size with leading zeros.

and if there are already 12digits/characters are there in an entry then we will leave it and move to next entry.
This process will work from bof to eof.

I want to insert zeros(0) to make all entries of 12 characters/digits.

User can enter any number of characters/digits but we have to process all entries and make them 12 characters/digits by inserting zeros(0) into them.

for example :
if user enters 25636
then 000000025636
or
if user enters 123
then 000000000123
or if user enters 123456789123
then 123456789123 (becausethere is already 12 characters).
all entries in the column should be updated with the format explained above with one button click.

Hi

I've tried searching the forum but cannot find anything relevant.

My problem is I have a combo box in an access form which displays dates from a table. The table field and the combo box format is set to short date. When the value changes the data on the form body changes relevant to the selected date. This works fine except when the date has a leading zero (e.g. 06/03/2006) in which case no data is displayed. I have a variable of type date which takes the value of the combo box and then an sql statement which uses this variable. I have used breakpoints and foudn that the variable has a value of (e.g.) 06/03/2006 and this is how the date is stored in my table. I'm wondering if I'll need to do something with totext, turn the date into a string and make sure it has the leading zero?

Do you know if this is a known problem with access/access forms? If anyone can point me in the right direction as to why the code has a problem with leading zeros I would greatly appreciate it.

Thanks in advance.

My code is:

Private Sub cboDate_Change()
Dim datTemp As Date
datTemp = cboDate.Value
''cboDate.SetFocus
Form.SetFocus

Form.RecordSource = "SELECT [staff].[staff_id], [staff].[lastname], [staff].[forename], [staff].[bocs_name], [staff].[headset], [staff].[phone_uic], [staff].[bocs_uic], [staff].[group], [staff].[type], [staff].[email], shifts.* FROM staff LEFT JOIN shifts ON [staff].[staff_id]=[shifts].[staff_id] WHERE [staff].[type] 'Leaders' AND (((shifts.date) Is Null Or (shifts.date)= #" & datTemp & "#))"

End Sub

I hope someone can help with this one. After many years of using Access for ad-hoc data conversion this has beaten me.
I need to produce an ascii text file with fixed column widths, separated by commas, strange I know but the customer is always right. As it is fixed width I have inserted the commas by using a separate column for each one.
Numeric columns need to be left padded with zeros. I have constructed a query to do all the column selection and reformatting into a new table which I then export using a fixed length export file spec. Everything works fine except for 3 columns which are calculated by subtracting one column from another. I can get the data to look fine in the output table, the datatype is text, but when I export the table the leading zeros are stripped.
This is my expression: String(9-Len(FormatNumber([FULL_FARE_EQUIV]-[TAX_EQUIV],2,0,0,0)),"0") & FormatNumber([FULL_FARE_EQUIV]-[TAX_EQUIV],2,0,0,0).
The result in the table is exactly what I want: 000200.00 but when I export it I get a left adjusted 200.00.
I've tried using format with a "000000.00" mask which gives the same results.
I've tried removing the preceding comma column and including the comma as a prefix using the format mask ",000000.00" and also by concatenation. This looks fine in the table column ,000200.00 but I get an error when I export the table which blanks the column. Error attached.

I need to convert my text data to a number but when I convert using the VALUE function or use "format cells" to the numbers category, I loose the leading zeros. I need to keep them for sorting purposes.

What formula do I use?

I searched through the forum, and found alot of examples on DateDiff(), but I need to take two dates (both stored in a table as [Off Time] and [On Time] and subtract them. Then show them in hh:mm:ss format, keeping in mind this is in a query. The datediff() function gives me and error if I try to do this. So I tried using math to do
hours & ":" & minutes & ":" & seconds
but the minutes will not show a leading zero, same with seconds.

ie: 2:3:02 is not acceptable.

Is there any way around this?
Or am I going about this all wrong.

I have to work with two tables to run a query. The first table has a field titled QUAL_TRK_NO, but the way the data appears in the field is as follows: 00000000000000000000000000000002721897
(leading zeros)

The second table has a field titled KEY_INFO. This field contains the same data as the QUAL_TRK_NO, but the data appears in the following format: 2721897.

(It's the same number, but in a without the leading zeros)

Right now, if I join the tables and run a query, there is no data returned. I need to prepare a query using the two tables to retrieve other data from the tables, but this field is the one field that the two tables have in common.

The tables I use are on a network, so I don't maintain the data. How can I get both of the fields to match up and allow the query to run?

Please help! Thanks.

I'm learning as I go and this is probably a basic question but if I have an alpha numeric field of variable lenght, i.e. AUI856Z....how to I format it so that it is 19 characters long with leading zeros, i.e. 000000000000AUI856Z.

I am using the format below to give me Year-WkNo and the leading zeros are not being put in so when a report is ran the yyyy-ww is not sorted correctly. Is there a way to get Jan 2007 week 1 to come out 2007-01?


YearMonth: Format([F Jobs Stats_sub2].[MaxofWork_Date],"yyyy-ww")

Thanks,
Greg

I'm using an expression to show some text details and an autonumber with the "&".

The problem is i want some leading zeros in the autonumber to ensure overall format stays the same when the number grows in digits. I've tried changing the format for the autonumber but this doesn't change the actual value.

I want to get a result like "Record00001" or "Record00101"
But i'm getting "Record1" or Record101" at the moment.

In excel i'd use something like =text(autonumber field,"00000")

I have two fields where I want to have leading zeros entered to make up a fixed number of digits. In the format property of the textbox, I entered in 000000000-0000, which does indeed display the account number on the form with leading zeros (if necessary). How can I store the leading zeros in that field in the underlying field in the table?

Oh, and I wanted to ask whether there is any way to have a format on a text box without actually displaying that format on the form itself? I don't want the user to see all the zeros in the field when they start a new record.

Thanks in advance....



[This message has been edited by LQ (edited 05-10-2001).]

hi

if i have a long in a text box. how do i get it to give it a leading zero up to 8digits?

in Excel, it's a fairly simple format>custom>"00,###,##0"

but that doesn't work on a text box


Not sure I'm using the right terminology, but I hope this makes sense:

I have a text box that is bound to a combo box, such that the selection in the combo box (text) auto-populates the textbox (numbers). The textbox is used to display 5 digit numbers, some (but not all) with leading zeros.

I formatted the table that serves as the Control source to display the leading zeros - and it worked (the leading zeros show in the table). However, I can't get the leading zeros to display in the form.

I found this thread -

http://www.access-programmers.co.uk/...=leading+zeros

and have tried what's suggested, but no dice.

Thanks in advance for any help given.

Hi!!

I have 24 text boxes (OTDR Index1-24) on a form with default values of "01" to "24". Often however, the first of the boxes will need to be (say) "05" and I want the following boxes to auto fill incrementally after I have typed "05" in the first box. I have done this with a simple after update on the first box:
[OTDR Index2] = [OTDR Index1] + 1
[OTDR Index3] = [OTDR Index2] + 1

This works OK except I lose the leading zero in all but the first box and it is important that the numbers are "05", "06" etc not "5", "6".

I have tried number and data/text data types in the field. I have tried @@ as the format, nn as the format and I'm now lost!!

Help me if you can you lopvely people!

Many thanks

Hope you can help.

I have a Client table with an IDNUMBER in Number-type which is set to Double with a format of 00000000000. It has a name field.
The leading zero of IDNUMBER shows in the stored table.

In a second table, Work History, I have a multi-column combo-box combining the name field in 1 column and the IDNUMBER in the 2nd column. The bound column for saving is the 2nd (IDNUMBER). But when saved, the leading zeros are missing! - The format on the form and in the table are identical. Any ideas?

How do I force a leading zero to be shown in a text field?

What I have are three combo boxes where the user selects the date, which populates a field, but our format requires that there be a leading zero in front of any single digit month or day number. Access strips out the leading zero when populating the field.

Any assistance appreciated.


Not finding an answer? Try a Google search.