GENERATING AUTOMATIC NUMBERS

[JAMES FENNEL]

I am responsible for keeping track of Safety discrepacies on board my ship.
I have a database to do so and I'm trying to automate some of the features to
make it easier. I would like to automate the ID feature using a Year-Julian
Date-auto increment format. Year in NN format, Julian Date in NNN format,
Auto Increment in NNNN format (not to exceed 9999 and reset each new julian
date). I would like this feature to key off when the the preceding field,
INSPTR loses focus. Can anybody out there help me?

 

[Graham Mandeno]

Hi James

I suggest you store two fields:
RecordDate (long)
RecordSequence (integer)

Store the date in the RecordDate field (I suggest datatype "long" instead of
"date/time" to ensure that it can only be a date part with no time) and
store the NNNN part in the RecordSequence field. Make the two fields
together the primary (or a unique secondary) composite key to ensure no
duplicates.

If you typically enter records on the date to which they apply, then set the
DefaultValue for RecordDate to "=Date()". Storing the date may have other
spin-offs, such as the ability to easily retrieve all records from a
particular month.

Now, the next sequence number for a given date may be ascertained as
follows:

iNextNum = Nz( DMax( "RecordSequence", "TableName", _
"RecordDate=" & Format( NewDateValue, "\#mm\dd\yyyy\#" ) ), 0) + 1

In plain English, find the maximum sequence number for the given date (and
if there ain't one, use zero) and then add one to it.

Now, the ID number you want in the form YYDDDNNNN can easily be generated on
the fly in a calculated field in a query, form or report:
IDString = Format( [RecordDate], "yy" ) _
& Format( DatePart ( "y", [RecordDate] ), "000" ) _
& Format ( [RecordSequence], "0000" )

This also means that if in the future you need to exceed the limit of 9999
records a day, all you need do is add another "0" to the format string and
the job is done.

 

[JAMES FENNEL]

Hi Graham,
Judging by what I'm seeing this should work, but I'm havin trouble resolving
some placement issues. The "iNextNum" calculator; where do I put this? I've
tried placing this in the DefaultValue Criteria in my table and it gives me
errors. I've tried placing it as a calculated field in a Query and it's
giving me problems there. What am I doing wrong. The "IDString"
calculation; I would like to keep my form the same so how do I get all of
these new inputs to show up in the "ID" field on my form when I hit the Add
button? I've got a copy of "Running Microsoft Access 2000" and it's been a
big help, but this one is a little beyond me.
Thanks for the Help so far and I hope you can get me past this.
James

Hi James

I suggest you store two fields:
RecordDate (long)
RecordSequence (integer)

Store the date in the RecordDate field (I suggest datatype "long" instead of
"date/time" to ensure that it can only be a date part with no time) and
store the NNNN part in the RecordSequence field. Make the two fields
together the primary (or a unique secondary) composite key to ensure no
duplicates.

If you typically enter records on the date to which they apply, then set the
DefaultValue for RecordDate to "=Date()". Storing the date may have other
spin-offs, such as the ability to easily retrieve all records from a
particular month.

Now, the next sequence number for a given date may be ascertained as
follows:

iNextNum = Nz( DMax( "RecordSequence", "TableName", _
"RecordDate=" & Format( NewDateValue, "\#mm\dd\yyyy\#" ) ), 0) + 1

In plain English, find the maximum sequence number for the given date (and
if there ain't one, use zero) and then add one to it.

Now, the ID number you want in the form YYDDDNNNN can easily be generated on
the fly in a calculated field in a query, form or report:
IDString = Format( [RecordDate], "yy" ) _
& Format( DatePart ( "y", [RecordDate] ), "000" ) _
& Format ( [RecordSequence], "0000" )

This also means that if in the future you need to exceed the limit of 9999
records a day, all you need do is add another "0" to the format string and
the job is done.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am responsible for keeping track of Safety discrepacies on board my ship.
I have a database to do so and I'm trying to automate some of the features to
make it easier. I would like to automate the ID feature using a Year-Julian
Date-auto increment format. Year in NN format, Julian Date in NNN format,
Auto Increment in NNNN format (not to exceed 9999 and reset each new julian
date). I would like this feature to key off when the the preceding field,
INSPTR loses focus. Can anybody out there help me?

 

[Graham Mandeno]

Hi James

The expression to calculate the next number will only work in VBA code -
not, say, it the DefaultValue property of the table field. The main
impediment to calculating it at that point is that you first need to know
the date value, in order to look up the current maximum sequence number.

I suggest you ascertain the next sequence value either:
1) when the record is saved (Form_AfterInsert), or
2) when the user starts to enter the new record (Form_BeforeInsert), or
3) when the date value is entered (RecordDate_AfterUpdate)

