Re: Tables/Form

Jeff,

Where would I type "ITCC" & Format(Date(),yy) & "-" &
[YourSequenceNumberField] in the query? I have set up a field for each
fact.

E.g. ITCC is one field, 07 (the year) is another field and 001 is the third
field.


"Jeff Boyce" <nonsense@xxxxxxxxxxxx> wrote in message
news:%23Fzt91KRHHA.2124@xxxxxxxxxxxxxxxxxxxxxxx
Jenn

<scratch, scratch!> I could have sworn there was information at mvps.org,
but I don't find it there either.

The basic concept is that you will use DMax() to find the maximum value
currently used in your [SequenceNumber] field, add one to it, and use THAT
for your new sequence number in your new record.

If you want to have a "number" that looks like "ITCC07-001", concatenate
"ITCC" & Format(Date(),yy) & "-" & [YourSequenceNumberField] in a query
(note that this is untested aircode). Use that value from the query in
your forms and reports.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Jenn Civello" <jenn.civello@xxxxxxxxx> wrote in message
news:eMenzEKRHHA.3440@xxxxxxxxxxxxxxxxxxxxxxx
I can't seem to find any articles on "custom auto numbers", can you please
tell me where I should be looking?


"Jeff Boyce" <nonsense@xxxxxxxxxxxx> wrote in message
news:O7p7Il%23QHHA.4276@xxxxxxxxxxxxxxxxxxxxxxx
Jean

Take a look at www.mvps.org/access for "Custom Autonumbers" for an
example of how to do that.

By the way, I hope you aren't saying you have that entire string of
characters in one field. From your description, it consists of at least
two separate values (year, sequence #), and I suspect at least one or
two more. You need one field for each fact, and can easily use a query
to concatenate the field values together for display in a form. If you
try putting them all together in one field, you (and Access) will have
to work a lot harder.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Jenn Civello" <jenn.civello@xxxxxxxxx> wrote in message
news:u2HlQU%23QHHA.4384@xxxxxxxxxxxxxxxxxxxxxxx
I have a table that I have consecutive numbers in and I would like to
setup a form that will automatically enter the next number each time.
E.G. ITCC07-001, once this number is used I would like it to
automatically go to ITCC07-002. But I need to be able to stop this at
the end of the year. Can you please let me know if this can be done.

Thanks









.

Relevant Pages

  • Re: Tables/Form
    ... THAT for your new sequence number in your new record. ... Use that value from the query in ... Jeff Boyce ... Microsoft Office/Access MVP ...
    (microsoft.public.access.gettingstarted)
  • Re: Tables/Form
    ... THAT for your new sequence number in your new record. ... Use that value from the query in ... Jeff Boyce ... Microsoft Office/Access MVP ...
    (microsoft.public.access.gettingstarted)
  • Re: Tables/Form
    ... Doug Steele, Microsoft Access MVP ... <scratch, scratch!> ... and use THAT for your new sequence number in your new record. ... in a query (note that this is untested ...
    (microsoft.public.access.gettingstarted)
  • Re: Tables/Form
    ... Doug Steele, Microsoft Access MVP ... THAT for your new sequence number in your new record. ... in a query (note that this is untested ... Microsoft Office/Access MVP ...
    (microsoft.public.access.gettingstarted)
  • Re: Syntax Error
    ... Thanks for your help....looks like I will start from scratch. ... > That is an invalid statement. ... That wil bring up the query builder which you can use to ... > queries I would rebuild the form over agaon from scratch using the wizard. ...
    (microsoft.public.access.forms)