Alphanumeric incremented number

[BruceM]

First, I know that my approach violates all sorts of rules. The trouble is
that I can't find another way to do this. I have not been able to find a
way to frame the question so that a groups search yields information that is
useful to my situation. So with the disclaimer that this approach is all
wrong according to normalization principles, it does at least produce the
desired result. I would like to know how I can accomplish this and still
maintain normalization rules.

The aim of this code is to assemble a number consisting of the department
code (one or two letters), the last two digits of the year, and an
incremented two-digit number (with a leading 0 as needed). For example:
D0701, D0702, AR0701, D0703. Actually, the number needs to appear as
D-07-01, AR-07-01, etc., but I expect I can figure out how to insert the
dashes for display purposes. Next year the number starts over at D0801, etc.

cboDept is an unbound combo box from which the user selects a department.
If I can find a way to do this without storing the two-digit year
redundantly I will need to store the department code in the field [DeptCode]
rather than pulling it from an unbound combo box.

This code is in the After Update event of cboDept. I realize that in a
multi-user environment I will need to make arrangements to guard against
duplication.

If Me.NewRecord Then
Dim strWhere As String, strDept As String, strYear As String
Dim varResult As Variant

strDept = Me.cboDept
strYear = Format(Me.RepDate, "yy")

strWhere = "[MyNumber] Like """ & strDept & strYear & "*"""
varResult = DMax("[MyNumber]", "tblMyTable", strWhere)

If IsNull(varResult) Then
Me.txtMyNumber = strDept & strYear & "01"
Else
Me.txtMyNumber = Left(varResult, Len(strDept) + 2) & _
Format(Val(Right(varResult, 2)) + 1, "00")
End If
End If

 

[Bob Hairgrove]

First, I know that my approach violates all sorts of rules. The troubleis
that I can't find another way to do this. I have not been able to find a
way to frame the question so that a groups search yields information that is
useful to my situation. So with the disclaimer that this approach is all
wrong according to normalization principles, it does at least produce the
desired result. I would like to know how I can accomplish this and still
maintain normalization rules.

The aim of this code is to assemble a number consisting of the department
code (one or two letters), the last two digits of the year, and an
incremented two-digit number (with a leading 0 as needed). For example:
D0701, D0702, AR0701, D0703. Actually, the number needs to appear as
D-07-01, AR-07-01, etc., but I expect I can figure out how to insert the
dashes for display purposes. Next year the number starts over at D0801, etc.

OK. Forget about the display representation for now and concentrate on
concepts.

You need to have at least a table of departments which has the
department code as its primary key. As to the other requirements,
there are serious probems which need to be addressed.

First of all, you have some imposed limitations which might not bear
out in real life, such as the fact that you can only have 99 or less
sequential numbers per year. It is easy to generate this kind of
numeric data on the fly *IF* you are storing the date information as a
DateTime type separate field in the table. However, you are inviting a
kind of "year 2000" scenario with the present setup.

As to the primary key, I would recommend using a column "Dept_ID", or
whatever you wish to call it, and a column with the DateTime
information you need. Make the primary key as a combination of these
two columns. Set up a foreign key constraint on the Dept_No column to
the table of departments.

In your reports or forms, it is pretty easy to generate the sequence
number on the fly given the date/time data and the department. Store
the sequence number if you must, but only to guard against changes in
the underlying data if necessary.

 

[BruceM]

First, I know that my approach violates all sorts of rules. The trouble is
that I can't find another way to do this. I have not been able to find a
way to frame the question so that a groups search yields information that
is
useful to my situation. So with the disclaimer that this approach is all
wrong according to normalization principles, it does at least produce the
desired result. I would like to know how I can accomplish this and still
maintain normalization rules.

The aim of this code is to assemble a number consisting of the department
code (one or two letters), the last two digits of the year, and an
incremented two-digit number (with a leading 0 as needed). For example:
D0701, D0702, AR0701, D0703. Actually, the number needs to appear as
D-07-01, AR-07-01, etc., but I expect I can figure out how to insert the
dashes for display purposes. Next year the number starts over at D0801,
etc.

OK. Forget about the display representation for now and concentrate on
concepts.

You need to have at least a table of departments which has the
department code as its primary key. As to the other requirements,
there are serious probems which need to be addressed.

There is such a table, just as you describe. It is the row source for
cboDept.