Where you do it depends on your requirements. Option (1) is best, unless
your user needs to see the sequence number *before* saving the record. If
this is necessary then use (2) if the date can only get a default value, or
(3) if the user enters the date. If both (there is a default value which
can be overridden) then use both places.

You say you have an "Add" button. That might be an appropriate time to get
the new sequence number - provided, of course, that the date is known at
that point. You can't get the next number for a given date if you don't yet
know the date!

Note that you also cannot calculate the "IDString" until both the date *and*
the sequence number have been ascertained.

If you have a multi-user environment, then lengthening the delay between
calculating the sequence number and saving the record will increase the
possibility that someone else will get in and try to nab the same number.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,
Judging by what I'm seeing this should work, but I'm havin trouble resolving
some placement issues. The "iNextNum" calculator; where do I put this? I've
tried placing this in the DefaultValue Criteria in my table and it gives me
errors. I've tried placing it as a calculated field in a Query and it's
giving me problems there. What am I doing wrong. The "IDString"
calculation; I would like to keep my form the same so how do I get all of
these new inputs to show up in the "ID" field on my form when I hit the Add
button? I've got a copy of "Running Microsoft Access 2000" and it's been a
big help, but this one is a little beyond me.
Thanks for the Help so far and I hope you can get me past this.
James

Hi James

I suggest you store two fields:
RecordDate (long)
RecordSequence (integer)

Store the date in the RecordDate field (I suggest datatype "long" instead of
"date/time" to ensure that it can only be a date part with no time) and
store the NNNN part in the RecordSequence field. Make the two fields
together the primary (or a unique secondary) composite key to ensure no
duplicates.

If you typically enter records on the date to which they apply, then set the
DefaultValue for RecordDate to "=Date()". Storing the date may have other
spin-offs, such as the ability to easily retrieve all records from a
particular month.

Now, the next sequence number for a given date may be ascertained as
follows:

iNextNum = Nz( DMax( "RecordSequence", "TableName", _
"RecordDate=" & Format( NewDateValue, "\#mm\dd\yyyy\#" ) ), 0) + 1

In plain English, find the maximum sequence number for the given date (and
if there ain't one, use zero) and then add one to it.

Now, the ID number you want in the form YYDDDNNNN can easily be generated on
the fly in a calculated field in a query, form or report:
IDString = Format( [RecordDate], "yy" ) _
& Format( DatePart ( "y", [RecordDate] ), "000" ) _
& Format ( [RecordSequence], "0000" )

This also means that if in the future you need to exceed the limit of 9999
records a day, all you need do is add another "0" to the format string and
the job is done.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am responsible for keeping track of Safety discrepacies on board my ship.
I have a database to do so and I'm trying to automate some of the

features
to

make it easier. I would like to automate the ID feature using a Year-Julian
Date-auto increment format. Year in NN format, Julian Date in NNN format,
Auto Increment in NNNN format (not to exceed 9999 and reset each new julian
date). I would like this feature to key off when the the preceding field,
INSPTR loses focus. Can anybody out there help me?

 

[JAMES FENNEL]

Graham,
I must be a moron because I can't get this to work. I keep getting a
"missing operator" error with the "RecordDate=" part of the statement. I've
about given up on this.
I have the following columns in my table "ID", "RECORDSEQUENCE",
"RECORDDATE" and "REPORT DATE" in a table called "HAZARDS1". I've set up the
"REPORT DATE" to be automatically inputed into the "REPORT DATE" field (in
mm/dd/yy format) whenever the "ADD" button is depressed. It looks like
option 3 is the way I want to go. What in the heck am I'm doing wrong.
James

Hi James

The expression to calculate the next number will only work in VBA code -
not, say, it the DefaultValue property of the table field. The main
impediment to calculating it at that point is that you first need to know
the date value, in order to look up the current maximum sequence number.

I suggest you ascertain the next sequence value either:
1) when the record is saved (Form_AfterInsert), or
2) when the user starts to enter the new record (Form_BeforeInsert), or
3) when the date value is entered (RecordDate_AfterUpdate)

Where you do it depends on your requirements. Option (1) is best, unless
your user needs to see the sequence number *before* saving the record. If
this is necessary then use (2) if the date can only get a default value, or
(3) if the user enters the date. If both (there is a default value which
can be overridden) then use both places.

