By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,581 Members | 1,975 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,581 IT Pros & Developers. It's quick & easy.

Automatic Tracking Number

P: n/a
I need assistance in creating an Automatic tracking number that
appears in a textbox that looks like: 2004-001. The first part of the
number is based on the year, the second part is generated from the
last available number used. Also, the second part of the number needs
to start over whenever a new year is started. for example: 2005-001.
Any help would be greatly appreciated.

Thanks,
pjac
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
try for the controlsource of the control:

=format(datepart(date(), "yyyy"), "0000") & format([id], "000")

or something along those lines

"pjac" <pj**@erols.com> wrote in message
news:94**************************@posting.google.c om...
I need assistance in creating an Automatic tracking number that
appears in a textbox that looks like: 2004-001. The first part of the
number is based on the year, the second part is generated from the
last available number used. Also, the second part of the number needs
to start over whenever a new year is started. for example: 2005-001.
Any help would be greatly appreciated.

Thanks,
pjac

---
Please immediately let us know (by phone or return email) if (a) this email
contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.679 / Virus Database: 441 - Release Date: 07/05/2004
Nov 12 '05 #2

P: n/a
pjac,

The posted formatting ideas may be useful but your question requires a lot
more of an answer.

I often have a table, tblAppVariables for containing values that change over
various times and situations. I put a variable name and then the variable
argument into the table so that I can search the table for the named
variable and get the current value of arument. For example: field VarName:
MyYear might contain "2004" and field VarArg: MyYearlySequence might contain
"123". Do not make this table available or even visible to your users!

You need to write vba code to test the current year against MyYear. When
the year part of Now() is greater then you store the new value in MyYear and
store "000" (or what ever your first number for the year is) in
MyYearlySequence. In your application there should be only one place that
would use the code so the code logically belongs there. If it might be
called from more than one place then put it into a function procedure and
call the function where ever you need it.

HTH
--
-Larry-
--

"WindAndWaves" <ac****@ngaru.com> wrote in message
news:B3*******************@news.xtra.co.nz...
try for the controlsource of the control:

=format(datepart(date(), "yyyy"), "0000") & format([id], "000")

or something along those lines

"pjac" <pj**@erols.com> wrote in message
news:94**************************@posting.google.c om...
I need assistance in creating an Automatic tracking number that
appears in a textbox that looks like: 2004-001. The first part of the
number is based on the year, the second part is generated from the
last available number used. Also, the second part of the number needs
to start over whenever a new year is started. for example: 2005-001.
Any help would be greatly appreciated.

Thanks,
pjac

---
Please immediately let us know (by phone or return email) if (a) this

email contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.679 / Virus Database: 441 - Release Date: 07/05/2004


Nov 12 '05 #3

P: n/a
BOT
Nov 12 '05 #4

P: n/a
Oops!

I left out the sequence number bit. You probably have figured it out
already but in cases where the year now is the same as the year saved then
you will retrieve the current sequence number, increment it, save it to a
variable and store the new value back in the table. Then concatenate your
formatted year-sequence and use it as needed.

HTH
--
-Larry-
--

"Larry Daugherty" <La********************@verizon.net> wrote in message
news:Jb*******************@nwrddc02.gnilink.net...
pjac,

The posted formatting ideas may be useful but your question requires a lot
more of an answer.

I often have a table, tblAppVariables for containing values that change over various times and situations. I put a variable name and then the variable
argument into the table so that I can search the table for the named
variable and get the current value of arument. For example: field VarName: MyYear might contain "2004" and field VarArg: MyYearlySequence might contain "123". Do not make this table available or even visible to your users!

You need to write vba code to test the current year against MyYear. When
the year part of Now() is greater then you store the new value in MyYear and store "000" (or what ever your first number for the year is) in
MyYearlySequence. In your application there should be only one place that
would use the code so the code logically belongs there. If it might be
called from more than one place then put it into a function procedure and
call the function where ever you need it.

HTH
--
-Larry-
--

"WindAndWaves" <ac****@ngaru.com> wrote in message
news:B3*******************@news.xtra.co.nz...
try for the controlsource of the control:

=format(datepart(date(), "yyyy"), "0000") & format([id], "000")

or something along those lines

"pjac" <pj**@erols.com> wrote in message
news:94**************************@posting.google.c om...
I need assistance in creating an Automatic tracking number that
appears in a textbox that looks like: 2004-001. The first part of the
number is based on the year, the second part is generated from the
last available number used. Also, the second part of the number needs
to start over whenever a new year is started. for example: 2005-001.
Any help would be greatly appreciated.

Thanks,
pjac

---
Please immediately let us know (by phone or return email) if (a) this

email
contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.679 / Virus Database: 441 - Release Date: 07/05/2004


Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.