First of all, you have some imposed limitations which might not bear
out in real life, such as the fact that you can only have 99 or less
sequential numbers per year. It is easy to generate this kind of
numeric data on the fly *IF* you are storing the date information as a
DateTime type separate field in the table. However, you are inviting a
kind of "year 2000" scenario with the present setup.

I am always torn between providing too much information and having people
ignore the question as too complicated, and providing too little and
prompting reasonable questions such as the one you have asked. In the ten
years or so this numbering system has been in place the most *ever* for one
department in one year was 31. I am proceeding on the assumption that two
digits will continue to be adequate.

As to the primary key, I would recommend using a column "Dept_ID", or
whatever you wish to call it, and a column with the DateTime
information you need. Make the primary key as a combination of these
two columns. Set up a foreign key constraint on the Dept_No column to
the table of departments.

I called the field MyNumber in my sample code. RepDate is the date/time
field from which the two-digit year is derived. When you say "a column with
the DateTime information you need", are you referring to the two-digit year?
If so, we are back to redundancy, since the year is part of the RepDate
field. If you mean the RepDate field (the date the report was started), it
is in the table. By the way, I am using "report" in the generic sense, not
as a reference to an Access object.

In your reports or forms, it is pretty easy to generate the sequence
number on the fly given the date/time data and the department. Store
the sequence number if you must, but only to guard against changes in
the underlying data if necessary.

I know I can generate the number. That's what the code does. However, it
does so by storing the two-digit year, which is already part of another
field. I wonder if it is possible to generate the number without storing
the year twice.

 

[Bob Hairgrove]

I wonder if it is possible to generate the number without storing
the year twice.

Ask yourself instead: Why shouldn't it be possible?

If there is one valid reason (except to make reports easier to set up,
of course), then you need to store it.

 

[BruceM]

The reason I posted the question in the first place is that I couldn't
figure out how to generate the number without storing data (the year)
redundantly. It should be possible, I agree, but I couldn't figure out how,
so I asked a question. Asking myself the question yet again is not going to
make the answer appear.

I wonder if it is possible to generate the number without storing
the year twice.

Ask yourself instead: Why shouldn't it be possible?

If there is one valid reason (except to make reports easier to set up,
of course), then you need to store it.

 

[Bob Hairgrove]

The reason I posted the question in the first place is that I couldn't
figure out how to generate the number without storing data (the year)
redundantly. It should be possible, I agree, but I couldn't figure out how,
so I asked a question. Asking myself the question yet again is not going to
make the answer appear.

Check out the help topics for the Year() and the Format() functions
.... that's where you will find the answers.

 

[BruceM]

As my original post shows, I am familiar with the Format function. I am
also familiar with the Year function. I am familiar with quite a number of
functions, as you can infer from the code I posted. If you don't have an
answer to my question, please just say so.

The reason I posted the question in the first place is that I couldn't
figure out how to generate the number without storing data (the year)
redundantly. It should be possible, I agree, but I couldn't figure out
how,
so I asked a question. Asking myself the question yet again is not going
to
make the answer appear.

Check out the help topics for the Year() and the Format() functions
.... that's where you will find the answers.

 

[Bob Hairgrove]

[top-posting corrected...]

As my original post shows, I am familiar with the Format function. I am
also familiar with the Year function. I am familiar with quite a numberof
functions, as you can infer from the code I posted. If you don't have an
answer to my question, please just say so.

OK ... I apologize if I haven't been able to get a handle on all the
requirements here. It would help to know what the existing table
structures are and what constraints (foreign keys, etc.) you now have
in place. [Also, it would help not to top-post.]

Since RepDate is already in the table, there is no need to store this
data redundantly. What I am wondering about is *WHY* you think this is
necessary? You have (presumably) all the data you need ... obviously,
you know enough VBA that you can construct a query with a calculated
field using various functions such as Format and Year (and perhaps
DatePart as well) to re-assemble the data you already have into a
user-friendly format. I'm just not sure why you find it necessary to
store the derived data at all, since you have the original data to
work with.

As to your original question, you seem to be trying to construct a
string with data from the combo box in order to insert it into the
table as a *new* record, although the rest of your question pertains
to some kind of "group search" (i.e. looking for a record which
already exists).

