Formatting an Autonumbering scheme 1

[marie515]

I want to have a custom numbering scheme for a database. I want the following information:

RFP20041001 - (sample numbering scheme)

The break down above is:

RFP - all records begin with this
Year - 2004 (that record was entered)
Month - 10 (that record was entered)
AutoNumber - 01 (autonumber how many records in database)

I was able to make separate fields that make up the above, but I'm wondering if I can simply code the autonumber to show this by itself?

 

[hymn]

I would advise very strongly to do a search on autonumber
It is advisable NOT to manipulate

Hope this helps
Hymn

 

[Trendsetter]

What you describe cannot be an Autonumber as it is a string.
What you describe can be built with code as a reference but I would strongly advise that you use an Autonumber as the primary key.

Dim refnum as string
Dim rst as Recordset
Dim rstCount as Integer
Set rst = CurrentDb.OpenRecordset("SELECT * FROM TargetTable")
rst.MoveLast
rstCount = rst.RecordCount
refnum = "RFP" & Right(Date(),4) & Mid(Date(),3, 2) & Format(rstCount,"000")
rst.Close
set rst = Nothing


Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[marie515]

Thanks Trendsetter -- I'll try your recommendation.

 

[hymn]

Hi Frank
Have you been offline for awhile?
cant remember seeing you online for a long time anyway nice to see you again

Hope this helps
Hymn

 

[Trendsetter]

I got severly reprimanded by the egos that run this forum for something that they thought had happened but had not....
some sort of breach of unknown, unwritten rules.
Thanks for your kind wishes

Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[hymn]

tip leave your email address off otherwise you will get another email

Hope this helps
Hymn