reset number on new year

"Kd" <ke******@yahoo.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

I have the following set up to give me aresponse number R05-001
I would like it to reset to R06-001 at new year

ResponseNo: "R" & Right(Format(Date(),"yyyy"),2) & "-" &
Format([PONum],"000")
This is an expression in a query
any help is apprecieated
Ken

Ken,

That should do the job as is. "Date()" will return the current date, so it
should switch automatically at new year.

It seems a bit odd to Format for a 4 digit year then take only the right 2
digits, but it should work.

--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.

intead of Right(Format(Date(),"yyyy"),2)
should i go with datepart(yy)

Kd wrote:

I have the following set up to give me aresponse number R05-001
I would like it to reset to R06-001 at new year

ResponseNo: "R" & Right(Format(Date(),"yyyy"),2) & "-" &
Format([PONum],"000")
This is an expression in a query
any help is apprecieated
Ken

How is PONum generated? What you have now will automtically change with the
year on the prefix, but the numbers will not go back over to start at one again
if you are using an AutoNumber for the PONum.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

"Kd" <ke******@yahoo.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

intead of Right(Format(Date(),"yyyy"),2)
should i go with datepart(yy)

Instead of

Right(Format(Date(),"yyyy"),2)

you could use:

Format(Date(),"yy")

The first one says to assemble the year for the current date in 4 digits
then throw away all but the last 2 digits. The second one says simply
assemble the last two digits of the year for the current date. There is
nothing wrong with the first form, it just does more work than is needed.

Cheers,
--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.

PONum is set up longint. on table
and
=DMax("[PONum]","[PO Table]")+1
on form
should i change this?

What is in the PO Table? Is it a single record with the current PO number,
or does it contain a record of each PO you've created?

--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.
"Kd" <ke******@yahoo.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

PONum is set up longint. on table
and
=DMax("[PONum]","[PO Table]")+1
on form
should i change this?

Kd wrote:

PONum is set up longint. on table
and
=DMax("[PONum]","[PO Table]")+1
on form
should i change this?

First I see another problem. If you are generating this formatted number in a
query using...

ResponseNo: "R" & Right(Format(Date(),"yyyy"),2) & "-" & Format([PONum],"000")

....then ALL of your records will change to R06 for the prefix in the new year
not just new ones because you are always using the current system date. You
need to be using the date that the record was created. Do you have such a
field?

If your table included a field named CreateDate then your query expression would
be...

ResponseNo: "R" & Format(CreateDate(),"yy") & "-" & Format([PONum],"000")

....(eliminating the unnecessary Right() function)

Then your DMax assignment changes to...