You say you have an "Add" button. That might be an appropriate time to get
the new sequence number - provided, of course, that the date is known at
that point. You can't get the next number for a given date if you don't yet
know the date!

Note that you also cannot calculate the "IDString" until both the date *and*
the sequence number have been ascertained.

If you have a multi-user environment, then lengthening the delay between
calculating the sequence number and saving the record will increase the
possibility that someone else will get in and try to nab the same number.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,
Judging by what I'm seeing this should work, but I'm havin trouble resolving
some placement issues. The "iNextNum" calculator; where do I put this? I've
tried placing this in the DefaultValue Criteria in my table and it gives me
errors. I've tried placing it as a calculated field in a Query and it's
giving me problems there. What am I doing wrong. The "IDString"
calculation; I would like to keep my form the same so how do I get all of
these new inputs to show up in the "ID" field on my form when I hit the Add
button? I've got a copy of "Running Microsoft Access 2000" and it's been a
big help, but this one is a little beyond me.
Thanks for the Help so far and I hope you can get me past this.
James

Hi James

I suggest you store two fields:
RecordDate (long)
RecordSequence (integer)

Store the date in the RecordDate field (I suggest datatype "long" instead of
"date/time" to ensure that it can only be a date part with no time) and
store the NNNN part in the RecordSequence field. Make the two fields
together the primary (or a unique secondary) composite key to ensure no
duplicates.

If you typically enter records on the date to which they apply, then set the
DefaultValue for RecordDate to "=Date()". Storing the date may have other
spin-offs, such as the ability to easily retrieve all records from a
particular month.

Now, the next sequence number for a given date may be ascertained as
follows:

iNextNum = Nz( DMax( "RecordSequence", "TableName", _
"RecordDate=" & Format( NewDateValue, "\#mm\dd\yyyy\#" ) ), 0) + 1

In plain English, find the maximum sequence number for the given date (and
if there ain't one, use zero) and then add one to it.

Now, the ID number you want in the form YYDDDNNNN can easily be generated on
the fly in a calculated field in a query, form or report:
IDString = Format( [RecordDate], "yy" ) _
& Format( DatePart ( "y", [RecordDate] ), "000" ) _
& Format ( [RecordSequence], "0000" )

This also means that if in the future you need to exceed the limit of 9999
records a day, all you need do is add another "0" to the format string and
the job is done.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am responsible for keeping track of Safety discrepacies on board my
ship.
I have a database to do so and I'm trying to automate some of the features
to
make it easier. I would like to automate the ID feature using a
Year-Julian
Date-auto increment format. Year in NN format, Julian Date in NNN format,
Auto Increment in NNNN format (not to exceed 9999 and reset each new
julian
date). I would like this feature to key off when the the preceding field,
INSPTR loses focus. Can anybody out there help me?

 

[Graham Mandeno]

Hi James

It's hard to say what the problem might be without seeing the code. Could
you post it here in a reply, please, and I (or someone else) can have a look
at it.

Do you have a value in your date field at the time your code is being
called?
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,
I must be a moron because I can't get this to work. I keep getting a
"missing operator" error with the "RecordDate=" part of the statement. I've
about given up on this.
I have the following columns in my table "ID", "RECORDSEQUENCE",
"RECORDDATE" and "REPORT DATE" in a table called "HAZARDS1". I've set up the
"REPORT DATE" to be automatically inputed into the "REPORT DATE" field (in
mm/dd/yy format) whenever the "ADD" button is depressed. It looks like
option 3 is the way I want to go. What in the heck am I'm doing wrong.
James

Hi James

The expression to calculate the next number will only work in VBA code -
not, say, it the DefaultValue property of the table field. The main
impediment to calculating it at that point is that you first need to know
the date value, in order to look up the current maximum sequence number.

I suggest you ascertain the next sequence value either:
1) when the record is saved (Form_AfterInsert), or
2) when the user starts to enter the new record (Form_BeforeInsert), or
3) when the date value is entered (RecordDate_AfterUpdate)

Where you do it depends on your requirements. Option (1) is best, unless
your user needs to see the sequence number *before* saving the record. If
this is necessary then use (2) if the date can only get a default value, or
(3) if the user enters the date. If both (there is a default value which
can be overridden) then use both places.

You say you have an "Add" button. That might be an appropriate time to get
the new sequence number - provided, of course, that the date is known at
that point. You can't get the next number for a given date if you don't yet
know the date!

