How to keep leading add leading zeros (1 Viewer)

[Parrot215]

Good evening to you all.

I have a problem I need help on. There is a field I am trying to add a leading zeroes to a fix set of numbers. I already combines the fields through a conversion query as such Field3: Table1.[Field1] & " " & [field2].

Field 1 = 112 Field 2 = 56843312
16 9745231.A
235 3462367

I want these two combined fields (Field 1 and Field 2) to look like in Field 3:
112 56843312 112 56843312
016 09745231.A and not 16 9745231.A
235 03462367 235 3462367


All data should have a single space between and should align towards the left. Any help is appreciate it. The data is from an Excel file that contains "Field1" and "Field2" that do not contain zeros when downloaded from a certain online program. Then I import these numbers from an access database file to format before downloading that data into another a main access database file. I know there is a way to add leading zeroes through excel itself before I export them from the first access file. I want to know how I can add zeros to the data while it's in the main database file.

There is a conversion query in the main access file that converts headers from a the downloaded data from the online website to the new headers in the main access database file.

Can there be a formula I need to place during the conversion query or do I need to place the formula somewhere else? Once the data passes thru the conversion, it goes to a set of duplicate queries before settling down to a main table.

Help is much appreciate it.

 

[pbaldy]

Only a text data type can hold the leading zeros. You can use the Format() function to add them during import or display them later:

Format(YourValue, "00000")

Will turn 123 into 00123.

 

[Parrot215]

Thanks for your input. I have tried that already. Nothing. I have used that expression already. The access database file was written by someone else. Once the company moved to a new online program, nothing works. I had to change the conversion headings with the new ones, and it picked up the data fine, except for the leading zeros.

There is one extra problem, but need to tackle this problem first.

"Field1" is fixed to 3 digits. "Field2" is fixed to 8 digits. Sometimes you see a alphabet innthe end such as 78647554.A

Don't know if that would be a fixed field? Thanks for your quick response.

 

[pbaldy]

Tried it where exactly, or how? It certainly works when used appropriately. It wouldn't work importing into a numeric data type; as I mentioned, a numeric data type will not hold leading zeros.

 

[Parrot215]

I think the query conversion part of access is where I used it. The data comes in from Excel as general or numeric. I see the problem there. The data then rests in s table which is shiot text data type. I tried the expression there in table design but the two fields are combined already. The first three digits followed by a space and the last 8 digits, and sometimes with a letter after a period.

 

[Parrot215]

Maybe I can leading zeros in the main table where the data sits? What could be a good formula for a field that contains "000 0000000.A"?

 

[Pat Hartman]

Field1 needs to be a numeric data type for the Format(field1, "000") to work. So as long as field1 only ever contains numeric characters then

Format(clng(field1), "000") will convert the field to long integer and then add the leading zeros to the front AND convert it back to a text string.

You can do the conversion when you import the data as long as you save field1 as text because ONLY text fields will ever have leading zeros. This is a standard convention agreed to by everyone who writes numeric values. Since a leading zero has no value, no number is normally written with leading zeros. Occassionally, you will see numbers written as fixed length such as $000456.45 on a check to avoid tampering but that's a rare case. There are however many numeric "codes". These are not numbers in the sense that you don't do arithmetic on them. They could just as easily be a mixture of letters and numbers. Since they are almost always fixed length because they are codes, they will have leading zeros. Two common instances in the US are SSN and Zip.

 

[Parrot215]

I will try your suggestion and let you know. Thanks.

 

[arnelgp]

create a function in a Module and call it from your query:

Public Function fnLeadZero(pField As Variant)
    Dim vSplit As Variant
    pField = pField & ""
    If pField <> "" Then
        vSplit = Split(pField, ".")
        If UBound(vSplit) = 0 Then
            vSplit = Split(pField, " ")
            If UBound(vSplit) = 0 Then
                fnLeadZero = Format(pField, "00000000")
            Else
                vSplit(0) = Format(vSplit(0), "00000000")
                fnLeadZero = Join(vSplit, " ")
            End If
        Else
            vSplit(0) = Format(vSplit(0), "00000000")
            fnLeadZero = Join(vSplit, ".")
        End If
    End If
End Function

your query will look like something like this:

SELECT field1, field2, Format([field1],"000") & " " & fnLeadZero([field2]) AS field3
FROM yourTable;

 

[The_Doc_Man]

Just remember this: If your data element is numeric, it is treated as an atomic entity and the concept of how it is presented is not a question. In forms and queries, however, you can impose presentation formats using the FORMAT function as needed. Your other suggestions coming from my colleagues are examples of this principle.

Do NOT expect datasheet views of a table to contain leading zeros for a field. But you CAN make datasheet views of queries do this.