Care to post some DDL so that we know what the existing tables look
like and don't have to guess about things such as primary and foreign
keys, data types and nullable fields?

 

[BruceM]

[top-posting corrected...]

As my original post shows, I am familiar with the Format function. I am
also familiar with the Year function. I am familiar with quite a number of
functions, as you can infer from the code I posted. If you don't have an
answer to my question, please just say so.

OK ... I apologize if I haven't been able to get a handle on all the
requirements here. It would help to know what the existing table
structures are and what constraints (foreign keys, etc.) you now have
in place. [Also, it would help not to top-post.]

Since RepDate is already in the table, there is no need to store this
data redundantly. What I am wondering about is *WHY* you think this is
necessary? You have (presumably) all the data you need ... obviously,
you know enough VBA that you can construct a query with a calculated
field using various functions such as Format and Year (and perhaps
DatePart as well) to re-assemble the data you already have into a
user-friendly format. I'm just not sure why you find it necessary to
store the derived data at all, since you have the original data to
work with.

As to your original question, you seem to be trying to construct a
string with data from the combo box in order to insert it into the
table as a *new* record, although the rest of your question pertains
to some kind of "group search" (i.e. looking for a record which
already exists).

Care to post some DDL so that we know what the existing tables look
like and don't have to guess about things such as primary and foreign
keys, data types and nullable fields?

--
Bob Hairgrove
(e-mail address removed)

I wish I could figure out a way in OE to automatically bottom post, but it
eludes me.

The reason I think it is necessary to store the year redundantly is because
I can't figure out an alternative.

If a record needs to be created for the Shipping department, the first thing
is to go to a new record, then to select Shipping, which uses the prefix
"S". The first record this year for Shipping is S-07-01. Then a record
needs to be created for Maintenance. It is numbered M-07-01. Then another
for shipping: S-07-02. And so forth.

If DDL means CREATE TABLE code, I don't know how to do that well enough for
it to be helpful. I can describe the table as it exists now.

tblReport
RptID (autonumber PK)
RptDate
RptNum (S-07-01, etc.)
Description and other such fields

tblDept (serves as a lookup table)
DeptCode (e.g "S"; can be the PK field)
DeptDescription (Shipping, etc.)

The table is the record source for a form. This is the code that will
generate the full number, minus hyphens, for the existing structure.
RptDate will default to the current date, and the user will select a
department from the combo box cboDept. This code is in the combo box After
Update event. It generates the number as required (including starting over
with S-08-01 next year). However, it stores the date redundantly in that
the two-digit year becomes part of RptNum. On the other hand, it doesn't
store the DeptCode except in the RptNum field.

If Me.NewRecord Then
Dim strWhere As String, strDept As String, strYear As String
Dim varResult As Variant

strDept = Me.cboDept
strYear = Format(Me.RptDate, "yy")

strWhere = "[RptNum] Like """ & strDept & strYear & "*"""
varResult = DMax("[RptNum]", "tblReport", strWhere)

If IsNull(varResult) Then
Me.txtRptNum = strDept & strYear & "01"
Else
Me.txtRptNme = Left(varResult, Len(strDept) + 2) & _
Format(Val(Right(varResult, 2)) + 1, "00")
End If
End If

This produces the number as described above.

What I envision is:

tblReport
RptID (autonumber PK)
RptDate
RptDept (Department code such as M, S, etc.)
RptNum (01, 02, etc.)

tblDept
DeptID (e.g "S"; can be the PK field)
RptID_Dept (FK to tblReport)
DeptDescription (Shipping, etc.)

The logic I'm looking for is (assuming Shipping was selected):
Find the latest record that has been created for the Shipping department
this year. If there is no record for Shipping this year, RptNum is 01. The
department code, the year, and RptNum will be combined into S0701 on
reports, forms, and so forth. If there is already a record for Shipping
this year, increment RptNum by 1. If there is one record for Shipping this
year, its number is 01. This latest record, then, is number 02, which
results in the concatenated value S0702.

