Date and Autonumber

[Sondra]

I put this in as the Control Source of the textbox on a
form and on a report...

=Format(Date(),"yy") & "CC-" & Format([AN_Field],"000")

I'm a very novice user so I don't write a great deal of
code. I realize that the format will make all records
change to 04 for 2004; however I will have to create a
blank empty database for 2004 as I need to use the 2003
records.

It keeps locking access up and makes me restart. Does
anyone have any suggestions???

**********
I have created a table that uses an autonumber for the
primary key which will be used in about 90% of my forms,
queries and reports. The format of the autonumber is
000. However I need to create the reports to show a
different format. I want it to look like 03CC-001, 03CC-
002, 03CC-003, etc.

I have entered the format at "03CC-"000; however I will
have to go in and modify everything for next year as "04CC-
"000.

Is there a format I can enter that will automatically put
the year in for me. I have tried entering yy"CC-"000 but
it comes up as 1900CC-001, 1900CC-002, etc. I then tried
y"CC-"000 and still couldn't make the current year work.
Does anyone have any suggestions? This is a big concern
as I create several databases for our region that require
date but also consecutive numbers.

Thanks.

 

[Tim Ferguson]

I put this in as the Control Source of the textbox on a
form and on a report...

=Format(Date(),"yy") & "CC-" & Format([AN_Field],"000")

Well, this is still a dumb thing to do: just looking at the records next
year will make them look like 04cc....

I realize that the format will make all records
change to 04 for 2004; however I will have to create a
blank empty database for 2004 as I need to use the 2003
records.

There is nothing you have said so far that indicates you need to fragment
your data over several databases: to get 03cc001, 03cc002... 04cc001,
04cc002 etc is very simple and there are so many examples around that you
don't have to understand the code to get it working.

In the meantime, what about the "CC": is that ever likely to change?

And once more, you must be very clear about the fate of used numbers: if
you delete 03CC012 when you are up to 03CC620, are you going to re-use the
twelve or not? What about cancelling a half-entered record -- at what point
do you acknowledge that the number is used and to be re-issued? None of
these are hard to do, but you have to know the answers yourself before
starting.



Finally, as a help to people who are reading and responding to your posts
here, please reply and follow up within the same thread.

All the best



Tim F

 

[Sondra]

-----Original Message-----

I put this in as the Control Source of the textbox on a
form and on a report...

=Format(Date(),"yy") & "CC-" & Format([AN_Field],"000")

Well, this is still a dumb thing to do: just looking at the records next
year will make them look like 04cc....

I realize that the format will make all records
change to 04 for 2004; however I will have to create a
blank empty database for 2004 as I need to use the 2003
records.

There is nothing you have said so far that indicates you need to fragment
your data over several databases: to get 03cc001, 03cc002... 04cc001,
04cc002 etc is very simple and there are so many examples around that you
don't have to understand the code to get it working.

In the meantime, what about the "CC": is that ever likely to change?

And once more, you must be very clear about the fate of used numbers: if
you delete 03CC012 when you are up to 03CC620, are you going to re-use the
twelve or not? What about cancelling a half-entered record -- at what point
do you acknowledge that the number is used and to be re- issued? None of
these are hard to do, but you have to know the answers yourself before
starting.



Finally, as a help to people who are reading and responding to your posts
here, please reply and follow up within the same thread.

All the best



Tim F

.

Thanks Tim:

1. Well the CC will never change it will be a constant
factor in the database.

2. The deletion of a number is clear. We would never re-
use a number that has already been issued.

3. Sorry for reposting, wasn't sure how to do it. I
understand now. I was just afraid no one would see my
message.

4. If the answers are so clear, I must be blind because I
am not getting it at all. Any guidance you might have
would be great.

Thanks.

S

 

[Steve Schapel]

Sondra,

PMFJI. It seems to me that the confusion is about the nature of this
data. You started off asking about the formatting of a control on a
report, relating it to the current year. A report is for the
presentation of existing data. Now it transpires that you wish the
"year" portion of your 03CC-1234 to relate to the year the record was
first recorded or created, rather than the year when it is printed.
This is an entirely different matter. This means you need to record
the DateCreated or the YearCreated with each record. Possible the
easiest way to do this would be to make a new field in the table,
Number data type, and set its Default Value on your data entry form to
Year(Date()). And then, on your forms or reports where you want your
composite number displayed, you would use the equivalent of...
=Right([YearCreated],2) & "CC-" & Format([AN_Field],"000")