=Nz(DMax("[PONum]","[PO Table]", "CreateDate >= DateSerial(Year(Date()), 1,
1)"), 0)+1

Now instead of finding the highest number in the table and adding one to it, the
expression finds the highest number from only those records created in the
current year. The Nz() wrapper is required for the very first entry of any
given year since DMax() will return Null in that circumstance.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

thanks
and i would assign the dmax satement to the
response number textbox on form correct
thanks again
Ken

On Fri, 30 Dec 2005 15:09:14 GMT, "Rick Brandt"
<ri*********@hotmail.com> wrote:

Kd wrote:

PONum is set up longint. on table
and
=DMax("[PONum]","[PO Table]")+1
on form
should i change this?

First I see another problem. If you are generating this formatted number in a
query using...

ResponseNo: "R" & Right(Format(Date(),"yyyy"),2) & "-" & Format([PONum],"000")

...then ALL of your records will change to R06 for the prefix in the new year
not just new ones because you are always using the current system date. You
need to be using the date that the record was created. Do you have such a
field?

If your table included a field named CreateDate then your query expression would
be...

ResponseNo: "R" & Format(CreateDate(),"yy") & "-" & Format([PONum],"000")

...(eliminating the unnecessary Right() function)

Then your DMax assignment changes to...

=Nz(DMax("[PONum]","[PO Table]", "CreateDate >= DateSerial(Year(Date()), 1,
1)"), 0)+1

Now instead of finding the highest number in the table and adding one to it, the
expression finds the highest number from only those records created in the
current year. The Nz() wrapper is required for the very first entry of any
given year since DMax() will return Null in that circumstance.

If I may tack on...

I've had similar cases where clients want to number their files or
responses or whatever, in the format yy/nnn. When I ask why, the
reason is always so they know how many are in that year. Of course, a
simple query can always give that answer. So I always try to change
clients to accepting a simple sequential numbering system.

Where I can't convince them, I would do something like Rick has
suggested but put this in the Default value of the control, thereby
generating the number when the record is created and maintaining it
in subsequent edits by locking the field.

P

Peter Sutton wrote:

If I may tack on...

I've had similar cases where clients want to number their files or
responses or whatever, in the format yy/nnn. When I ask why, the
reason is always so they know how many are in that year. Of course, a
simple query can always give that answer. So I always try to change
clients to accepting a simple sequential numbering system.

Where I can't convince them, I would do something like Rick has
suggested but put this in the Default value of the control, thereby
generating the number when the record is created and maintaining it
in subsequent edits by locking the field.

Actually using the DefaultValue property for this is a poor choice in a
multi-user system as it allows mutliple users to grab the same value if they
both start records before either one saves. Also it won't work at all in a
continuous form.

Assigning the value in the BeforeUpdate event is the best place to prevent
collisions.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

On Sat, 31 Dec 2005 05:40:52 GMT, "Rick Brandt"
<ri*********@hotmail.com> wrote:

Peter Sutton wrote:

If I may tack on...

I've had similar cases where clients want to number their files or
responses or whatever, in the format yy/nnn. When I ask why, the
reason is always so they know how many are in that year. Of course, a
simple query can always give that answer. So I always try to change
clients to accepting a simple sequential numbering system.

Where I can't convince them, I would do something like Rick has
suggested but put this in the Default value of the control, thereby
generating the number when the record is created and maintaining it
in subsequent edits by locking the field.

Actually using the DefaultValue property for this is a poor choice in a
multi-user system as it allows mutliple users to grab the same value if they
both start records before either one saves. Also it won't work at all in a
continuous form.

Assigning the value in the BeforeUpdate event is the best place to prevent
collisions.

True what you say about collisions.

In the few cases, I've had like this, users want to see the allocated
number at the start of the process and with not a lot of users adding
records infrequently, I put a check in the BeforeUpdate event to check
and fix any collisions.

Horses for courses I guess.

P

Rick
Thanks for all your help first of all
I changed this as you sugested and even changed my table field from
OrderDate to
CreateDate
When I put
ResponseNo: "R" & Format(CreateDate(),"yy") & "-" &
Format([PONum],"000")
I get undefined function'CreateDate' in expression
What am I doing wrong
Ken

Kd wrote:

Rick
Thanks for all your help first of all
I changed this as you sugested and even changed my table field from
OrderDate to
CreateDate
When I put
ResponseNo: "R" & Format(CreateDate(),"yy") & "-" &
Format([PONum],"000")
I get undefined function'CreateDate' in expression
What am I doing wrong
Ken

CreateDate (being the name of a field and not a function) should not have ()
after it.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Thanks
That worked
now all I have to do is get PONum to reset to 001
deleted all records from last year except 5 and
new PONum forr 2006 came out R06-006

think I have tried
Then your DMax assignment changes to...
=Nz(DMax("[PONum]","[PO Table]", "CreateDate >=
DateSerial(Year(Date()), 1,
1)"), 0)+1
everywhere i can think of on before update events
still cant get number to set to R06-001

Kd wrote:

think I have tried
Then your DMax assignment changes to...
=Nz(DMax("[PONum]","[PO Table]", "CreateDate >=
DateSerial(Year(Date()), 1,
1)"), 0)+1
everywhere i can think of on before update events
still cant get number to set to R06-001

Did you try the BeforeUpdate event of the form?

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

yes i did

Kd wrote:

yes i did

And what do you get? The expression is using Nz() so when there are no records
yet created in the year 2006 the DMax() will return Null and then the Nz()
pretty much HAS to return zero to which we add 1. There is not much that can go
wrong with that.

Just to clarify...the DMax() statement will only produce the incrementing PONum.
It will not give you a result that looks like "R06-001". You use a default
value of Now() or Date() for the CreateDate, and you assign the value of PONum
in the BeforeUpdate event of the form using the DMax() expression. It is only
AFTER the record has been created that you can use your Format() expression to
give you the "R06-001" output.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Well I am gonna learn a lot of paitence working with this stuff
cleaned out table have 3 fields
PoNum - lng int
Create Date - short Date default Date()
Customer -Text

Query
PoNum
Create Date
Customer
ResponseNo: "R" & Format(CreateDate(),"yy") & "-" &
Format([PONum],"000")
Form
PONum default value=DMax("[PONum]","[PO Table]")+1
CreateDate default value date()
Customer
ResponseNo
BeforeUpDate on form
=Nz(DMax("[PONum]","[PO Table]", "CreateDate >=
DateSerial(Year(Date()), 1,
1)"), 0)+1

I am sure I have something in wrong place
Any help is so appreceated
Ken

Kd wrote:

Well I am gonna learn a lot of paitence working with this stuff
cleaned out table have 3 fields
PoNum - lng int
Create Date - short Date default Date()
Customer -Text

Query
PoNum
Create Date
Customer
ResponseNo: "R" & Format(CreateDate(),"yy") & "-" &
Format([PONum],"000")
Form
PONum default value=DMax("[PONum]","[PO Table]")+1
CreateDate default value date()
Customer
ResponseNo
BeforeUpDate on form
=Nz(DMax("[PONum]","[PO Table]", "CreateDate >=
DateSerial(Year(Date()), 1,
1)"), 0)+1

I am sure I have something in wrong place
Any help is so appreceated
Ken

Get rid of the default value on PONum. That is what the BeforeUpdate event is
for and besides the default value you are using won't start over each year the
way the BeforeUpdate code will.

The DMax expression doesn't go into the BeforeUpdate property box. What you
enter there is "[Event Procedure]" (without the quotes) and then you press the
builder button [...] to the right of the box which will take you to the VBA code
window for your form. THAT is where the DMax statement goes. You should find
your cursor pre-positioned between two lines of VBA code that marks the start
and end of the BeforeUpdate sub-routine. Your DMax() statement goes between
those two lines.

Lastly your DMax statement is incorrect because the value it retrieves needs to
be assigned to the PONum field. You should have (in the VBA code window)...

If IsNull(Me!PONum) Then
Me!PONum = Nz(DMax("[PONum]","[PO Table]", "CreateDate >=
DateSerial(Year(Date()), 1, 1)"), 0)+1
End If

The reason for the IsNull Test is that the BeforeUpdate event can fire multiple
times for a given record. You only want to assign the the PONum value the very
first time the record is saved (at which point PONum will still be null). There
are other form events that only fire once per record, but they are either
unreliable or allow more chances for two users to grab the same value if you
ever have more than one person entering records.

So to recap...

When you start to create a new record PONum will be Null, CreateDate will
default to the current date, and ResponseNo will be Null (because PONum is still
Null). As soon as you save the record the BeforeUpdate will calculate the
proper PONum value and assign it to the PONum field just before the save occurs.
Afterwards both PONumn and ResponseNo shoud be populated with the correct value.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Thank you very much
that was simple
I have one more question
last year when we started this
I used just a formatted number to be the R05-001
PoNumber
This was my primary key on the table
With this new approach what would you suggest for a Pk for this table?
Thanks again
Ken

Kd wrote:

Thank you very much
that was simple
I have one more question
last year when we started this
I used just a formatted number to be the R05-001
PoNumber
This was my primary key on the table
With this new approach what would you suggest for a Pk for this
table? Thanks again
Ken

I would use a composite key consisting of both CreateDate and PONum.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com