Note that you also cannot calculate the "IDString" until both the date *and*
the sequence number have been ascertained.

If you have a multi-user environment, then lengthening the delay between
calculating the sequence number and saving the record will increase the
possibility that someone else will get in and try to nab the same number.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,
Judging by what I'm seeing this should work, but I'm havin trouble resolving
some placement issues. The "iNextNum" calculator; where do I put

this?
I've

tried placing this in the DefaultValue Criteria in my table and it

gives
me

errors. I've tried placing it as a calculated field in a Query and it's
giving me problems there. What am I doing wrong. The "IDString"
calculation; I would like to keep my form the same so how do I get all of
these new inputs to show up in the "ID" field on my form when I hit

the
Add

button? I've got a copy of "Running Microsoft Access 2000" and it's

been
a

big help, but this one is a little beyond me.
Thanks for the Help so far and I hope you can get me past this.
James

:

Hi James

I suggest you store two fields:
RecordDate (long)
RecordSequence (integer)

Store the date in the RecordDate field (I suggest datatype "long" instead of
"date/time" to ensure that it can only be a date part with no time) and
store the NNNN part in the RecordSequence field. Make the two fields
together the primary (or a unique secondary) composite key to ensure no
duplicates.

If you typically enter records on the date to which they apply, then

set
the

DefaultValue for RecordDate to "=Date()". Storing the date may have other
spin-offs, such as the ability to easily retrieve all records from a
particular month.

Now, the next sequence number for a given date may be ascertained as
follows:

iNextNum = Nz( DMax( "RecordSequence", "TableName", _
"RecordDate=" & Format( NewDateValue, "\#mm\dd\yyyy\#" ) ), 0) + 1

In plain English, find the maximum sequence number for the given

date
(and

if there ain't one, use zero) and then add one to it.

Now, the ID number you want in the form YYDDDNNNN can easily be generated on
the fly in a calculated field in a query, form or report:
IDString = Format( [RecordDate], "yy" ) _
& Format( DatePart ( "y", [RecordDate] ), "000" ) _
& Format ( [RecordSequence], "0000" )

This also means that if in the future you need to exceed the limit

of
9999

records a day, all you need do is add another "0" to the format

string
and

the job is done.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am responsible for keeping track of Safety discrepacies on board my
ship.
I have a database to do so and I'm trying to automate some of the features
to
make it easier. I would like to automate the ID feature using a
Year-Julian
Date-auto increment format. Year in NN format, Julian Date in NNN format,
Auto Increment in NNNN format (not to exceed 9999 and reset each new
julian
date). I would like this feature to key off when the the

preceding
field,

INSPTR loses focus. Can anybody out there help me?

 

[JAMES FENNEL]

Hey Boss,

This is what I've got. My Form only contains the fields "REPORT DATE" and
"ID"
"REPORT DATE" is automatically filled out when the "ADD" button is depressed.

Private Sub REPORT_DATE_AfterUpdate()
iNextNum = Nz(DMax("RECORDSEQUENCE", "HAZARDS1", "RECORDDATE=" &
Format(NEWDATEVALUE, "\#MM\DD\YY\#")), 0) + 1
IDString = Format([RECORDDATE], "YY") & Format(DatePart("Y", [RECORDDATE]),
"000") & Format([RECORDSEQUENCE], "0000")

End Sub

Hi James

It's hard to say what the problem might be without seeing the code. Could
you post it here in a reply, please, and I (or someone else) can have a look
at it.

Do you have a value in your date field at the time your code is being
called?
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,
I must be a moron because I can't get this to work. I keep getting a
"missing operator" error with the "RecordDate=" part of the statement. I've
about given up on this.
I have the following columns in my table "ID", "RECORDSEQUENCE",
"RECORDDATE" and "REPORT DATE" in a table called "HAZARDS1". I've set up the
"REPORT DATE" to be automatically inputed into the "REPORT DATE" field (in
mm/dd/yy format) whenever the "ADD" button is depressed. It looks like
option 3 is the way I want to go. What in the heck am I'm doing wrong.
James

Hi James

The expression to calculate the next number will only work in VBA code -
not, say, it the DefaultValue property of the table field. The main
impediment to calculating it at that point is that you first need to know
the date value, in order to look up the current maximum sequence number.

I suggest you ascertain the next sequence value either:
1) when the record is saved (Form_AfterInsert), or
2) when the user starts to enter the new record (Form_BeforeInsert), or
3) when the date value is entered (RecordDate_AfterUpdate)

