Report numbering based on YYWW##

[Stimpy707]

I am trying to figure out how to create a numbering system based on the year,
week #, and two numbers starting from 01 through 99. The format would be
YYWW##. Lets use today as an example; 10/29/2008.

Since this is the year 2008, the first two numbers would be 08.
10/29 is in the 44th week of the year, so the next two numbers would be 44.
The last two digits would start at 01 and increment by 1 for each subsequent
report created that week. The last two digits would reset back to 01 for the
following week.

The report numbers for this week would look something like this...

084401
084402
084403...

Next week they would be as such...

084501
084502
084503...

One thousand years from now the report numbers will begin to be duplicated.
The Fountain of Youth has eluded me thus far so I don't think it will matter
too much.

This probably sounds a bit whacked to some of you power users and wizards
out there but I do have my reasons. Any help would be greatly appreciated.
Thanks in advance.

Take Care.

 

[Sninkle]

I can help you with the year and week but not sure how the report would now
which number to use.

=(Format(Now(),"YY")) & "" & (Format(Now(),"ww"))

 

[Stimpy707]

Thanks, Sninkle. I put that formula into a text box in a form and it worked.
Now I need to figure out how to add two digits to the end and increment by
one each time I enter data into a form. Thanks.

 

[Ken Sheridan]

When building a structured key like this its better to use separate columns
(fields) for each part, so I'd suggest using columns ReportYear (Text),
ReportWeek (Text) and ReportID (Long Integer Number). Its easy to get the
full structured report number from these, as you'll see below.

In table design view set the DefaultValue property of the ReportYear column
to:

=Format(Date(),"yy")

and that of the ReportWeek column to:

=Format(Date(),"ww")

Set the Validationrule property of the ReportID column to:

< 100

and its ValidationText property to something like:

Permissible number of records for week exceeded

For data entry purposes create a form bound to the table, or better still to
a sorted query based on the table, sorting on the ReportYear, ReportWeek and
ReportID columns, so that the records are ordered by the report number. In
the form include controls bound to the ReportYear, ReportWeek and ReportID
columns but set their Visible properties to False (No) to hide them. Add an
unbound text box control named ReportNumber with a ControlSource property of:

=[ReportYear] & [ReportWeek] & Format([ReportID],"00")

Add the following function to the form's module:

Private Function GetReportNumber()

Dim strCriteria As String

