As I said, you should have two separate fields (although it's not necessary
to have Year field if there's some other date field already there that you
can use as the basis for the year information). Let's assume that you do
have two fields, though. I'll call them YearField and IncrementalNumber.
(Note that you should NOT name the field Year, as that's a reserved word.
For a comprehensive list of field names to avoid, see what Allen Browne has
at
http://www.allenbrowne.com/AppIssueBadWord.html )
In your form's BeforeUpdate event, use code like:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
Me.IncrementalNumber = _
Nz(DMax("[IncrementalNumber]", "[MyTable]", _
"[YearField] = " & Me.YearField), 0) + 1
End If
End Sub
(use your actual table name instead of MyTable)
Now, create a query that returns all the rows in MyTable. Add a computed
field to that query that concatenates the two fields for display purposes:
Right(CStr([YearField]), 2) & "-" & Format([IncrementalNumber], "000")
Use that query wherever you would otherwise have used the table.
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
Bill said:
Doug,
You are correct the 08 is for the year and I dont want to be changing it
each year. Also the numbers start over year after year. So we have a
08-001
and next year we will start with 09-001.
Not sure I'm how to accomplish this.
Douglas J. Steele said:
Sounds like you're trying to use a so-called "smart key" (it's not a
complimentary description...)
Let me guess. The 08 is because this is 2008: next year you're going to
want
09. You should have multiple fields in the table, and concatenate them
together in a query, rather than try to store them as a single field.
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
Bill said:
Thanks Dave. I kinda figured I would have to do it that way. The only
other
part of this is that I want to start counting at a certain number.
Each
time
I make it a autonumber it resets to 1 and adds from there. How can I
set
the
first number (I want to start around 08-158
Thanks
:
Not directly, but you can make it appear that way to users on reports
and
forms.
I had similar issue with a quote program. Put two field in table:
QuoteYear, Default Value = Year(Date())
This plugs the year when record is added.
QuoteNumber, Autonumber
Then on form (or report), use text box with Control Source =
=Right(CStr([QuoteYear]),2) & CStr(Format([QuoteNumber],"0000"))
This would show, for example, 080251
In your case, add & "-" & , and you would get 08-0251
--
Dave Thompson
Allen, TX
US
:
Any way to create a auto number field with a text field in it? I
have
a
field called "RecordID" which is formated as XX-XXX (08-111). Since
I
need
the '-' dash in the field, it must be a text field. But I want it
to
autonumber and I want to pick the number where it starts.
Any chance of this??
Thanks,
Bill