Numbering (1 Viewer)

[racdata]

Hi All

Is it possible to do the following?

I have a column with a heading EventNo.
The Event number must be 06-00000 The first 2 digits is the year.

In the Input Mast I have - 00/-00000 and the Default value set as 06-00000

How can I set it up so it can go to 07-00000,08-00000 and so on for next years? The Event number must start at 00001 every year.

It will be impossible to change the default value every year.
Thanx

 

[statsman]

The search function in this forum really does work.

Sorry, couldn't resist

 

[racdata]

Auto Number

Hi
Yes it is easy to say search, I did search before but coudnt find any posts that suits my request. I do not know if I can make use of the examples because I do not know how to edit the codes to make it work.

Attached is an example of my database. Can anybody edit it and sent it back please.

 

[racdata]

Is there no one that can help me solving this problem

 

[ansentry]

Have a look at the attached sample, I got the "Year" bit working but starting the numbering again when it a new year well I have not solved that one yet.

Open the form add a new record and you will see that the prefix is 06- and the number will increase by 1.

Now close the form and exit access, change the Year on your computer to say 2007 and then open the db and add a new entry and you will see that the prefix is now 07-

Hope this is a little help.

 

[Rod D]

RACData

I think this is what you were after...

=Right(Date(),2) & Right(NZ(DMax("[EventNo]","[tbl_EmgCalls]","Left([EventNo],2)='" & Right(Date(),2) & "'"),"9900000")+1,5)

-What this does is takes the last two digits of the current year (Right(Date(),2) ) and appends the last 5 digits of the largest number found for the year, plus one (Dmax(...)). For the first entry of the new year, there will not be a "Max" value, so the NZ function provides a value for the null - "9900000".
-Hope this makes sense...

Place this code as the EventNo Default property, so that it only calculates a value for new records.

I would also change the format of the field to @@-@@@@@ and set Enabled to False (to stop users trying to change it!)

Hope this helps
Regards
Rod
(ex. Bulawayo)

 

[racdata]

Thank you it work perfectly.

 

[GaryPanic]

have a admin form with a counter in it - and every year end reset the counter - easest way of doing this

 

[racdata]

Code:
=Right(Date(),2) & Right(NZ(DMax("[EventNo]","[tbl_EmgCalls]","Left([EventNo],2)='" & Right(Date(),2) & "'"),"9900000")+1,5)-What this does is takes the last two digits of the current year (Right(Date(),2) ) and appends the last 5 digits of the largest number found for the year, plus one (Dmax(...)). For the first entry of the new year, there will not be a "Max" value, so the NZ function provides a value for the null - "9900000".
-Hope this makes sense...

Place this code as the EventNo Default property, so that it only calculates a value for new records.

I would also change the format of the field to @@-@@@@@ and set Enabled to False (to stop users trying to change it!)

Hope this helps
Regards
Rod

Hi everybody
I am now in China and busy translate a previous database to Chinese. The date format in RSA was dd/mm/yyyy and the numbering was working fine. But the date format here in China is yyyy/mm/dd and I can not get this working. (brain blocage) Can someone help please?

Regards
Arrie

 

[Dairy Farmer]

Right(DatePart("yyyy",date()),2)

or

Format$(Date(),"yy")

 

[Dairy Farmer]

Here is a procedure you could use. In the form I presume you have a date field. Put the following code in the date field to generate a new EventNo.

'Takes the last 2 digits of the year in the EventDate field
Year = Right((DatePart("yyyy", [EventDate])), 2)

'Finds the last event number for the year in Date1
LastEvent = Nz(DMax("[EventNo]", "[tbl_EmgCalls]", "Left([EventNo],2)='" & Year & "'"))

'If this is the first event of the year then start at 001. Change to add more digits.
If LastEvent < 1 Then
Me.EventNo = Year & "001"
Else

'If this is not the first event of the year then add 1 to the last event
Me.EventNo = Year & Right(LastEvent + 1, 3)
End If