Acc97: Masking uniqueID

[noodnutt]

G'day all,

I would like to setup the unique field to generate booking numbers as such:
2004/1 - 2004/399 etc... then, if possible, when the new year rolls along,
have the unique continue on as 2005/1.

In the unique fields format, can I do this: =Date("yyyy" "/"), additionally,
will this reset the autocounter to ( 1 ) when the calendar rolls over?

very much looking forward to your thoughts

TIA

Mark.

 

[Tim Ferguson]

I would like to setup the unique field to generate booking numbers as
such: 2004/1 - 2004/399 etc... then, if possible, when the new year
rolls along, have the unique continue on as 2005/1.

This is not difficult: but you need two fields, one for the YearNumber and
one for the SerialNumber. You also need a way of allocating the
SerialNumber -- access/ jet cannot do this automatically so you will have
to program it into your form(s) and make sure that nobody can insert new
records without going through your data entry forms.

Try googling for Access Custom Autonumbers, or else look at Dev's Access

In the unique fields format, can I do this: =Date("yyyy" "/"),
additionally, will this reset the autocounter to ( 1 ) when the
calendar rolls over?

No: you need a single Primary Key consisting both the YearNumber and
SerialNumber fields. You can display your number by concatenating them on a
form or a report like this:

= Format(YearNumber,"0000") & "/" & Format(SerialNumber,"000")

Hope that helps


Tim F

 

[John Vinson]

G'day all,

I would like to setup the unique field to generate booking numbers as such:
2004/1 - 2004/399 etc... then, if possible, when the new year rolls along,
have the unique continue on as 2005/1.

In the unique fields format, can I do this: =Date("yyyy" "/"), additionally,
will this reset the autocounter to ( 1 ) when the calendar rolls over?

This is called an "Intelligent Key" in the trade - and that's not a
compliment. Storing two pieces of data in one field is *emphatically*
a Bad Idea.

I would suggest using two fields, BookingYear and BookingSeq. You can
ctrl-click both fields in table design view and make them a joint
Primary Key. The Default property of BookingYear could be

Year(Date())

to set it to the current year. To get BookingSeq you must - no option,
tables don't have any usable events - use a Form to do your data
entry. You can put VBA code in the Form's BeforeInsert event such as:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!BookingSeq = NZ(DMax("[BookingSeq]", "[your-table]", _
"[BookingYear] = " & Year(Date()))) + 1
End Sub


John W. Vinson[MVP]

 

[noodnutt]

Tim & John,

Thank you both for your idea's, though neither will work, here's how:

John:
Year(Date()), <this returns 26/06/1905>
Private Sub Form_BeforeInsert(Cancel as Integer)
Me!BookingSeq = NZ(DMax("[BookingNo]", "[tblBookings]", "[BookingYear] = " &
Year(Date()))) + 1
End Sub, <this does nothing(that I can see!)>

Tim:
= Format(YearNumber,"0000") & "/" & Format(SerialNumber,"000")
The only return I get from this is <#Name?>,

This must be slightly more difficult than first thought, I will scrap idea.

Thx heaps for your idea's guys.

Regards

Mark.

 

[noodnutt]

Tim,

Ran your suggestion as a query like this:

BookingSeq: Format([BookingYear],"yyyy") & "/" & Format([BookingNo],"0000")

aestetically, It works, thats the main issue.

Cheers

Mark.

 

[John Vinson]

Tim & John,

Thank you both for your idea's, though neither will work, here's how:

John:
Year(Date()), <this returns 26/06/1905>

Only if you format it as a Date/Time, or store it in a DateTime field.
The Year() function returns an integer; and 2005 days after 12/30/1899
(the base of the date/time numbering system) is in fact June 26, 1905.

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!BookingSeq = NZ(DMax("[BookingNo]", "[tblBookings]", "[BookingYear] = " &
Year(Date()))) + 1
End Sub, <this does nothing(that I can see!)>

It sets the value of the Control named BookingSeq on your Form to that
value, if your form and your table are set up correctly. Sorry, I was
giving an example, not a detailed cookbook approach; if you'ld like
the latter, please post back.

Tim:
= Format(YearNumber,"0000") & "/" & Format(SerialNumber,"000")
The only return I get from this is <#Name?>,

This must be slightly more difficult than first thought, I will scrap idea.

Only very slightly. I think if you use an Integer field rather than a
Date field for the BookingYear you'll be fine.

John W. Vinson[MVP]

 

[Tim Ferguson]

aestetically, It works, thats the main issue.

Wrong. Informatically, it works, and _that_ is the main issue. You can
always make anything look aesthetically pretty.

All the best


Tim F