Where you do it depends on your requirements. Option (1) is best, unless
your user needs to see the sequence number *before* saving the record. If
this is necessary then use (2) if the date can only get a default value, or
(3) if the user enters the date. If both (there is a default value which
can be overridden) then use both places.

You say you have an "Add" button. That might be an appropriate time to get
the new sequence number - provided, of course, that the date is known at
that point. You can't get the next number for a given date if you don't yet
know the date!

Note that you also cannot calculate the "IDString" until both the date *and*
the sequence number have been ascertained.

If you have a multi-user environment, then lengthening the delay between
calculating the sequence number and saving the record will increase the
possibility that someone else will get in and try to nab the same number.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,
Judging by what I'm seeing this should work, but I'm havin trouble
resolving
some placement issues. The "iNextNum" calculator; where do I put this?
I've
tried placing this in the DefaultValue Criteria in my table and it gives
me
errors. I've tried placing it as a calculated field in a Query and it's
giving me problems there. What am I doing wrong. The "IDString"
calculation; I would like to keep my form the same so how do I get all of
these new inputs to show up in the "ID" field on my form when I hit the
Add
button? I've got a copy of "Running Microsoft Access 2000" and it's been
a
big help, but this one is a little beyond me.
Thanks for the Help so far and I hope you can get me past this.
James

:

Hi James

I suggest you store two fields:
RecordDate (long)
RecordSequence (integer)

Store the date in the RecordDate field (I suggest datatype "long"
instead of
"date/time" to ensure that it can only be a date part with no time) and
store the NNNN part in the RecordSequence field. Make the two fields
together the primary (or a unique secondary) composite key to ensure no
duplicates.

If you typically enter records on the date to which they apply, then set
the
DefaultValue for RecordDate to "=Date()". Storing the date may have
other
spin-offs, such as the ability to easily retrieve all records from a
particular month.

Now, the next sequence number for a given date may be ascertained as
follows:

iNextNum = Nz( DMax( "RecordSequence", "TableName", _
"RecordDate=" & Format( NewDateValue, "\#mm\dd\yyyy\#" ) ), 0) + 1

In plain English, find the maximum sequence number for the given date
(and
if there ain't one, use zero) and then add one to it.

Now, the ID number you want in the form YYDDDNNNN can easily be
generated on
the fly in a calculated field in a query, form or report:
IDString = Format( [RecordDate], "yy" ) _
& Format( DatePart ( "y", [RecordDate] ), "000" ) _
& Format ( [RecordSequence], "0000" )

This also means that if in the future you need to exceed the limit of
9999
records a day, all you need do is add another "0" to the format string
and
the job is done.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am responsible for keeping track of Safety discrepacies on board my
ship.
I have a database to do so and I'm trying to automate some of the
features
to
make it easier. I would like to automate the ID feature using a
Year-Julian
Date-auto increment format. Year in NN format, Julian Date in NNN
format,
Auto Increment in NNNN format (not to exceed 9999 and reset each new
julian
date). I would like this feature to key off when the the preceding
field,
INSPTR loses focus. Can anybody out there help me?

 

[Graham Mandeno]

Hi James

Well, it seems that you are referring to a nonexistent variable called
"NEWDATEVALUE", in the third argument for the DMax:
"RECORDDATE=" & Format(NEWDATEVALUE, "\#MM\DD\YY\#")

I suspect this is because I used "NewDateValue" in my example to show you
where to put the name of the date field textbox.

Assuming the textbox is named "RECORD DATE" with a space (note: it is
generally considered a Very Bad Idea to include spaces in the names of
fields/controls/forms/tables/queries... in fact, any object), it should read
like this:
"RECORDDATE=" & Format([RECORD DATE], "\#MM\DD\YY\#")

This also assumes that the field is called RECORDDATE (without a space) in
the "HAZARDS1" table, but "RECORD DATE" (with a space) in your other table.

I suggest you adopt a naming convention that eliminates spaces from all
names, and that you stop SHOUTING at all your database objects - you must
fiond it very tiring to read.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hey Boss,

This is what I've got. My Form only contains the fields "REPORT DATE" and
"ID"
"REPORT DATE" is automatically filled out when the "ADD" button is depressed.

Private Sub REPORT_DATE_AfterUpdate()
iNextNum = Nz(DMax("RECORDSEQUENCE", "HAZARDS1", "RECORDDATE=" &
Format(NEWDATEVALUE, "\#MM\DD\YY\#")), 0) + 1
IDString = Format([RECORDDATE], "YY") & Format(DatePart("Y", [RECORDDATE]),
"000") & Format([RECORDSEQUENCE], "0000")

End Sub

Hi James

It's hard to say what the problem might be without seeing the code. Could
you post it here in a reply, please, and I (or someone else) can have a look
at it.

Do you have a value in your date field at the time your code is being
called?
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,
I must be a moron because I can't get this to work. I keep getting a
"missing operator" error with the "RecordDate=" part of the statement. I've
about given up on this.
I have the following columns in my table "ID", "RECORDSEQUENCE",
"RECORDDATE" and "REPORT DATE" in a table called "HAZARDS1". I've set

up
the

"REPORT DATE" to be automatically inputed into the "REPORT DATE" field (in
mm/dd/yy format) whenever the "ADD" button is depressed. It looks like
option 3 is the way I want to go. What in the heck am I'm doing wrong.
James
:

Hi James

The expression to calculate the next number will only work in VBA code -
not, say, it the DefaultValue property of the table field. The main
impediment to calculating it at that point is that you first need to know
the date value, in order to look up the current maximum sequence number.

I suggest you ascertain the next sequence value either:
1) when the record is saved (Form_AfterInsert), or
2) when the user starts to enter the new record (Form_BeforeInsert), or
3) when the date value is entered (RecordDate_AfterUpdate)