- Steve Schapel, Microsoft Access MVP

 

[Tim Ferguson]

1. Well the CC will never change it will be a constant
factor in the database.

In that case, I thoroughly support your idea of coding this into the
formatting or controlsource on forms/ reports etc.

2. The deletion of a number is clear. We would never re-
use a number that has already been issued.

The reason I asked this came from your original post in the other thread,
when it sounded like you were using an autonumbr and expecting it to count
records too.

4. If the answers are so clear, I must be blind because I
am not getting it at all. Any guidance you might have
would be great.

Steve S has accurately summed up the advice you received in the other
thread as well: to keep the YearCreated as a field in the table and then
display it together with the "CC" and the serial number. First normal form
= keep all data in fields in the table: not in column names, or table
names, or buried in Format properties.

I obviously don't know much about your application, but as a general rule
you gain practically nothing and lose a huge amount of usability by
creating new database files for each year (or warehouse, or department,
etc). Unless there is an overriding reason, for example the size limit of
2GB, I would encourage you to keep the database together in one file. It
would be very easy to query on "WHERE YearCreated=2003" and so on, and then
it enables you to compare years, carry over records, maintain look up
tables and so on with a minimum of fuss.

Hope that helps


Tim F

 

[Steve Schapel]

Sondra,

Do you mean you can't add a new record at all? If so, can you please
post back with the SQL view of the query that the form is based on.
Or are you trying to enter/type into the textbox with the
concatenation expression? If so, no, you can't do this. The idea
should be that when you enter a new record, the Autonumber field will
automatically acquire a value, the YearEntered field will
automatically acquire a value by virtue of its Default Value as
previously discussed, and the unbound textbox with the concatenation
expression as its controlsource will automatically display the
composite data you require.

- Steve Schapel, Microsoft Access MVP

 

[Sondra]

I entered the information directly into the format
property of the field. I believe the I am trying to do
the concatenation expression. I'm working on it. If you
have any suggestions on how I might make it work smoothly
please let me know. I am currently investigating the
concept that Tim Ferguson offered about creating my own
autonumber; however I'm real novice and not sure if I can
write that code.

Thanks for all your help.

S

 

[Sondra.]

I've looked and found a few things, but another issue
would be to get the number to start over again. Can you
suggest the correct code to enter so that when the year
2004 takes effect that the numbering will begin at 1 again.

Thanks.

 

[Sondra]

="REC-" & right(DMax("FOO", "FOOTable"), _
Len(DMax("FOO", "FOOTable")) - _
InStr(1, DMax("FOO", "FOOTable"), "-")) + 1
this is the string that I found. Can you help me
understand this and work it into mine.

 

[Steve Schapel]

Sondra,

If you use a single view form to enter your new records, there is no
need for any code at all. Put a new Number data type field into your
table, let's call it ProjectNo, and use an expression similar to the
following as its Default Value property setting on the form...
=Nz(DMax("[ProjectNo]","YourTable","[YearCreated]=Year(Date())"),0)+1

If you are using a continuous view form to enter your new records, you
will need to use simple code to assign the value by using the form's
Before Insert event, something like...
Private Sub Form_BeforeInsert(Cancel As Integer)
Me.ProjectNo = _
Nz(DMax("[ProjectNo]","YourTable","[YearCreated]=Year(Date())"),0)+1
End Sub

- Steve Schapel, Microsoft Access MVP

 

[Sondra]

Could you please explain Single view form and Continuous
View Form???

And if I read you right then what I originally had set up
as the AN field should be deleted and a new number data
type field put in its place each year???

-----Original Message-----
Sondra,

If you use a single view form to enter your new records, there is no
need for any code at all. Put a new Number data type field into your
table, let's call it ProjectNo, and use an expression similar to the
following as its Default Value property setting on the form...

