Question on function and how to format it?

[d9pierce1]

Hi all,
A week or so ago one of the members here gave me some code to create a Unique # for my transactions in my db. (Code Below)

I have two questions, One is I would like to format this to 10 digits such as 0000000001, Basically I want to have all be a 10 digit number and as the Unique # grows the 0's decreese
Example... 0000000001, 0000000002, 0000000110, and so on... Need some assistance on how to make that happen?

The second question is do I store the Unique # that is called for in my transaction table as a Num or ShortTxt?
And if a Num would that be an interger, long integer, single....?

Public Function fnHeaderID() As Long
    
    Dim sSql As String
    Dim db As Database
    
    sSql = "INSERT INTO tblUniqueIDs (UniqueDate) Values (Now())"
    Set db = CurrentDb
    
    db.Execute sSql, dbSeeChanges
    fnHeaderID = db.OpenRecordset("SELECT @@IDENTITY")(0)
    Set db = Nothing




End Function

Thanks
Dave

[Minty]

I recognise that...

You can simply format the number for display on the form.
Leave it as a Long Integer as joining on it for PK / FK purposes will be much more efficient.

In the format in the format properties of the display control use "0000000000" or create a formatted version of it in the underlying query with a calculated field;

MyDisplayRef : Format(YourFieldName,"0000000000")

This means you still have access to the underlying number.

[d9pierce1]

Thank you Minty
Helps A Lot
Dave