strCriteria = "ReportYear = """ & Me.ReportYear & """ " & _
"And ReportWeek = """ & Me.ReportWeek & """ "

Me.ReportId.DefaultValue = _
"""" & Nz(DMax("ReportID", "ReportsTable", strCriteria), 0) + 1 & """"

End Function

where ReportsTable is the name of your table. What this code does is look
up the highest (MAX) ReportID value already in the table for the current
year/week and add 1. If there are no records yet fro the current week the
DMax function will return Null, so the Nz function converts this to zero.

In the form's properties sheet set its On Current and After Del Confirm
event properties to:

=GetReportNumber()

You should now find the full structured report number shows in the unbound
text box when you navigate to a new record, or if you should have occasion to
delete the latest record for the current week, in which case the last number
will be reused.

If you create a report based on the table you can again use a text box with
a ControlSource of:

=[ReportYear] & [ReportWeek] & Format([ReportID],"00")

to show the structured report number.

I realize the above sounds a bit long-winded, but it probably takes longer
to describe it than to implement it. Just take it step by step and it should
work fine.

Ken Sheridan
Stafford, England

 

[Stimpy707]

Wow! Thanks a lot Ken. That worked great! I really appreciate your help.
I especially appreciate the depth in which you explain solutions. God save
the Queen!

When building a structured key like this its better to use separate columns
(fields) for each part, so I'd suggest using columns ReportYear (Text),
ReportWeek (Text) and ReportID (Long Integer Number). Its easy to get the
full structured report number from these, as you'll see below.

In table design view set the DefaultValue property of the ReportYear column
to:

=Format(Date(),"yy")

and that of the ReportWeek column to:

=Format(Date(),"ww")

Set the Validationrule property of the ReportID column to:

< 100

and its ValidationText property to something like:

Permissible number of records for week exceeded

For data entry purposes create a form bound to the table, or better still to
a sorted query based on the table, sorting on the ReportYear, ReportWeek and
ReportID columns, so that the records are ordered by the report number. In
the form include controls bound to the ReportYear, ReportWeek and ReportID
columns but set their Visible properties to False (No) to hide them. Add an
unbound text box control named ReportNumber with a ControlSource property of:

=[ReportYear] & [ReportWeek] & Format([ReportID],"00")

Add the following function to the form's module:

Private Function GetReportNumber()

Dim strCriteria As String

strCriteria = "ReportYear = """ & Me.ReportYear & """ " & _
"And ReportWeek = """ & Me.ReportWeek & """ "

Me.ReportId.DefaultValue = _
"""" & Nz(DMax("ReportID", "ReportsTable", strCriteria), 0) + 1 & """"

End Function

where ReportsTable is the name of your table. What this code does is look
up the highest (MAX) ReportID value already in the table for the current
year/week and add 1. If there are no records yet fro the current week the
DMax function will return Null, so the Nz function converts this to zero.

In the form's properties sheet set its On Current and After Del Confirm
event properties to:

=GetReportNumber()

You should now find the full structured report number shows in the unbound
text box when you navigate to a new record, or if you should have occasion to
delete the latest record for the current week, in which case the last number
will be reused.

If you create a report based on the table you can again use a text box with
a ControlSource of:

=[ReportYear] & [ReportWeek] & Format([ReportID],"00")

to show the structured report number.

I realize the above sounds a bit long-winded, but it probably takes longer
to describe it than to implement it. Just take it step by step and it should
work fine.

Ken Sheridan
Stafford, England

I am trying to figure out how to create a numbering system based on the year,
week #, and two numbers starting from 01 through 99. The format would be
YYWW##. Lets use today as an example; 10/29/2008.

Since this is the year 2008, the first two numbers would be 08.
10/29 is in the 44th week of the year, so the next two numbers would be 44.
The last two digits would start at 01 and increment by 1 for each subsequent
report created that week. The last two digits would reset back to 01 for the
following week.

The report numbers for this week would look something like this...

084401
084402
084403...

Next week they would be as such...

084501
084502
084503...

One thousand years from now the report numbers will begin to be duplicated.
The Fountain of Youth has eluded me thus far so I don't think it will matter
too much.

This probably sounds a bit whacked to some of you power users and wizards
out there but I do have my reasons. Any help would be greatly appreciated.
Thanks in advance.

Take Care.

 

[Stimpy707]

When building a structured key like this its better to use separate columns
(fields) for each part, so I'd suggest using columns ReportYear (Text),
ReportWeek (Text) and ReportID (Long Integer Number). Its easy to get the
full structured report number from these, as you'll see below.

In table design view set the DefaultValue property of the ReportYear column
to:

=Format(Date(),"yy")

and that of the ReportWeek column to:

=Format(Date(),"ww")

Set the Validationrule property of the ReportID column to:

< 100

and its ValidationText property to something like:

Permissible number of records for week exceeded

For data entry purposes create a form bound to the table, or better still to
a sorted query based on the table, sorting on the ReportYear, ReportWeek and
ReportID columns, so that the records are ordered by the report number. In
the form include controls bound to the ReportYear, ReportWeek and ReportID
columns but set their Visible properties to False (No) to hide them. Add an
unbound text box control named ReportNumber with a ControlSource property of:

=[ReportYear] & [ReportWeek] & Format([ReportID],"00")

Add the following function to the form's module:

Private Function GetReportNumber()

Dim strCriteria As String

strCriteria = "ReportYear = """ & Me.ReportYear & """ " & _
"And ReportWeek = """ & Me.ReportWeek & """ "

Me.ReportId.DefaultValue = _
"""" & Nz(DMax("ReportID", "ReportsTable", strCriteria), 0) + 1 & """"

End Function

where ReportsTable is the name of your table. What this code does is look
up the highest (MAX) ReportID value already in the table for the current
year/week and add 1. If there are no records yet fro the current week the
DMax function will return Null, so the Nz function converts this to zero.

In the form's properties sheet set its On Current and After Del Confirm
event properties to:

=GetReportNumber()

You should now find the full structured report number shows in the unbound
text box when you navigate to a new record, or if you should have occasion to
delete the latest record for the current week, in which case the last number
will be reused.

If you create a report based on the table you can again use a text box with
a ControlSource of:

=[ReportYear] & [ReportWeek] & Format([ReportID],"00")

to show the structured report number.

I realize the above sounds a bit long-winded, but it probably takes longer
to describe it than to implement it. Just take it step by step and it should
work fine.

Ken Sheridan
Stafford, England

I am trying to figure out how to create a numbering system based on the year,
week #, and two numbers starting from 01 through 99. The format would be
YYWW##. Lets use today as an example; 10/29/2008.

Since this is the year 2008, the first two numbers would be 08.
10/29 is in the 44th week of the year, so the next two numbers would be 44.
The last two digits would start at 01 and increment by 1 for each subsequent
report created that week. The last two digits would reset back to 01 for the
following week.

The report numbers for this week would look something like this...

084401
084402
084403...

Next week they would be as such...

084501
084502
084503...

One thousand years from now the report numbers will begin to be duplicated.
The Fountain of Youth has eluded me thus far so I don't think it will matter
too much.

This probably sounds a bit whacked to some of you power users and wizards
out there but I do have my reasons. Any help would be greatly appreciated.
Thanks in advance.

Take Care.

 

[David K.]

Ken. If you are out there, can you give me a little more help on this? I
just now got back to working on my database and can't get to work. I had it
working several months ago but messed something up and can't figure out what
went wrong. Over the last few days I have tried doing it again on a new db
just to test and keep getting this error when I open the form.

The expresstion On Current you entered as the event property setting
produced the following error: Syntax error (missing operator) in query
expresstion 'ReportYear="09" ReportWeek="43" '.

I carefully followed the instructions you laid out below but somehow I'm
missing something. Like I said, I had it working several months ago.

Any help from you or anyone else would be greatly appreciated.

Thanks,
David K.

When building a structured key like this its better to use separate columns
(fields) for each part, so I'd suggest using columns ReportYear (Text),
ReportWeek (Text) and ReportID (Long Integer Number). Its easy to get the
full structured report number from these, as you'll see below.

In table design view set the DefaultValue property of the ReportYear column
to:

=Format(Date(),"yy")

and that of the ReportWeek column to:

=Format(Date(),"ww")

Set the Validationrule property of the ReportID column to:

< 100

and its ValidationText property to something like:

Permissible number of records for week exceeded

For data entry purposes create a form bound to the table, or better still to
a sorted query based on the table, sorting on the ReportYear, ReportWeek and
ReportID columns, so that the records are ordered by the report number. In
the form include controls bound to the ReportYear, ReportWeek and ReportID
columns but set their Visible properties to False (No) to hide them. Add an
unbound text box control named ReportNumber with a ControlSource property of:

=[ReportYear] & [ReportWeek] & Format([ReportID],"00")

Add the following function to the form's module:

Private Function GetReportNumber()

Dim strCriteria As String

strCriteria = "ReportYear = """ & Me.ReportYear & """ " & _
"And ReportWeek = """ & Me.ReportWeek & """ "

Me.ReportId.DefaultValue = _
"""" & Nz(DMax("ReportID", "ReportsTable", strCriteria), 0) + 1 & """"

End Function

where ReportsTable is the name of your table. What this code does is look
up the highest (MAX) ReportID value already in the table for the current
year/week and add 1. If there are no records yet fro the current week the
DMax function will return Null, so the Nz function converts this to zero.

In the form's properties sheet set its On Current and After Del Confirm
event properties to:

=GetReportNumber()

You should now find the full structured report number shows in the unbound
text box when you navigate to a new record, or if you should have occasion to
delete the latest record for the current week, in which case the last number
will be reused.

If you create a report based on the table you can again use a text box with
a ControlSource of:

=[ReportYear] & [ReportWeek] & Format([ReportID],"00")

to show the structured report number.

I realize the above sounds a bit long-winded, but it probably takes longer
to describe it than to implement it. Just take it step by step and it should
work fine.

Ken Sheridan
Stafford, England

I am trying to figure out how to create a numbering system based on the year,
week #, and two numbers starting from 01 through 99. The format would be
YYWW##. Lets use today as an example; 10/29/2008.

Since this is the year 2008, the first two numbers would be 08.
10/29 is in the 44th week of the year, so the next two numbers would be 44.
The last two digits would start at 01 and increment by 1 for each subsequent
report created that week. The last two digits would reset back to 01 for the
following week.

The report numbers for this week would look something like this...

084401
084402
084403...

Next week they would be as such...

084501
084502
084503...

One thousand years from now the report numbers will begin to be duplicated.
The Fountain of Youth has eluded me thus far so I don't think it will matter
too much.

This probably sounds a bit whacked to some of you power users and wizards
out there but I do have my reasons. Any help would be greatly appreciated.
Thanks in advance.

Take Care.

 

[John W. Vinson]

The expresstion On Current you entered as the event property setting
produced the following error: Syntax error (missing operator) in query
expresstion 'ReportYear="09" ReportWeek="43" '.

I carefully followed the instructions you laid out below but somehow I'm
missing something. Like I said, I had it working several months ago.

You're missing the word AND (between the two expressions). It's probably a
problem with the (rather tricky!) quotemarks. Please post your actual code.