Where you do it depends on your requirements. Option (1) is best, unless
your user needs to see the sequence number *before* saving the

record.
If

this is necessary then use (2) if the date can only get a default

value,
or

(3) if the user enters the date. If both (there is a default value which
can be overridden) then use both places.

You say you have an "Add" button. That might be an appropriate time

to
get

the new sequence number - provided, of course, that the date is known at
that point. You can't get the next number for a given date if you

don't
yet

know the date!

Note that you also cannot calculate the "IDString" until both the

date
*and*

the sequence number have been ascertained.

If you have a multi-user environment, then lengthening the delay between
calculating the sequence number and saving the record will increase the
possibility that someone else will get in and try to nab the same number.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,
Judging by what I'm seeing this should work, but I'm havin trouble
resolving
some placement issues. The "iNextNum" calculator; where do I put this?
I've
tried placing this in the DefaultValue Criteria in my table and it gives
me
errors. I've tried placing it as a calculated field in a Query

and
it's

giving me problems there. What am I doing wrong. The "IDString"
calculation; I would like to keep my form the same so how do I get

all
of

these new inputs to show up in the "ID" field on my form when I

hit
the

Add
button? I've got a copy of "Running Microsoft Access 2000" and

it's
been

a
big help, but this one is a little beyond me.
Thanks for the Help so far and I hope you can get me past this.
James

:

Hi James

I suggest you store two fields:
RecordDate (long)
RecordSequence (integer)

Store the date in the RecordDate field (I suggest datatype "long"
instead of
"date/time" to ensure that it can only be a date part with no

time)
and

store the NNNN part in the RecordSequence field. Make the two fields
together the primary (or a unique secondary) composite key to

ensure
no

duplicates.

If you typically enter records on the date to which they apply,

then
set

the
DefaultValue for RecordDate to "=Date()". Storing the date may have
other
spin-offs, such as the ability to easily retrieve all records from a
particular month.

Now, the next sequence number for a given date may be ascertained as
follows:

iNextNum = Nz( DMax( "RecordSequence", "TableName", _
"RecordDate=" & Format( NewDateValue, "\#mm\dd\yyyy\#" ) ),

0) +
1

