Date Serial problem

[ken]

I am trying to get job numbers to reset to 000 at begining of each
month
and beginning of new year
I was able to do this on another form just resetting at beginning of
year now
i need to do the same on this form with month

this is what i have right now on before update event
If IsNull(Me!JobNo1) Then
Me!JobNo1 = Nz(DMax("[JobNo1]", "[jobs]") + 1, "EntrDate >=
DateSerial(Year(Date(),1,1), Month(Date()),1,1)")
End If

my result should be ymm-000
at beginning of each month and year
I am getting the correct ymm but getting highest number +1 instead of
000
This is what I used on other for that works
If IsNull(Me!ResponseNo) Then
Me!ResponseNo = Nz(DMax("[ResponseNo]", "[tblRepair]", "DateEntered

>=DateSerial(Year(Date()),1,1)"), 0) + 1

I really appreceate any help and input on this
Ken

[Rick Brandt]

ken wrote:

I am trying to get job numbers to reset to 000 at begining of each
month
and beginning of new year
I was able to do this on another form just resetting at beginning of
year now
i need to do the same on this form with month

this is what i have right now on before update event
If IsNull(Me!JobNo1) Then
Me!JobNo1 = Nz(DMax("[JobNo1]", "[jobs]") + 1, "EntrDate >=
DateSerial(Year(Date(),1,1), Month(Date()),1,1)")
End If

my result should be ymm-000
at beginning of each month and year
I am getting the correct ymm but getting highest number +1 instead of
000
This is what I used on other for that works
If IsNull(Me!ResponseNo) Then
Me!ResponseNo = Nz(DMax("[ResponseNo]", "[tblRepair]", "DateEntered

=DateSerial(Year(Date()),1,1)"), 0) + 1

I really appreceate any help and input on this
Ken

[Rick Brandt]

ken wrote:

I am trying to get job numbers to reset to 000 at begining of each
month
and beginning of new year
I was able to do this on another form just resetting at beginning of
year now
i need to do the same on this form with month

this is what i have right now on before update event
If IsNull(Me!JobNo1) Then
Me!JobNo1 = Nz(DMax("[JobNo1]", "[jobs]") + 1, "EntrDate >=
DateSerial(Year(Date(),1,1), Month(Date()),1,1)")
End If

my result should be ymm-000
at beginning of each month and year
I am getting the correct ymm but getting highest number +1 instead of
000
This is what I used on other for that works
If IsNull(Me!ResponseNo) Then
Me!ResponseNo = Nz(DMax("[ResponseNo]", "[tblRepair]", "DateEntered

=DateSerial(Year(Date()),1,1)"), 0) + 1

I really appreceate any help and input on this
Ken

Your syntax is all messed up here. The plus 1 goes at the end just like it is
on the expression you say is working. Also the where clause with DateSerial as
you have it written makes no sense. Try the following...

Me!JobNo1 = Nz(DMax("[JobNo1]", "[jobs]", "EntrDate >= DateSerial(Year(Date()),
Month(Date()), 1)"), 0) + 1

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[ken]

Thank you very much
Ken

[ken]

Now that I have this number working in the form
what would be the best approach to get the
ymm-000 number back to the jobs table
so I can use this as the primary key
some thing like after update
jobs.JobNumber=Me!JobNumber?
Thanks
Ken

[Rick Brandt]

ken wrote:

Now that I have this number working in the form
what would be the best approach to get the
ymm-000 number back to the jobs table
so I can use this as the primary key
some thing like after update
jobs.JobNumber=Me!JobNumber?
Thanks
Ken

Personally I wouldn't do that. I would use a composite PK consisting of the
date and Max()+1 number or I would use a surrogate like AutoNumber for the PK.

If you insist on saving it to the table then doing what you are suggesting in
the BeforeUpdate of the form should accomplish it. Just add that line after the
lines that you already have to calculate the Max()+1 value.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[ken]

Rick Brandt wrote:

ken wrote:

Now that I have this number working in the form
what would be the best approach to get the
ymm-000 number back to the jobs table
so I can use this as the primary key
some thing like after update
jobs.JobNumber=Me!JobNumber?
Thanks
Ken

Personally I wouldn't do that. I would use a composite PK consisting of the
date and Max()+1 number or I would use a surrogate like AutoNumber for the PK.

If you insist on saving it to the table then doing what you are suggesting in
the BeforeUpdate of the form should accomplish it. Just add that line after the
lines that you already have to calculate the Max()+1 value.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

well after playing with this for a while decided I would like the
JobNumber to be primary key in table as it matches what I had before
and all other tables use this number
I already have about 9000 records with this number in database from the
old way
of changing first three numbers manually every month
Just trying to make this better
When i put jobs.JobNumber=Me!JobNumberin before update on form it
does not update table
thanks for any advice
JobNumber is in query as expression JobNumber:
Right(Format([EntrDate],"yyyy"),1) & Format([EntrDate],"mm") & "-" &
Format([JobNo1],"000")

[Bob Quintal]

"ken" <ke******@yahoo.comwrote in
news:11**********************@e3g2000cwe.googlegro ups.com:

I am trying to get job numbers to reset to 000 at begining of
each month
and beginning of new year
I was able to do this on another form just resetting at
beginning of year now
i need to do the same on this form with month

this is what i have right now on before update event
If IsNull(Me!JobNo1) Then
Me!JobNo1 = Nz(DMax("[JobNo1]", "[jobs]") + 1, "EntrDate

>=

DateSerial(Year(Date(),1,1), Month(Date()),1,1)")
End If

my result should be ymm-000
at beginning of each month and year
I am getting the correct ymm but getting highest number +1
instead of 000

That ymm-000+1 is because your field is text and not numeric. It
worked in your previous example because you were adding a number
to a number.

>
This is what I used on other for that works
If IsNull(Me!ResponseNo) Then
Me!ResponseNo = Nz(DMax("[ResponseNo]", "[tblRepair]",
"DateEntered

>>=DateSerial(Year(Date()),1,1)"), 0) + 1

I really appreceate any help and input on this
Ken

what you want to do is get the current number, grab the fifth
through seventh characters, convert to a number, add 1 and
reassemble the string.

iNewJobNo = _
Nz(cint(DMax("mid([JobNo1],5,7))", _
"[jobs]", _
"EntrDate >= DateSerial(Year(Date(),1,1), _
& "Month(Date()),1,1),0) + 1
If IsNull(Me!JobNo1) Then
me!jobNo1 = _
Right(Format([EntrDate],"yyyy"),1) _
& Format([EntrDate],"mm") _
& "-" & Format([iNewJobNo],"000")
end if
--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[ken]

Me!JobNo1 = Nz(DMax("[JobNo1]", "[jobs]", "EntrDate >=
DateSerial(Year(Date()),
Month(Date()), 1)"), 0) + 1
seemed to work good in form

JobNo1=001
JobNumber=610-001

now I need to get JobNumber to populate table for primary Key
Thanks
Ken

[Rick Brandt]

ken wrote:

Rick Brandt wrote:

>ken wrote:

>>Now that I have this number working in the form
what would be the best approach to get the
ymm-000 number back to the jobs table
so I can use this as the primary key
some thing like after update
jobs.JobNumber=Me!JobNumber?
Thanks
Ken

Personally I wouldn't do that. I would use a composite PK
consisting of the date and Max()+1 number or I would use a surrogate
like AutoNumber for the PK.

If you insist on saving it to the table then doing what you are
suggesting in the BeforeUpdate of the form should accomplish it.
Just add that line after the lines that you already have to
calculate the Max()+1 value.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

well after playing with this for a while decided I would like the
JobNumber to be primary key in table as it matches what I had before
and all other tables use this number
I already have about 9000 records with this number in database from
the old way
of changing first three numbers manually every month
Just trying to make this better
When i put jobs.JobNumber=Me!JobNumberin before update on form it
does not update table
thanks for any advice
JobNumber is in query as expression JobNumber:
Right(Format([EntrDate],"yyyy"),1) & Format([EntrDate],"mm") & "-" &
Format([JobNo1],"000")

You cannot set the value of a field based on an expression. You need to set
the value of the base fields that are used in the expression and you are
already doing that. This was exaclty my point, that if you can use an
expression then you do not need a "saved field" containing the same data.

If you must have it saved as a separate field in your table then it is THAT
field that you must set in the BeforeUpdate event, not the one based on an
expression.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com