Hi, im using access and dreamweaver with ASP (formats the code for you)
Im a newbie, but im really needing help please.

Basically i need an order number system that includes the year and a sequential number that starts at 1 again in the next year so...

0901
0902 .....
09101
09102 and so on

1001 for the next year
1002
10101
10102 and so on

If anyone could send me the database or what was done, as I have searched hundreds of pages looking for help, but its over my head !

Thanks so much!
will reply with my email if someone can help.

You could use something like;


If IsNull(Me.OrderNum) Then
Me.OrderNum = DMax("[ordernum]", "TBL_order") + 1
End If

To increment your order number, and then in an unbound field use;


=Format(Year([orderdate]),"YY") & Format([ordernum],"0#")

To display your order number.

Have a look at the attached for an example.

Thanks for your reply ...
I am still absolutely puzzled :P

I have uploaded what ive done ...
Now i have a form that when filled in with the Patient name, it is sent to that database, and u can c that im using the ID autonumber system.

I basically need to do another field or update that ID field, to the
number system with the year as i explained in my previous post.

I have also attached the pic of the uncomplete form, just to show you where that number would go.

Any help is greatly appreciated.

Conventional wisdom states that calculated fields should not be stored in an RDB. For a fuller discussion on this subject see this link (http://allenbrowne.com/casu-14.html).

umm ... so there is nothing I can do ?

Just pull the information together when ever it needs to be displayed on a form or report.

I downloaded this off another forum some time ago. The paper is yellow its so old but it works great.
I don't recall whose code it is but it works like a charm for year and sequence. If you recognize it please claim it.

(quote)

Create two fields in your table:
TheYear - long
TheSequence – long

Then enter this code in your form's BeforeInsert
Me.TheYear = CLng(DatePart("YYYY", Date()))
Me.TheSequence = Nz(DMax("[TheSequence]", "YourTableName", "[TheYear] = "&Me.TheYear),0) +1

That’s all the code you need. When a new year starts the DMax function returns a Null, which the Nz function changes to a 0. Then it just adds 1 to get the next value.

Why two fields? You will spend a lot of time writing code to continuously tear them apart. It’s much easier to store and group them when they're stored separately.

It’s also easier to format them in a query.

(end quote)

What happens is when you make your first keystroke on the form the year and sequence are entered automatically. So if your last entry was
TheYear - 2006 TheSequence – 14
the next will be
TheYear - 2006 TheSequence – 15
assuming its still 2006. If this is the first entry in 2007 you will get:
TheYear – 2007 TheSequence – 1

Be sure to set TheYear and TheSequence text boxes on your forms to Enabled-NO Locked-YES. This will prevent people from over riding the pre-set numbers.