In plain English, find the maximum sequence number for the given date
(and
if there ain't one, use zero) and then add one to it.

Now, the ID number you want in the form YYDDDNNNN can easily be
generated on
the fly in a calculated field in a query, form or report:
IDString = Format( [RecordDate], "yy" ) _
& Format( DatePart ( "y", [RecordDate] ), "000" ) _
& Format ( [RecordSequence], "0000" )

This also means that if in the future you need to exceed the

limit
of

9999
records a day, all you need do is add another "0" to the format string
and
the job is done.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am responsible for keeping track of Safety discrepacies on

board
my

ship.
I have a database to do so and I'm trying to automate some of the
features
to
make it easier. I would like to automate the ID feature using a
Year-Julian
Date-auto increment format. Year in NN format, Julian Date in NNN
format,
Auto Increment in NNNN format (not to exceed 9999 and reset

each
new

julian
date). I would like this feature to key off when the the preceding
field,
INSPTR loses focus. Can anybody out there help me?

 

[Jess]

I am very confused on how to auto increment numbers. I am ok with the basics
of Excel but I am trying to do this project for work and no one here is able
to figure out how to auto increment numbers into cells. I have been
researching on the web and also through Excel books. If you could be of any
help to me in this process please email me. Thank you for your time.

Jess

Graham,
I must be a moron because I can't get this to work. I keep getting a
"missing operator" error with the "RecordDate=" part of the statement. I've
about given up on this.
I have the following columns in my table "ID", "RECORDSEQUENCE",
"RECORDDATE" and "REPORT DATE" in a table called "HAZARDS1". I've set up the
"REPORT DATE" to be automatically inputed into the "REPORT DATE" field (in
mm/dd/yy format) whenever the "ADD" button is depressed. It looks like
option 3 is the way I want to go. What in the heck am I'm doing wrong.
James

Hi James

The expression to calculate the next number will only work in VBA code -
not, say, it the DefaultValue property of the table field. The main
impediment to calculating it at that point is that you first need to know
the date value, in order to look up the current maximum sequence number.

I suggest you ascertain the next sequence value either:
1) when the record is saved (Form_AfterInsert), or
2) when the user starts to enter the new record (Form_BeforeInsert), or
3) when the date value is entered (RecordDate_AfterUpdate)

Where you do it depends on your requirements. Option (1) is best, unless
your user needs to see the sequence number *before* saving the record. If
this is necessary then use (2) if the date can only get a default value, or
(3) if the user enters the date. If both (there is a default value which
can be overridden) then use both places.

You say you have an "Add" button. That might be an appropriate time to get
the new sequence number - provided, of course, that the date is known at
that point. You can't get the next number for a given date if you don't yet
know the date!

Note that you also cannot calculate the "IDString" until both the date *and*
the sequence number have been ascertained.

If you have a multi-user environment, then lengthening the delay between
calculating the sequence number and saving the record will increase the
possibility that someone else will get in and try to nab the same number.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,
Judging by what I'm seeing this should work, but I'm havin trouble resolving
some placement issues. The "iNextNum" calculator; where do I put this? I've
tried placing this in the DefaultValue Criteria in my table and it gives me
errors. I've tried placing it as a calculated field in a Query and it's
giving me problems there. What am I doing wrong. The "IDString"
calculation; I would like to keep my form the same so how do I get all of
these new inputs to show up in the "ID" field on my form when I hit the Add
button? I've got a copy of "Running Microsoft Access 2000" and it's been a
big help, but this one is a little beyond me.
Thanks for the Help so far and I hope you can get me past this.
James

:

Hi James

I suggest you store two fields:
RecordDate (long)
RecordSequence (integer)

Store the date in the RecordDate field (I suggest datatype "long" instead of
"date/time" to ensure that it can only be a date part with no time) and
store the NNNN part in the RecordSequence field. Make the two fields
together the primary (or a unique secondary) composite key to ensure no
duplicates.

If you typically enter records on the date to which they apply, then set the
DefaultValue for RecordDate to "=Date()". Storing the date may have other
spin-offs, such as the ability to easily retrieve all records from a
particular month.

Now, the next sequence number for a given date may be ascertained as
follows:

iNextNum = Nz( DMax( "RecordSequence", "TableName", _
"RecordDate=" & Format( NewDateValue, "\#mm\dd\yyyy\#" ) ), 0) + 1

