Reference Number

[horseman]

I have a database which I have a reference number field. Is there any way
that I can make the number start at 1 at the begging of each year.

 

[Jeff Boyce]

We're not there. We can't see what you've done already. Your definition of
a "reference number field" may be different that ours.

What data type is the field? What does your table hold? Which version of
Access are you using?

--
More info, please ...

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[horseman]

The database is to record complaints about different department, The database
is access 97. The fields I have are Reference Number, first name, surname,
address, complaint field. My reference number field is a number which starts
at 1, and the next record would be 2, 3, etc and at the begging of each year
I would like it to start at 1

 

[Jeff Boyce]

If I take your description literally, after one year, you'd be writing
records with matching Reference Numbers (each year starts @ 1). How would
you tell a complaint from 2005 from a complaint from 2006?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[horseman]

If I started at the begging of the year with 1 and put in the year eg: 1/06,
2/06, etc and then next year for it to start at 1 again eg: 1/07, 2/07, 3/07
etc

 

[Jeff Boyce]

A suggestion... don't combine more than one fact in one field. Your
intention of putting both a "reference number" and a year in the same field
will cause both you and Access heartburn. Instead, use one field per
"fact" -- one field for a reference number, one field for the "year". By
the way, don't call that second field "Year" -- this is a reserved word in
Access and you will only confuse things. Instead, name the field
"ProgramYear" or some such...

Take a look at mvps.org/access (or search Google) for "custom autonumbers".
This is a misnomer, as they are not actually "autonumbers", but the concept
is the same. There's code available that determines the "year" and the
maximum "sequence number" (your reference number I suspect), then adds 1.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/