424,949 Members | 1,302 Online
Need help? Post your question and get tips & solutions from a community of 424,949 IT Pros & Developers. It's quick & easy.

# Date Serial problem

 P: n/a 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 Oct 22 '06 #1
9 Replies

 P: n/a 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 Oct 22 '06 #2

 P: n/a 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 Oct 22 '06 #3

 P: n/a Thank you very much Ken Oct 22 '06 #4

 P: n/a 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 Oct 22 '06 #5

 P: n/a 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 Oct 22 '06 #6

 P: n/a 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") Oct 22 '06 #7

 P: n/a "ken" = 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 Oct 23 '06 #8

 P: n/a 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 Oct 23 '06 #9

 P: n/a ken wrote: Rick Brandt wrote: >ken wrote: >>Now that I have this number working in the formwhat would be the best approach to get theymm-000 number back to the jobs tableso I can use this as the primary keysome thing like after updatejobs.JobNumber=Me!JobNumber?ThanksKen Personally I wouldn't do that. I would use a composite PKconsisting of the date and Max()+1 number or I would use a surrogatelike AutoNumber for the PK.If you insist on saving it to the table then doing what you aresuggesting in the BeforeUpdate of the form should accomplish it.Just add that line after the lines that you already have tocalculate the Max()+1 value. --Rick Brandt, Microsoft Access MVPEmail (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 Oct 24 '06 #10

### This discussion thread is closed

Replies have been disabled for this discussion.