increment a text!?!?!

i have a field in a table which is of text type. It stores
values like
0401/01
0401/02
0401/03
..
..
0402/01
0402/02
The logic behind is that the first two indicate the year,
the second two indicate the month and the last two
indicate the no of Invoice raised in a particular month.
Now i want to have this particular field in my forms to be
automatically incremented each time i add a record. Not
only that but if the month changes then the monthnumber
should also change and cause the numbering to start from
the begining.
There is again a problem. We raise invoices not directly
allways. but sometimes an estimate is raised and if
approved the corresponding invoice is raised. This means
that either the invoices entry can be a new entry
(addition of a record - insert event) or it can be in an
existing record. I have already made an unbound form that
only has a text box that displays the last maximum number
raised. how can this be done. I am also not well versed in
VBA & codings etc. CAN this be DONE????
Also my form should update the table.
What are the possibilities??
regards
manish

Manish,

I guess the normal way to do this would be to not store any such thing
as 0401/01 in a field in a table. This is not "atomic" data, and also I
presume some of it is redundant, in that I imagine you also have an
invoice date field as well? So, I would just have a number field for
the invoice number, and then whenever needed for user/display purposes
on your forms and reports, use a calculated field in the query or on the
form/report, such as...
=Format([InvoiceDate],"yymm\/") & Format([InvoiceNumber],"00")
Then, when adding a new record, or entering the invoice number for an
existing estimate record, you can set the invoice number to the
equivalent of...
Nz(DMax("[InvoiceNumber]","YourTable","Format([InvoiceDate],'yymm')='" &
Format(Me![InvoiceDate],"yymm") & "'"),0)+1

--
Steve Schapel, Microsoft Access MVP

manish wrote:
i have a field in a table which is of text type. It stores
values like
0401/01
0401/02
0401/03
.
.
0402/01
0402/02
The logic behind is that the first two indicate the year,
the second two indicate the month and the last two
indicate the no of Invoice raised in a particular month.
Now i want to have this particular field in my forms to be
automatically incremented each time i add a record. Not
only that but if the month changes then the monthnumber
should also change and cause the numbering to start from
the begining.
There is again a problem. We raise invoices not directly
allways. but sometimes an estimate is raised and if
approved the corresponding invoice is raised. This means
that either the invoices entry can be a new entry
(addition of a record - insert event) or it can be in an
existing record. I have already made an unbound form that
only has a text box that displays the last maximum number
raised. how can this be done. I am also not well versed in
VBA & codings etc. CAN this be DONE????
Also my form should update the table.
What are the possibilities??
regards
manish