start automatic number at a given value???

[st8employee]

Until 2008, on my data entry form, I have had to automatically generate a
4-digit number that resets itself to "0001" at the beginning of each year. I
use the following to generate that number:

=DMax("[MaxOfWorkOrd]","Establish Order Number/Year
Query","[MaxOfYear]=[testyear]")+1

FYI NOTES:

[WorkOrd] = field in which autonumber is generated
[testyear] = yyyy of the current date
[MaxOfYear] = [testyear]
format of [WorkOrd] is set to "0000"


This was a hand-me-down formula that was already on the form when I got it
from my predecessor like six years ago, and since it wasn't "broke", I never
tried to "fix" it...so what you see above is pretty much all I know about how
this calculation works.

Now, my predicament:
Beginnig with 1/1/2008, my supervisor wants reset the number to "1000" at
the beginnig of each year instead of "0001". How do I modify this formula to
give me "1000" as the first autonumber?

P.S.: The data entry user is holding all hardcopies to be keyed, until AFTER
this adjustment is applied...!

 

[Ken Snell \(MVP\)]

This expression should "start" at 1000 for the returned value:

=Nz(DMax("[MaxOfWorkOrd]","Establish Order Number/Year
Query","[MaxOfYear]=[testyear]"),999)+1

But it will not affect the "autonumber" field itself, just the value
returned by the above expression.

 

[st8employee]

It didn't work...just returns "Error#" in the [WorkOrd] field.

You think perhaps there is something else that I am missing?
Or do you think that maybe it's not working because they started keying
(like 40 records) before I applied the expression? Do I need to remove the
records, apply the expression, and re-enter them? If so, it's no problem, I
just want to understand why it isn't working...because the expression LOOKS
right...

This expression should "start" at 1000 for the returned value:

=Nz(DMax("[MaxOfWorkOrd]","Establish Order Number/Year
Query","[MaxOfYear]=[testyear]"),999)+1

But it will not affect the "autonumber" field itself, just the value
returned by the above expression.
--

Ken Snell
<MS ACCESS MVP>

Until 2008, on my data entry form, I have had to automatically generate a
4-digit number that resets itself to "0001" at the beginning of each year.
I
use the following to generate that number:

=DMax("[MaxOfWorkOrd]","Establish Order Number/Year
Query","[MaxOfYear]=[testyear]")+1

FYI NOTES:

[WorkOrd] = field in which autonumber is generated
[testyear] = yyyy of the current date
[MaxOfYear] = [testyear]
format of [WorkOrd] is set to "0000"


This was a hand-me-down formula that was already on the form when I got it
from my predecessor like six years ago, and since it wasn't "broke", I
never
tried to "fix" it...so what you see above is pretty much all I know about
how
this calculation works.

Now, my predicament:
Beginnig with 1/1/2008, my supervisor wants reset the number to "1000" at
the beginnig of each year instead of "0001". How do I modify this formula
to
give me "1000" as the first autonumber?

P.S.: The data entry user is holding all hardcopies to be keyed, until
AFTER
this adjustment is applied...!

 

[st8employee]

Didn't work...just returned "Error#".

You think I am missing something?

Or perhaps it is because I applied the expression after they entered 40
records for this year. Do you thin if I remove those records, apply the
expression, then re-enter them that it will work?

It's not a big deal, I just want to understand why it didn't work, because
the expression Nz=( ......999)+1 LOOKS good to me...

This expression should "start" at 1000 for the returned value:

=Nz(DMax("[MaxOfWorkOrd]","Establish Order Number/Year
Query","[MaxOfYear]=[testyear]"),999)+1

But it will not affect the "autonumber" field itself, just the value
returned by the above expression.
--

Ken Snell
<MS ACCESS MVP>

Until 2008, on my data entry form, I have had to automatically generate a
4-digit number that resets itself to "0001" at the beginning of each year.
I
use the following to generate that number:

=DMax("[MaxOfWorkOrd]","Establish Order Number/Year
Query","[MaxOfYear]=[testyear]")+1

FYI NOTES:

[WorkOrd] = field in which autonumber is generated
[testyear] = yyyy of the current date
[MaxOfYear] = [testyear]
format of [WorkOrd] is set to "0000"


This was a hand-me-down formula that was already on the form when I got it
from my predecessor like six years ago, and since it wasn't "broke", I
never
tried to "fix" it...so what you see above is pretty much all I know about
how
this calculation works.

Now, my predicament:
Beginnig with 1/1/2008, my supervisor wants reset the number to "1000" at
the beginnig of each year instead of "0001". How do I modify this formula
to
give me "1000" as the first autonumber?

P.S.: The data entry user is holding all hardcopies to be keyed, until
AFTER
this adjustment is applied...!

 

[Ken Snell \(MVP\)]

Do you mean the WorkOrd control (not field)? That error (#Error) means that
you have an error in the expression itself. Double-check the expression for
typos, etc.

Also, per your other post, if you've already had 40 work orders this year,
the solution will not work unless those other work orders have numbers
assigned to them that started at 1000.

--

Ken Snell
<MS ACCESS MVP>

It didn't work...just returns "Error#" in the [WorkOrd] field.

You think perhaps there is something else that I am missing?
Or do you think that maybe it's not working because they started keying
(like 40 records) before I applied the expression? Do I need to remove the
records, apply the expression, and re-enter them? If so, it's no problem,
I
just want to understand why it isn't working...because the expression
LOOKS
right...

This expression should "start" at 1000 for the returned value:

=Nz(DMax("[MaxOfWorkOrd]","Establish Order Number/Year
Query","[MaxOfYear]=[testyear]"),999)+1

But it will not affect the "autonumber" field itself, just the value
returned by the above expression.
--

Ken Snell
<MS ACCESS MVP>

Until 2008, on my data entry form, I have had to automatically generate
a
4-digit number that resets itself to "0001" at the beginning of each
year.
I
use the following to generate that number:

=DMax("[MaxOfWorkOrd]","Establish Order Number/Year
Query","[MaxOfYear]=[testyear]")+1

FYI NOTES:

[WorkOrd] = field in which autonumber is generated
[testyear] = yyyy of the current date
[MaxOfYear] = [testyear]
format of [WorkOrd] is set to "0000"


This was a hand-me-down formula that was already on the form when I got
it
from my predecessor like six years ago, and since it wasn't "broke", I
never
tried to "fix" it...so what you see above is pretty much all I know
about
how
this calculation works.

Now, my predicament:
Beginnig with 1/1/2008, my supervisor wants reset the number to "1000"
at
the beginnig of each year instead of "0001". How do I modify this
formula
to
give me "1000" as the first autonumber?

P.S.: The data entry user is holding all hardcopies to be keyed, until
AFTER
this adjustment is applied...!