Increment custom number format ie. 080100, 080101 (yy####)

  • Thread starter Thread starter Stimpy707
  • Start date Start date
S

Stimpy707

I've seen similar posts but I still need a little help.
If you have a better suggestion based on something you have used please let
me know. I'm always open to different ideas. Also, I'm an Access newbie so
you need to type slow and loud :)

Every so often, we will be using a form to record manufacturing results. I
would like to create a field that gives the report a number when it is
created.

The previous database (that has since died of unnatural causes) was set up
with the two-digit year followed by four digits. 080100 would represent the
year 2008, report number 0100. The next report would be 080101, and so on.

After yy9999, I imagine that the four digit number could begin again at 0000
but it's not important because we fill out so few of these it would take
hundreds of years for that to happen.

Thanks in advance.
 
Valid point but I don't think a date stamp would work because this number
would be referenced by other documents and even our customers. I think they
would be annoyed if they called on the telephone and had to reference a
document by 08/28/2008 5:35:54 PM as an example.

That said, I did try it your suggestion of putting a field in my table and
setting the default value to Now() but all that happened was now every new
entry has the same "8/29/2008 2:02:07 PM."

Maybe I will just have a five digit incrementing number starting with 10001.
 
Hi Stimpy,

If you need something to increment a number you can try this in the Before
Insert
Event Procedure on your form. (I use it in a sub form)

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strCriteria As String

Me.YourField = Nz(DMax("YourField", "YourTable", strCriteria), 080100) + 1

End Sub


Richard
Good Luck
 
Maybe I am missing something in your code, but I don't see how the year part
will increment when the year changes to 2009. If the max report number on
12/31/2008 is 080300, it looks like the first report number in 2009 would be
080301.

This is my version:

NOTE: replace "RptNumberField" with your field name (two places) and
replace "TheTable" with your table name (one place)


Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strCriteria As String
Dim TheYear As String
Dim MaxRptNum As Integer

TheYear = Right(Year(Date), 2)

strCriteria = "Left(RptNumberField, 2) = '" & TheYear & "'"
MaxRptNum = Nz(Right(DMax("RptNumberField", "TheTable", strCriteria), 4),
0)

MaxRptNum =MaxRptNum + 1

Me.YourField = TheYear & Format(MaxRptNum, "0000")
End Sub

HTH
 
why couldn't he use:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strCriteria As String

Me.YourField = Nz(DMax("YourField", "YourTable", strCriteria), 0) + 1

End Sub

then use a query to concatenate the date part for his report?




Steve said:
That can't be! If as you say every new entry has the same "8/29/2008
2:02:07 PM, that would mean that every new entry was entered at the same
exact second. Did you set the Default property of the field to:
= Now()

......... reference a document by 08/28/2008 5:35:54 PM as an example.......
You don't use the value of the DateStamp field for the document number!!!
You use the following expression for the controlsource of the textbox in
your forms and reports where you want to display the document number:
=Format([DateStamp],"yymmddhhnnss")

Steve



Stimpy707 said:
Valid point but I don't think a date stamp would work because this number
would be referenced by other documents and even our customers. I think
they
would be annoyed if they called on the telephone and had to reference a
document by 08/28/2008 5:35:54 PM as an example.

That said, I did try it your suggestion of putting a field in my table and
setting the default value to Now() but all that happened was now every new
entry has the same "8/29/2008 2:02:07 PM."

Maybe I will just have a five digit incrementing number starting with
10001.
Click to expand...
Click to expand...
 
What happens when the battery in the system does dead?
If you did not notice it, the dates would be old, NO?

Rich W.

Yeah that would work! The advamtage of a date stamp is that the date the
record was entered is automatically entered. If ever one would need the
date, it is available.

Steve


Richard said:
why couldn't he use:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strCriteria As String

Me.YourField = Nz(DMax("YourField", "YourTable", strCriteria), 0) + 1

End Sub

then use a query to concatenate the date part for his report?




Steve said:
That can't be! If as you say every new entry has the same "8/29/2008
2:02:07 PM, that would mean that every new entry was entered at the same
exact second. Did you set the Default property of the field to:
= Now()

......... reference a document by 08/28/2008 5:35:54 PM as an
example.......
You don't use the value of the DateStamp field for the document number!!!
You use the following expression for the controlsource of the textbox in
your forms and reports where you want to display the document number:
=Format([DateStamp],"yymmddhhnnss")

Steve



Valid point but I don't think a date stamp would work because this
number
would be referenced by other documents and even our customers. I think
they
would be annoyed if they called on the telephone and had to reference a
document by 08/28/2008 5:35:54 PM as an example.

That said, I did try it your suggestion of putting a field in my table
and
setting the default value to Now() but all that happened was now every
new
entry has the same "8/29/2008 2:02:07 PM."

Maybe I will just have a five digit incrementing number starting with
10001.

:

Why do you even need a report#? The report# is a carryover from the
old
days
when records were kept in file cabinets and filed numerically. In the
electronic age you can use the date and time to datestamp the
elctronic
report and then be able to retrieve any specific report by its
datestamp.
Just add a field named DateStamp to your table and set its default
value
to
Now().

Steve


I've seen similar posts but I still need a little help.
If you have a better suggestion based on something you have used
please
let
me know. I'm always open to different ideas. Also, I'm an Access
newbie
so
you need to type slow and loud :)

Every so often, we will be using a form to record manufacturing
results.
I
would like to create a field that gives the report a number when it
is
created.

The previous database (that has since died of unnatural causes) was
set
up
with the two-digit year followed by four digits. 080100 would
represent
the
year 2008, report number 0100. The next report would be 080101, and
so
on.

After yy9999, I imagine that the four digit number could begin again
at
0000
but it's not important because we fill out so few of these it would
take
hundreds of years for that to happen.

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
Using two columns (fields) makes it a lot easier. Both fields should be
integer number data type and named ReportYear and ReportNumber say. Set the
DefaultValue property of the ReportYear column to:

Year(Date())

The user will never see the values in either of these columns as in forms,
reports or queries you'd show the full report number. In an unbound computed
column in a form or report for instance its ControlSource property would be:

= Right(CStr(ReportYear),2) & Format(ReportNumber,"0000")

In the form's BeforeInsert event procedure put:

Dim strCriteria as String

strCriteria = "ReportYear = " & Year(Date())

Me.ReportNumber = Nz(DMax("ReportNumber", "YourTableName", strCriteria),0)

This will start the number sequence at 1 for each year, which I think is
what you had in mind.

Some people might argue that the report 'number' is not really a number in
the true sense but an encoding system, and it would therefore be more
appropriate to use a text data type. I would disagree as the number has both
an ordinal significance (the order in which the reports are produced) and a
cardinal significance ( the latest number per year is the number produced
that year) so should be an integer number data type. The computed
combination of the two values *is* an encoding system of course.

One caveat: in a multi-user environment two users adding records
simultaneously could get the same number. You should guard against this by
indexing the ReportYear and ReportNumber columns uniquely; that's an index on
both columns together, not individually. The simplest way is to make them
the composite primary key of the table, which you can do in table design view
by dragging down over both fields to highlight them, right-click and select
primary key from the shortcut menu. If a conflict does then occur the second
user attempting to save the record would trigger a data error and the illegal
record would not be saved.

Even if this application is not in a multi-user environment the columns
should be indexed uniquely of course.

Apologies in advance if you respond to this and I don't get back to you, but
after tomorrow I'll be away incommunicado for a while.

Ken Sheridan
Stafford, England
 
Back
Top