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

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.

"Steve" wrote:

> 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
>
>
> "Stimpy707" <Stimpy707 RemoveThis @discussions.microsoft.com> wrote in message
> news:013B3521-0A64-40BF-9B0B-A15A9A5DE9C3@microsoft.com...
> > 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.
>
>
>

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



"Stimpy707" wrote:

> 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.
>
> "Steve" wrote:
>
> > 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
> >
> >
> > "Stimpy707" <Stimpy707 DeleteThis @discussions.microsoft.com> wrote in message
> > news:013B3521-0A64-40BF-9B0B-A15A9A5DE9C3@microsoft.com...
> > > 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.
> >
> >
> >

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" <Stimpy707 DeleteThis @discussions.microsoft.com> wrote in message
news:9A3CAB3F-2190-48C9-9523-B3310449491D@microsoft.com...
> 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.
>
> "Steve" wrote:
>
>> 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
>>
>>
>> "Stimpy707" <Stimpy707 DeleteThis @discussions.microsoft.com> wrote in message
>> news:013B3521-0A64-40BF-9B0B-A15A9A5DE9C3@microsoft.com...
>> > 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.
>>
>>
>>

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
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


"Richard" wrote:

> 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
>
>
>
> "Stimpy707" wrote:
>
> > 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.
> >
> > "Steve" wrote:
> >
> > > 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
> > >
> > >
> > > "Stimpy707" <Stimpy707.RemoveThis@discussions.microsoft.com> wrote in message
> > > news:013B3521-0A64-40BF-9B0B-A15A9A5DE9C3@microsoft.com...
> > > > 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.
> > >
> > >
> > >

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" wrote:

> 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" <Stimpy707.TakeThisOut@discussions.microsoft.com> wrote in message
> news:9A3CAB3F-2190-48C9-9523-B3310449491D@microsoft.com...
> > 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.
> >
> > "Steve" wrote:
> >
> >> 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
> >>
> >>
> >> "Stimpy707" <Stimpy707.TakeThisOut@discussions.microsoft.com> wrote in message
> >> news:013B3521-0A64-40BF-9B0B-A15A9A5DE9C3@microsoft.com...
> >> > 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.
> >>
> >>
> >>
>
>
>

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" <Richard RemoveThis @discussions.microsoft.com> wrote in message
news:77DDDF37-4C0A-4844-ABC8-BC93A4F2FAD3@microsoft.com...
> 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" wrote:
>
>> 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" <Stimpy707 RemoveThis @discussions.microsoft.com> wrote in message
>> news:9A3CAB3F-2190-48C9-9523-B3310449491D@microsoft.com...
>> > 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.
>> >
>> > "Steve" wrote:
>> >
>> >> 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
>> >>
>> >>
>> >> "Stimpy707" <Stimpy707 RemoveThis @discussions.microsoft.com> wrote in message
>> >> news:013B3521-0A64-40BF-9B0B-A15A9A5DE9C3@microsoft.com...
>> >> > 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.
>> >>
>> >>
>> >>
>>
>>
>>