I have showed RptID as the autonumber PK. A compound PK would probably have
to include RptDate, since the combination of RptDept and RptNum will be
repeated for some number each year (the only difference between S0701 and
S0801 is the two-digit year.

Let me repeat again that I do not *want* to store the data redundantly.
It's just that I can't figure out how to increment as intended otherwise.
The crux of the difficulty is here, I think:
strWhere = "[RptNum] Like """ & strDept & strYear & "*"""
RptNum is the field that combines the values in the first example (S0701,
etc.). I think I need find a way to substitute for [RptNum] a value
concatenated from three separate fields:
strWhere = "[Three concatenated fields] Like """ & strDept & strYear & "*"""
except I can't figure out how to represent that value. As a string, maybe?
strCombined = Me.cboDept & strYear & "*"""
strWhere = strCombined & " Like """ & strDept & strYear & "*"""

Anyhow, I've been slicing and dicing this for a while, and can't quite seem
to find what I seek.

 

[Bob Hairgrove]

If a record needs to be created for the Shipping department, the first thing
is to go to a new record, then to select Shipping, which uses the prefix
"S". The first record this year for Shipping is S-07-01. Then a record
needs to be created for Maintenance. It is numbered M-07-01. Then another
for shipping: S-07-02. And so forth.

If DDL means CREATE TABLE code, I don't know how to do that well enough for
it to be helpful. I can describe the table as it exists now.

tblReport
RptID (autonumber PK)
RptDate
RptNum (S-07-01, etc.)
Description and other such fields

tblDept (serves as a lookup table)
DeptCode (e.g "S"; can be the PK field)
DeptDescription (Shipping, etc.)

The table is the record source for a form. This is the code that will
generate the full number, minus hyphens, for the existing structure.
RptDate will default to the current date, and the user will select a
department from the combo box cboDept. This code is in the combo box After
Update event. It generates the number as required (including starting over
with S-08-01 next year). However, it stores the date redundantly in that
the two-digit year becomes part of RptNum. On the other hand, it doesn't
store the DeptCode except in the RptNum field.

If Me.NewRecord Then
Dim strWhere As String, strDept As String, strYear As String
Dim varResult As Variant

strDept = Me.cboDept
strYear = Format(Me.RptDate, "yy")

strWhere = "[RptNum] Like """ & strDept & strYear & "*"""
varResult = DMax("[RptNum]", "tblReport", strWhere)

If IsNull(varResult) Then
Me.txtRptNum = strDept & strYear & "01"
Else
Me.txtRptNme = Left(varResult, Len(strDept) + 2) & _
Format(Val(Right(varResult, 2)) + 1, "00")
End If
End If

This produces the number as described above.

What I envision is:

tblReport
RptID (autonumber PK)
RptDate
RptDept (Department code such as M, S, etc.)
RptNum (01, 02, etc.)

tblDept
DeptID (e.g "S"; can be the PK field)
RptID_Dept (FK to tblReport)
DeptDescription (Shipping, etc.)

The logic I'm looking for is (assuming Shipping was selected):
Find the latest record that has been created for the Shipping department
this year. If there is no record for Shipping this year, RptNum is 01. The
department code, the year, and RptNum will be combined into S0701 on
reports, forms, and so forth. If there is already a record for Shipping
this year, increment RptNum by 1. If there is one record for Shipping this
year, its number is 01. This latest record, then, is number 02, which
results in the concatenated value S0702.

I have showed RptID as the autonumber PK. A compound PK would probably have
to include RptDate, since the combination of RptDept and RptNum will be
repeated for some number each year (the only difference between S0701 and
S0801 is the two-digit year.

Let me repeat again that I do not *want* to store the data redundantly.
It's just that I can't figure out how to increment as intended otherwise.
The crux of the difficulty is here, I think:
strWhere = "[RptNum] Like """ & strDept & strYear & "*"""
RptNum is the field that combines the values in the first example (S0701,
etc.). I think I need find a way to substitute for [RptNum] a value
concatenated from three separate fields:
strWhere = "[Three concatenated fields] Like """ & strDept & strYear &"*"""
except I can't figure out how to represent that value. As a string, maybe?
strCombined = Me.cboDept & strYear & "*"""
strWhere = strCombined & " Like """ & strDept & strYear & "*"""

Anyhow, I've been slicing and dicing this for a while, and can't quite seem
to find what I seek.

OK. Sorry that I haven't had time to think about this some more until now.

It seems to me that the real issue is the sequence number, not the date. The
sequence number is only meaningful together with a department and the date.
Since the date can be entered independent of anything else, just store itonce
in a date/time column. I would use Now() as the default value and set its
Required property to Yes. Make the primary key out of the combination of these
two columns. Of course, Dept should also hae Required=Yes as well as a foreign
key constraint to the departments table.

Access' timestamp is only accurate to one second, but the likelihood of two
users entering a new record for the same department within one second of one
another is probably very small (is it?). In order to generate a sequence number
on the fly, you can then do something like this:

Public Function GetSequenceNum(dept As String, yr As Long) As String
' Note: you should pass a four-digit year to this function.
Const TABLE_NAME As String = "<whatever it is>"
Const DEPT_COLNAME As String = "<whatever it is>"
Const TIMESTAMP_COLNAME As String = "<whatever it is>"
Const QQ As String = """"

Dim strCriteria As String
Dim numPrevRows As Long

strCriteria = DEPT_COLNAME & " = " & QQ & dept & QQ & " And Year(" & _
TIMESTAMP_COLNAME & ") = " & yr
numPrevRows = DCount("*", TABLE_NAME, strCriteria)
' note that DCount will never return Null,
' whereas DMax, DMin, DLookup and DAvg can!
If numPrevRows > 99
'we are in trouble:
Err.Raise 666
End If
GetSequenceNum = Format(numPrevRows + 1, "00")
End Function

Of course, you could generate the entire pseudo-key in this function, too. I
didn't do that, however, because the temptation to store it back in the table
might be too great for some people! <g>

Since you are storing a timestamp instead of a sequential number, you most
likely won't have to worry about multi-user issues. What might prove to be a
problem is when the system clocks of the various client PCs are not in synch.
But since Access is not a client/server RDBMS, it always runs in the client's
process space, and there isn't much you can do about it. If this is an issue,
you could require all the clients to use a special function written (by you) to
query the time on a central server somewhere. Instead of using Now() as the
default value for the date/time column, you would have to use that function.

Hope this will serve your purpose.

 

[Bob Hairgrove]

Public Function GetSequenceNum(dept As String, yr As Long) As String
' Note: you should pass a four-digit year to this function.
Const TABLE_NAME As String = "<whatever it is>"
Const DEPT_COLNAME As String = "<whatever it is>"
Const TIMESTAMP_COLNAME As String = "<whatever it is>"
Const QQ As String = """"

Dim strCriteria As String
Dim numPrevRows As Long

strCriteria = DEPT_COLNAME & " = " & QQ & dept & QQ & " And Year(" & _
TIMESTAMP_COLNAME & ") = " & yr
numPrevRows = DCount("*", TABLE_NAME, strCriteria)
' note that DCount will never return Null,
' whereas DMax, DMin, DLookup and DAvg can!
If numPrevRows > 99
'we are in trouble:
Err.Raise 666
End If
GetSequenceNum = Format(numPrevRows + 1, "00")
End Function

While this function might prove useful for generating a NEW sequence number, it
doesn't give you an existing sequence number. To do that, you need to pass the
date/time value as type Date and change the criteria to this:

strCriteria = DEPT_COLNAME & " = " & QQ & dept & QQ & " And " & _
TIMESTAMP_COLNAME & " < " & tm

assuming that "tm" is the name of the argument of type Date passed to the
function.

Might be worth making two separate functions, calling them perhaps "GetSeqNo"
and "GetNewSeqNo"...

Sorry if this has caused any confusion!

 

[Douglas J. Steele]

When you're dealing with Date/Time values, they must be delimited with #
characters, and they must be in an unambiguous format. Since you cannot be
sure of the user's Short Date format (as set through Regional Settings),
that means you need to use

strCriteria = DEPT_COLNAME & " = " & QQ & dept & QQ & " And " & _
TIMESTAMP_COLNAME & " < " & Format(tm, "\#yyyy\-mm\-dd hh\:nn\:ss\#)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Public Function GetSequenceNum(dept As String, yr As Long) As String
' Note: you should pass a four-digit year to this function.
Const TABLE_NAME As String = "<whatever it is>"
Const DEPT_COLNAME As String = "<whatever it is>"
Const TIMESTAMP_COLNAME As String = "<whatever it is>"
Const QQ As String = """"

Dim strCriteria As String
Dim numPrevRows As Long

strCriteria = DEPT_COLNAME & " = " & QQ & dept & QQ & " And Year(" & _
TIMESTAMP_COLNAME & ") = " & yr
numPrevRows = DCount("*", TABLE_NAME, strCriteria)
' note that DCount will never return Null,
' whereas DMax, DMin, DLookup and DAvg can!
If numPrevRows > 99
'we are in trouble:
Err.Raise 666
End If
GetSequenceNum = Format(numPrevRows + 1, "00")
End Function

While this function might prove useful for generating a NEW sequence number,
it
doesn't give you an existing sequence number. To do that, you need to pass
the
date/time value as type Date and change the criteria to this:

strCriteria = DEPT_COLNAME & " = " & QQ & dept & QQ & " And " & _
TIMESTAMP_COLNAME & " < " & tm

assuming that "tm" is the name of the argument of type Date passed to the
function.

Might be worth making two separate functions, calling them perhaps
"GetSeqNo"
and "GetNewSeqNo"...

Sorry if this has caused any confusion!

 

[Bob Hairgrove]

When you're dealing with Date/Time values, they must be delimited with #
characters, and they must be in an unambiguous format. Since you cannot be
sure of the user's Short Date format (as set through Regional Settings),
that means you need to use

strCriteria = DEPT_COLNAME & " = " & QQ & dept & QQ & " And " & _
TIMESTAMP_COLNAME & " < " & Format(tm, "\#yyyy\-mm\-dd hh\:nn\:ss\#)

Thanks, I overlooked that.

 

[BruceM]

Thanks for posting the code examples. I see what you're getting at. You
are saying not to store the sequence number at all, and instead to use a
domain aggregate function to determine it on the fly.
There may be a problem in that the date defaults to the current date, but it
is not necessarily the date that will always be used. These are in essence
problem reports. If a problem occurs during the second shift, it may not be
entered into the system until the next day, at which time it will need to
show the date of the problem's occurence, not the date the record was
created. In other words, the record may need to be postdated. I would have
mentioned that sooner, but I never considered that a time/date stamp would
be part of the suggested solution.
A few more questions:
Why should I pass a four-digit year to the function? Is <whatever it is>
the field containing that information in the case of DEPT_COLNAME and
TIMESTAMP_COLNAME. For instance, would it be:
Const DEPT_COLNAME As String = Me.DeptName

I will need to filter the records from time to time. For instance, I may
need to look at just the records where the chosen department is Shipping,
and a particular customer is involved. It seems to me the sequence number
may change. The record that the user formerly saw as S-07-03 will appear as
S-07-01 if it is the first record for the year for that customer. Or do I
misunderstand how this works? Also, I would think that I will need always
to sort the records by the time stamp field (or maybe department and time
stamp, but something that ensures chronological order), as any other order
would cause the sequence number to change. Again, I would have mentioned
this sooner, but it didn't occur to me that a solution may be not to store
the sequence number at all. With the sequence number being assigned
dynamically, will it work as intended on reports such as a summary of all
records for the year (provided the listing is always chronological within a
department)? If so, I assume a text box on the report would be set to
something like:
=GetSequenceNumber([DeptName],Format([DateField],"yy")
Regarding using two different functions, are you saying one would be used
for a New Record, and another for all other records?

Public Function GetSequenceNum(dept As String, yr As Long) As String
' Note: you should pass a four-digit year to this function.
Const TABLE_NAME As String = "<whatever it is>"
Const DEPT_COLNAME As String = "<whatever it is>"
Const TIMESTAMP_COLNAME As String = "<whatever it is>"
Const QQ As String = """"

Dim strCriteria As String
Dim numPrevRows As Long

strCriteria = DEPT_COLNAME & " = " & QQ & dept & QQ & " And Year(" & _
TIMESTAMP_COLNAME & ") = " & yr
numPrevRows = DCount("*", TABLE_NAME, strCriteria)
' note that DCount will never return Null,
' whereas DMax, DMin, DLookup and DAvg can!
If numPrevRows > 99
'we are in trouble:
Err.Raise 666
End If
GetSequenceNum = Format(numPrevRows + 1, "00")
End Function

While this function might prove useful for generating a NEW sequence number,
it
doesn't give you an existing sequence number. To do that, you need to pass
the
date/time value as type Date and change the criteria to this:

strCriteria = DEPT_COLNAME & " = " & QQ & dept & QQ & " And " & _
TIMESTAMP_COLNAME & " < " & tm

assuming that "tm" is the name of the argument of type Date passed to the
function.

Might be worth making two separate functions, calling them perhaps
"GetSeqNo"
and "GetNewSeqNo"...

Sorry if this has caused any confusion!

 

[Bob Hairgrove]

Thanks for posting the code examples. I see what you're getting at. You
are saying not to store the sequence number at all, and instead to use a
domain aggregate function to determine it on the fly.
Correct.

There may be a problem in that the date defaults to the current date, but it
is not necessarily the date that will always be used. These are in essence
problem reports. If a problem occurs during the second shift, it may not be
entered into the system until the next day, at which time it will need to
show the date of the problem's occurence, not the date the record was
created. In other words, the record may need to be postdated. I would have
mentioned that sooner, but I never considered that a time/date stamp would
be part of the suggested solution.

Post-dating a record probably isn't going to matter by itself as long as there
is a complete timestamp (i.e. date + time value) to enter. With only a date, the
time part would always be the same (i.e. 12 midnight) which would create a
problem because you will most likely get multiple records for the same
department, year and date value. You might have to create a "custom counter"
(i.e. sequence number) after all. There are various approaches to this problem,
but it isn't trivial to get it to work correctly in a multi-user environment.

A few more questions:
Why should I pass a four-digit year to the function?

Because the Year() function will return a number such as "2007", and you want:
2007 < 2008
instead of:
2007 > 08
when your code builds the criteria string.

Is <whatever it is>
the field containing that information in the case of DEPT_COLNAME and
TIMESTAMP_COLNAME. For instance, would it be:
Const DEPT_COLNAME As String = Me.DeptName

No ... you need the name of the column, not its value. It should look like this:
Const DEPT_COLNAME As String = "DeptName"

I will need to filter the records from time to time. For instance, I may
need to look at just the records where the chosen department is Shipping,
and a particular customer is involved. It seems to me the sequence number
may change. The record that the user formerly saw as S-07-03 will appear as
S-07-01 if it is the first record for the year for that customer. Or doI
misunderstand how this works?

The criteria string built by the VBA function knows nothing about any particular
customers. However, it will change if records are not inserted in the proper
order. For example, if you have three existing rows, they will show the sequence
numbers 01, 02 and 03. But if someone inserts a new row with a timestamp in
between those of records 02 and 03, the new row will become 03 and the previous
03 will become 04. Whether or not this is acceptable depends on what these
numbers mean within your existing system.

At any rate, the sequence is always consistent with the date + time entered. If
you do not attach any meaning to the sequence number, but don't want it to ever
change once entered into the system, I would use a custom counter.

Also, I would think that I will need always
to sort the records by the time stamp field (or maybe department and time
stamp, but something that ensures chronological order), as any other order
would cause the sequence number to change.

Not so, since the sequence number is based on a count of records. However, as I
pointed out, entering new rows "in between" existing rows will cause the numbers
to change.

Again, I would have mentioned
this sooner, but it didn't occur to me that a solution may be not to store
the sequence number at all. With the sequence number being assigned
dynamically, will it work as intended on reports such as a summary of all
records for the year (provided the listing is always chronological within a
department)? If so, I assume a text box on the report would be set to
something like:
=GetSequenceNumber([DeptName],Format([DateField],"yy")
Yes.

Regarding using two different functions, are you saying one would be used
for a New Record, and another for all other records?

That's correct.

 

[BruceM]

Thanks for answering all of the various questions. I will probably need to
use some version of a custom counter. I had started off by wondering if I
could do so without storing the two-digit date, but in the course of this
thread I have found myself thinking about things I had not considered (such
as using a time stamp and incrementing the sequence number for display
only). It doesn't look like that approach will work in this situation, but
the logic behind it could prove useful in other contexts.
As regards the problem of generating a custom number in a multi-user
environment, I could wait until the Before Update event to calculate the
value. In a simpler version of this situation (no year in that one) I
calculate the value and display it in a text box. In the form's Before
Update event I calculate the value again and compare it to the displayed
value. If the two differ, I calculate the value again, and use a message
box to advise the user that the value has changed. There is error handling,
too, just in case, but the likelihood of duplicated values after the above
process is quite small.
I appreciate your taking the time to explain your thoughts on the matter.

Thanks for posting the code examples. I see what you're getting at. You
are saying not to store the sequence number at all, and instead to use a
domain aggregate function to determine it on the fly.
Correct.

There may be a problem in that the date defaults to the current date, but
it
is not necessarily the date that will always be used. These are in essence
problem reports. If a problem occurs during the second shift, it may not
be
entered into the system until the next day, at which time it will need to
show the date of the problem's occurence, not the date the record was
created. In other words, the record may need to be postdated. I would
have
mentioned that sooner, but I never considered that a time/date stamp would
be part of the suggested solution.

Post-dating a record probably isn't going to matter by itself as long as
there
is a complete timestamp (i.e. date + time value) to enter. With only a date,
the
time part would always be the same (i.e. 12 midnight) which would create a
problem because you will most likely get multiple records for the same
department, year and date value. You might have to create a "custom counter"
(i.e. sequence number) after all. There are various approaches to this
problem,
but it isn't trivial to get it to work correctly in a multi-user
environment.

A few more questions:
Why should I pass a four-digit year to the function?

Because the Year() function will return a number such as "2007", and you
want:
2007 < 2008
instead of:
2007 > 08
when your code builds the criteria string.

Is <whatever it is>
the field containing that information in the case of DEPT_COLNAME and
TIMESTAMP_COLNAME. For instance, would it be:
Const DEPT_COLNAME As String = Me.DeptName

No ... you need the name of the column, not its value. It should look like
this:
Const DEPT_COLNAME As String = "DeptName"

I will need to filter the records from time to time. For instance, I may
need to look at just the records where the chosen department is Shipping,
and a particular customer is involved. It seems to me the sequence number
may change. The record that the user formerly saw as S-07-03 will appear
as
S-07-01 if it is the first record for the year for that customer. Or do I
misunderstand how this works?

The criteria string built by the VBA function knows nothing about any
particular
customers. However, it will change if records are not inserted in the proper
order. For example, if you have three existing rows, they will show the
sequence
numbers 01, 02 and 03. But if someone inserts a new row with a timestamp in
between those of records 02 and 03, the new row will become 03 and the
previous
03 will become 04. Whether or not this is acceptable depends on what these
numbers mean within your existing system.

At any rate, the sequence is always consistent with the date + time entered.
If
you do not attach any meaning to the sequence number, but don't want it to
ever
change once entered into the system, I would use a custom counter.

Also, I would think that I will need always
to sort the records by the time stamp field (or maybe department and time
stamp, but something that ensures chronological order), as any other order
would cause the sequence number to change.

Not so, since the sequence number is based on a count of records. However,
as I
pointed out, entering new rows "in between" existing rows will cause the
numbers
to change.

Again, I would have mentioned
this sooner, but it didn't occur to me that a solution may be not to store
the sequence number at all. With the sequence number being assigned
dynamically, will it work as intended on reports such as a summary of all
records for the year (provided the listing is always chronological within a
department)? If so, I assume a text box on the report would be set to
something like:
=GetSequenceNumber([DeptName],Format([DateField],"yy")
Yes.

Regarding using two different functions, are you saying one would be used
for a New Record, and another for all other records?

That's correct.

 

[Bob Hairgrove]

As regards the problem of generating a custom number in a multi-user
environment, I could wait until the Before Update event to calculate the
value. In a simpler version of this situation (no year in that one) I
calculate the value and display it in a text box. In the form's Before
Update event I calculate the value again and compare it to the displayed
value. If the two differ, I calculate the value again, and use a message
box to advise the user that the value has changed. There is error handling,
too, just in case, but the likelihood of duplicated values after the above
process is quite small.

Ken Getz illustrated one such technique in the "Access 97 Developers Handbook"
which keeps a table with just one column and one row in a separate MDB file
which can be opened exclusively in code. You can then fetch the value, increment
it and store it back to the table before closing the database. If the database
is opened by another user, your code has to wait idle until it is free. To do
this, you wrap the OpenDatabase command inside a Do...Loop which traps the error
generated by the attempt to open the database, perhaps calls DoEvents to keep
the GUI responsive, and optionally times out after some number of seconds.

If you go this route, you will probably need one row, or even table, for each
department. This means that any changes to the table of departments wouldneed
to be reflected in your sequence table. And referential integrity cannot be
enforced across MDB files, so this could also become an issue. But the number of
departments is usually quite small, so it is probably doable.