In plain English, find the maximum sequence number for the given date (and
if there ain't one, use zero) and then add one to it.

Now, the ID number you want in the form YYDDDNNNN can easily be generated on
the fly in a calculated field in a query, form or report:
IDString = Format( [RecordDate], "yy" ) _
& Format( DatePart ( "y", [RecordDate] ), "000" ) _
& Format ( [RecordSequence], "0000" )

This also means that if in the future you need to exceed the limit of 9999
records a day, all you need do is add another "0" to the format string and
the job is done.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am responsible for keeping track of Safety discrepacies on board my
ship.
I have a database to do so and I'm trying to automate some of the features
to
make it easier. I would like to automate the ID feature using a
Year-Julian
Date-auto increment format. Year in NN format, Julian Date in NNN format,
Auto Increment in NNNN format (not to exceed 9999 and reset each new
julian
date). I would like this feature to key off when the the preceding field,
INSPTR loses focus. Can anybody out there help me?

 

[Rick B]

Excel?

This is an ACCESS newsgroup, not EXCEL.

I am very confused on how to auto increment numbers. I am ok with the basics
of Excel but I am trying to do this project for work and no one here is able
to figure out how to auto increment numbers into cells. I have been
researching on the web and also through Excel books. If you could be of any
help to me in this process please email me. Thank you for your time.

Jess

Graham,
I must be a moron because I can't get this to work. I keep getting a
"missing operator" error with the "RecordDate=" part of the statement. I've
about given up on this.
I have the following columns in my table "ID", "RECORDSEQUENCE",
"RECORDDATE" and "REPORT DATE" in a table called "HAZARDS1". I've set up the
"REPORT DATE" to be automatically inputed into the "REPORT DATE" field (in
mm/dd/yy format) whenever the "ADD" button is depressed. It looks like
option 3 is the way I want to go. What in the heck am I'm doing wrong.
James

Hi James

The expression to calculate the next number will only work in VBA code -
not, say, it the DefaultValue property of the table field. The main
impediment to calculating it at that point is that you first need to know
the date value, in order to look up the current maximum sequence number.

I suggest you ascertain the next sequence value either:
1) when the record is saved (Form_AfterInsert), or
2) when the user starts to enter the new record (Form_BeforeInsert), or
3) when the date value is entered (RecordDate_AfterUpdate)

Where you do it depends on your requirements. Option (1) is best, unless
your user needs to see the sequence number *before* saving the record. If
this is necessary then use (2) if the date can only get a default value, or
(3) if the user enters the date. If both (there is a default value which
can be overridden) then use both places.

You say you have an "Add" button. That might be an appropriate time to get
the new sequence number - provided, of course, that the date is known at
that point. You can't get the next number for a given date if you don't yet
know the date!

Note that you also cannot calculate the "IDString" until both the date *and*
the sequence number have been ascertained.

If you have a multi-user environment, then lengthening the delay between
calculating the sequence number and saving the record will increase the
possibility that someone else will get in and try to nab the same number.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,
Judging by what I'm seeing this should work, but I'm havin trouble
resolving
some placement issues. The "iNextNum" calculator; where do I put this?
I've
tried placing this in the DefaultValue Criteria in my table and it gives
me
errors. I've tried placing it as a calculated field in a Query and it's
giving me problems there. What am I doing wrong. The "IDString"
calculation; I would like to keep my form the same so how do I get all of
these new inputs to show up in the "ID" field on my form when I hit the
Add
button? I've got a copy of "Running Microsoft Access 2000" and it's been
a
big help, but this one is a little beyond me.
Thanks for the Help so far and I hope you can get me past this.
James

:

Hi James

I suggest you store two fields:
RecordDate (long)
RecordSequence (integer)

Store the date in the RecordDate field (I suggest datatype "long"
instead of
"date/time" to ensure that it can only be a date part with no time) and
store the NNNN part in the RecordSequence field. Make the two fields
together the primary (or a unique secondary) composite key to ensure no
duplicates.

If you typically enter records on the date to which they apply, then set
the
DefaultValue for RecordDate to "=Date()". Storing the date may have
other
spin-offs, such as the ability to easily retrieve all records from a
particular month.

Now, the next sequence number for a given date may be ascertained as
follows:

iNextNum = Nz( DMax( "RecordSequence", "TableName", _
"RecordDate=" & Format( NewDateValue, "\#mm\dd\yyyy\#" ) ), 0) + 1

In plain English, find the maximum sequence number for the given date
(and
if there ain't one, use zero) and then add one to it.

Now, the ID number you want in the form YYDDDNNNN can easily be
generated on
the fly in a calculated field in a query, form or report:
IDString = Format( [RecordDate], "yy" ) _
& Format( DatePart ( "y", [RecordDate] ), "000" ) _
& Format ( [RecordSequence], "0000" )

This also means that if in the future you need to exceed the limit of
9999
records a day, all you need do is add another "0" to the format string
and
the job is done.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am responsible for keeping track of Safety discrepacies on board my
ship.
I have a database to do so and I'm trying to automate some of the
features
to
make it easier. I would like to automate the ID feature using a
Year-Julian
Date-auto increment format. Year in NN format, Julian Date in NNN
format,
Auto Increment in NNNN format (not to exceed 9999 and reset each new
julian
date). I would like this feature to key off when the the preceding
field,
INSPTR loses focus. Can anybody out there help me?