=Nz(DMax("[ProjectNo]","YourTable","[YearCreated]=Year

(Date())"),0)+1

If you are using a continuous view form to enter your new records, you
will need to use simple code to assign the value by using the form's
Before Insert event, something like...
Private Sub Form_BeforeInsert(Cancel As Integer)
Me.ProjectNo = _
Nz(DMax("[ProjectNo]","YourTable","[YearCreated]=Year(Date ())"),0)+1
End Sub

- Steve Schapel, Microsoft Access MVP

I've looked and found a few things, but another issue
would be to get the number to start over again. Can you
suggest the correct code to enter so that when the year
2004 takes effect that the numbering will begin at 1 again.

Thanks.

.

 

[Tim Ferguson]

="REC-" & right(DMax("FOO", "FOOTable"), _
Len(DMax("FOO", "FOOTable")) - _
InStr(1, DMax("FOO", "FOOTable"), "-")) + 1
this is the string that I found. Can you help me
understand this and work it into mine.

Actually, I think that Steve's answer is better than this one because it's
simpler. This bit of code just shows what a bad idea it is to try to cram
too much into one field!

If you are interested (but don't lose any sleep if this doesn't make any
sense), this refers to a field that looks like "REC-1", "REC-2", "REC-3",
"REC-10", "REC-11", etc. Going backwards, the Instr() function finds the
hyphen; the Len()-Instr() finds the length of the actual numeric part; the
Right(..., Len()-Instr()) extracts the number. The DMax finds the highest
existing number, and there is a +1 at the end to find the next one to use.

Steve's answer is better

Nz(DMax("[ProjectNo]","YourTable","[YearCreated]=Year(Date())"),0)+1

because it uses two separate fields. The DMax() command looks up the
largest ProjectNo field value in the YourTable field, but only looks in
rows where the YearCreated field is equal to this year (i.e. Year(Date())
returns 2003 today, and 2004 next year, etc). If there are no records for
the year in question (every year there's a first one!) then DMax returns
Null, so the Nz catches that and turns it into a zero. The +1 at the end
makes the new number One for the first record or Last-One-Plus-One for all
the subsequent ones.

As he suggests, you can put this in the DefaultValue of the ProjectNo text
box on the form (so it fills in every time there's a record without a
ProjectNo) or in the BeforeInsert event code (so that Access will call it
whenever you try to save a new record). And yes, this new ProjectNo field
can replace the autonumber field you had; and you can set the Primary Key
to both YearCreated and ProjectNo fields together [see below].

Single-forms and Continuous-forms: you can create a form to look at one
record at a time, which is the usual way to do it. There is a setting in
the properties, though, to set it to Continuous forms, which produces a
vertical ribbon-like form with all the records one after the other, and is
mostly used to provide a data-grid like interface like a datasheet but with
all the benefits of forms. Steve seems to be saying that the DefaultValue
doesn't work properly with this, but I'm not quite sure why as I have not
particularly tried it.

All the best

Oh, by the way [compound primary keys] -- Ctrl-click the two fields in the
table design window, so they are both selected, and then click the yellow
key PK toolbar button. That way, you can have lots of records with the same
ProjectNo, and lots with the same YearCreated, but any combination of
YearCreated and ProjectNo must be unique -- which is what you want.


Hope that helps: sorry it's a bit long!


Tim F

 

[Steve Schapel]

Sondra,

Could you please explain Single view form and Continuous
View Form???

Go to the Default View property of the form, put your cursor in there,
and press the F1 key.

And if I read you right then what I originally had set up
as the AN field should be deleted and a new number data
type field put in its place each year???

No, you don't need a new field for each year. As mentioned before,
you need a YearCreated field, and I have already mentioned making this
default to the current year at the point of data entry. And you need
a field for your ID number, and the suggestions I made before about
using the DMax expression to enter this data should ensure that the
numbering of this field will restart at 1 for each year. As for
whether this should replace the existing Autonumber field could be a
matter for debate, if one was so inclined to debate such things. I
expect Tim would say to remove the Autonumber field, and make the new
ID field plus the YearCreated field a composite Primary Key. For
myself, I would retain the Autonumber field as the Primary Key, and
use this as the basis of any one-to-many relationships between this
table and any others an applicable. And then I would make a Unique
Index on the combination of ID and YearCreated. In anticipation of
your next question, look for "index" in Access Help, and find and read
the topic "Prevent duplicate values from being entered into a field".

- Steve Schapel, Microsoft Access MVP