Auto Numbering

[Guest]

I'm trying to number my job entries in a new database (one I'm creating).
Can someone direct me toward the correct expression for this auto numbering
sequence? Seems fairly simple, but nothing I've tried works.

I need the autonumber to use the last 2 digits of the current year, a dash
or hyphen, then the autonumber.

I'm new, took a couple classes, but I'm far from REALLY knowing what I'm
doing.

I'd appreciate any help.

 

[Rick Brandt]

I'm trying to number my job entries in a new database (one I'm
creating). Can someone direct me toward the correct expression for
this auto numbering sequence? Seems fairly simple, but nothing I've
tried works.

I need the autonumber to use the last 2 digits of the current year, a
dash or hyphen, then the autonumber.

I'm new, took a couple classes, but I'm far from REALLY knowing what
I'm doing.

I'd appreciate any help.

Actually it would be best to NOT do that. What you should do is have a
RecordDate field that defaults to Now(). That will automatically record the
date and time that every record is first saved. That is very often a handy
piece of information to have anyway. Then you have a separate ID field that
contains the ordinal number. That can be an AutoNumber (assuming you know
the limitations of those in this context) or a Long Integer field that you
populate when records are created with a code-based solution.

Now...on fomrs nad reports you can DISPLAY exactly what you originally
askjed for with the expression...

=Format(RecordDate,"yy\-") & ID

If you want leading zeros on the ID like 07-0001 then use...

=Format(RecordDate,"yy\-") & Format(ID,"0000")

If you want the ID to be sequential and start over each year then you cannot
use AutoNumber. You would calculate and assign the number in the
BeforeUpdate event of your form...

If Me.NewRecord Then
Me.ID = Nz(DMax("ID", "TableName", "Year(RecordDate) = Year(Date())"),0)
+ 1
End If

 

[Guest]

I guess I'm just not understanding.

I understand that it won't be possible to do this in a table, correct? I
can't use any of the expressions you've suggested as a validation rule? Or
as an input mask? So, if I put it in a query...would it work correctly if I
had the table look it up in that query?

 

[Jason Lepack]

I guess I'm just not understanding.

I understand that it won't be possible to do this in a table, correct? I
can't use any of the expressions you've suggested as a validation rule? Or
as an input mask?

The table itself cannot handle this.

So, if I put it in a query...would it work correctly if I
had the table look it up in that query?

Sure you could, but I wouldn't suggest it...

but I would then direct you to:
http://www.mvps.org/access/lookupfields.htm

I direct you to the page above because it will save me time later when
you post back later wondering why your query isn't returning any
records when you specify a text value criteria for a number field.

Bottom Line:
Users will be interacting with Forms and Reports so why bother with
adding complexity at the table level? ... Just stick with Rick's
suggestions... K.I.S.S.

Cheers,
Jason Lepack

 

[Rick Brandt]

I guess I'm just not understanding.

I understand that it won't be possible to do this in a table,
correct? I can't use any of the expressions you've suggested as a
validation rule? Or as an input mask? So, if I put it in a
query...would it work correctly if I had the table look it up in that
query?

You can set the DefaultValue property of the RecordDate field to =Now() in
your table's design (good idea actually). Everything else though would be
done in code behind the form you use for entering records and you would HAVE
to use that form to enter them. Anything you enter directly into a table or
query would not work without you manually supplying the correct value for
the ID field.

 

[Guest]

UGH! Like I said...I'm new.

It was a simple mis-spelling that was prohibiting the numbering sequence
from working.

It FINALLY works now.

Thank you so very much. I will likely be back with other issues. Thanks
for providing your time...this is a wonderful Access tool!

Regards,

Betty