insert sequential numbers with leading zeros into a table using query

[aisgeek]

I am using Access ’97 and am trying to insert sequential numbers with leading zeros into a table using a make table query and am stumped. Is this even possible using a query?

 

[jebry]

Hi!

Use a public function:

Public Function basSeqNums() As String

basSeqNums = Format(Forms!frmParameters!txtSeqNum, "0000&quot
Forms!frmParameters!txtSeqNum = Forms!frmParameters!txtSeqNum + 1

End Function

Make a form called frmParameters and add a text box called txtSeqNum setting the initial value to 1. You could use a Static variable in the function or a global variable as well. I usually prefer the form because never trust these variable to initialize correctly. You can call this function from your query as a new field. Also, I set up the format for leading zeros up to four places. If you want more, just add more zeros.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[Darrylles]

Hi ais,

You got to remember that the field that you store this in must be text. I don't think you can specify 'allow leading zeros' for a numeric field.

Regards,

Darrylle

p.s. Someone will prove me wrong of course! "Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[aisgeek]

Thanks for the replies. I will attempt using the function first as that does seem logical. This numbering used to identify the number of records to the customer.
Ciao

 

[tlbroadbent]

Why not just insert an integer value and format the value with leading zeros when displaying the value? One of the most frequent errors I see in database design is the mis-typing of data columns because developers want to store data in a specific display format. This causes problems with sorting and computations, and usually requires more storage space. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.