Changing View to view leading zeros in a query

[gustavvs]

I have a problem downloading a specific field with leading zeros to an Access table.

Is there a way to add adjust the view in the query to show the leading zeros? I'm banging my head against the wall trying figure this out.

The source has the field set to Numeric and it can not be changed. The source retains the leading zeros.

However when viewed or downloaded in Access, it removes the leading zeros. This is the case even when I changed to field in Access to Text.

Barring any easy way of fixing the download, is there code out there to add the zeros via string experience. I suppose I can whip up something where I count the number of characters and then add the appropriate number of zeros to beginning of the field.

(Of course, to make it even more fun, it is suppose to be 8 characters and the actual length can vary from 5 to 8 depending on the number of zeros suppose to be there.)

Thanks!

 

[lespaul]

The only way to retain leading zeros in a number is to set the field to text. If the formatting is always a certain number of characters (like a SSN is always 9) you can format the number to show leading zeros, but they are NOT stored.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[AlexCuse]

Here's a quick and dirty version of a function I've used for this before:

Public Function PadZeros(InputCol As Integer, TotalLen As Integer)

Dim OutputCol As String

OutputCol = InputCol

While Len(OutputCol) < TotalLen
    OutputCol = "A" + OutputCol
Wend

PadZeros = Replace(OutputCol, "A", 0)

End Function

I couldn't get it to add a leading zero (does integer math when I try), so I added "A", then replaced with zero.

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[AlexCuse]

Hey Leslie,

Happy New Year! Hope that the guitar biz is still (not) treating you well ;-)

Alex

Ignorance of certain subjects is a great part of wisdom

 

[PHV]

Provided your field is defined as text in the table, here a simple Update query (SQL code):
UPDATE yourTable
SET yourField = Format(Val([yourField]),'00000000')
WHERE Len([yourField])<8 AND IsNumeric([yourField])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886