If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#12
|
|||
|
|||
Access 2003 : Numbering alarms by PermitNo so that after 365 d
Bonjour Ken,
No I did not try the last code you posted. I will take the time today or tomorrow. I went on Internet and search for Cannock Chase. Very nice ! From the picture of Winter 2005, I figured that you don't have a lot of snow in the winter. It seem a marvelous place to walk and observe birds. I hope the mountain bike are not all over the place. They can really deteriorate a trail. I have a mountain bike, but I never ride at a speed were I deteriorate the trail when I put the brake on. If I go up and start to slide, I get down from the bike, and walk the rest of the slope. I live by the side of a lake in les Laurentides, north of Montréal. Les Laurentides is a region of very old montains. A mix forest of hardwood (maple, birch, beech, and a bit of oak) and evergreen (fir, pine). We have a snowshoeing trail starting right from our backward and going around a montain. Many nice trails for walking, snowshoeing, crosscountry skiing and montain biking are at less than 30 minutes from my house. I live 45 minutes from la Station Tremblant http://www.tremblant.ca/index-e.htm. Yesterday I went snowshoeing with friends at the top of the mountain. It was nice, but I prefer the quiet forest far from the crowd. Have a good day, Céline "Ken Sheridan" a écrit dans le message de news: ... Bonjour Céline: Did you try the last code I posted? It seemed to work with my dummy test data, but I'd be interested to know if it worked with your real data. I hope you have somewhere nice to walk your dogs. I live on the edge of Cannock Chase, which is an ancient royal hunting forest, so I go there every morning with my dog, Ben. Mes amitiés à votre mère. Ken Sheridan Stafford, England "Céline Brien" wrote: Hi everybody, Hi Ken, Again, thank you so much for your answer. I highly appreciate your help ! I just came back from walking with my dogs, and during my walk, I had the idea of adding the time of the alarm in the same field has the date (it was in a different field). It works perfectly well, maybe even to much !!!! If the first alarm id Feb, 10 2007 at 22:45 and than you have another alarme Feb,10 2008 at 16:45, it will give the alarm the number 2 because we are missing 6 hours before the 365 days are over !!!! I will study your last proposition and come back tomorrow. Now I am going to visit my old mother. Many many thanks again and again ! Céline "Ken Sheridan" a écrit dans le message de news: ... Céline: Adding some code to the function to test for an existing alarm for the permit on the same day should do it. This seems to work with my test data: Public Function GetNoAlarm(lngPermitNo As Long, dtmDateAlarm As Date) As Integer Dim varStartOfYear As Variant Dim varLastNumber As Variant Dim strCriteria As String Dim intNextNumber As Integer ' if a row exists with an alarm number ' of 1 within last year then next alarm ' number is last number plus 1, ' otherwise new alarm number is 1 strCriteria = _ "PermitNo = " & lngPermitNo & " And DateAlarm #" & _ Format(DateAdd("yyyy", -1, dtmDateAlarm), "mm/dd/yyyy") & _ "# And NoAlarm = 1" varStartOfYear = DLookup("DateAlarm", "Alarms", strCriteria) If IsNull(varStartOfYear) Then GetNoAlarm = 1 Else ' is there aleady row for this permit on same day? strCriteria = _ "PermitNo = " & lngPermitNo & " And DateAlarm = #" & _ Format(dtmDateAlarm, "mm/dd/yyyy") & "# And NoAlarm 0" varLastNumber = DMax("NoAlarm", "Alarms", strCriteria) If Not IsNull(varLastNumber) Then ' add 1 to last number on same day GetNoAlarm = varLastNumber + 1 Else ' look up last alarm number strCriteria = _ "PermitNo = " & lngPermitNo & " And DateAlarm = #" & _ Format(DMax("DateAlarm", "Alarms", "PermitNo = " & _ lngPermitNo & " And NoAlarm 0"), "mm/dd/yyyy") & "#" intNextNumber = DMax("NoAlarm", "Alarms", strCriteria) ' and add 1 GetNoAlarm = intNextNumber + 1 End If End If End Function Ken Sheridan Stafford, England "Céline Brien" wrote: Hi everybody, Hi Ken, It is working perfectly ! Numbering and update of the numbers in case a date has to be retyped. The function UpdateNoAlarms is in the AfterUpdate event procedure of the subform. It is pretty fast to. Our total number of alarms by year should be around 1 200. I don't know how to thank you ! The only little thing is when there is more than two alarms the same day for the same permit. The last entered is given the right numer and all the others are given the number 1. Is there anything that can be done ? It might not happen often, but once is enough... Each alarm as a unique number : CallNo. It is a text field compose of the date and a number : 02262007-001 Can we use this field in the criteria ? Thanks again, Céline "Ken Sheridan" a écrit dans le message de news: ... Céline : In that case I think you probably will have to have a NoAlarm column in the Alarms table and update it when a new row is inserted in the subform. You should be able to do that with a VBA function like this: Public Function GetNoAlarm(lngPermitNo As Long, dtmDateAlarm As Date) As Integer Dim varStartOfYear As Variant Dim strCriteria As String Dim intNextNumber As Integer ' if a row exists with an alarm number ' of 1 within last year then next alarm ' number is last number plus 1, ' otherwise new alarm number is 1 strCriteria = _ "PermitNo = " & lngPermitNo & " And DateAlarm #" & _ Format(DateAdd("yyyy", -1, dtmDateAlarm), "mm/dd/yyyy") & _ "# And NoAlarm = 1" varStartOfYear = DLookup("DateAlarm", "Alarms", strCriteria) If IsNull(varStartOfYear) Then GetNoAlarm = 1 Else ' look up last alarm number strCriteria = _ "PermitNo = " & lngPermitNo & " And DateAlarm = #" & _ Format(DMax("DateAlarm", "Alarms", "PermitNo = " & _ lngPermitNo & " And NoAlarm 0"), "mm/dd/yyyy") & "#" intNextNumber = DLookup("NoAlarm", "Alarms", strCriteria) ' and add 1 GetNoAlarm = intNextNumber + 1 End If End Function In the BeforeUpdate event procedure of the subform update the number with: If Me.NewRecord Then Me.NoAlarm = GetNoAlarm(Me.PermitNo, Me.DateAlarm) End If The drawback of this of course is that it relies on the rows for each permit being inserted in the correct data order. You could not for instance insert a row for May 1, then one for July 14 and then one for July 7. It should be possible to write a global update routine like this which would renumber them correctly, however: Public Function UpdateNoAlarms() Dim dbs As DAO.Database Dim rst As DAO.Recordset Dim strSQL As String Set dbs = CurrentDb 'set all alarm numbers to zero strSQL = "Update Alarms SET NoAlarm = 0" dbs.Execute strSQL ' update alarm numbers calling GetNoAlarm function for each row strSQL = _ "SELECT PermitNo, DateAlarm, NoAlarm " & _ " FROM Alarms ORDER BY PermitNo, DateAlarm" Set rst = dbs.OpenRecordset(strSQL) With rst Do While Not .EOF .Edit .Fields("NoAlarm") = GetNoAlarm(.Fields("PermitNo"), .Fields("DateAlarm")) .Update .MoveNext Loop End With End Function Ken Sheridan Stafford, England "Céline Brien" wrote: Hi everybody, Hi Ken, I just tested again, and I realise I did not made clearly explain the problem to solve. The numbering does not resart at 1 at the beginning of a new year, but 365 after the last number 1. For exemple : If the first false alarm is April 3, 2007, you come back to 1 April 3, 2008. Then if your next false alarm is June 23, 2008, you come back to 1 June 23, 2009. I hope I made the problem more clear. Thank you for any help, Céline "Ken Sheridan" a écrit dans le message de news: ... Céline: Provided there is more than one false alarm per day per permit you could do it with a subquery, but that would make the query non-updatable, so as the query is the RecordSource for a subform which you presumably need to edit the VBA DCount function would be better: SELECT PermitNo, DateAlarm, DCount("*", "Alarms", "PermitNo = " & [PermitNo] & " And Year(DateAlarm) = " & Year([DateAlarm]) & " And DateAlarm = #" & Format([DateAlarm],"mm/dd/yyyy") & "#") AS NoAlarm FROM Alarms ORDER BY DateAlarm; NoAlarm is a computed column in the query here, not a column in the Alarms table. You don't need a column in the table, and should not have one as it would introduce redundancy. You may well find this is rather slow. Do you really need to see the numbering in the subform? A far more efficient way would be in a report grouped by PermitNo then Year(DateAlarm). You could then simply put an unbound text box with a ControlSource property of =1 and a RunningSum property of Over Group. This would also allow for more then one alarm per permit per day. Ken Sheridan Stafford, England "Céline Brien" wrote: Hi everybody, FormPermit with SubFormAlarms writing in QryAlarms. The form describe the owner of a permit for an alarm. The subform the list of false alarms related to that permit. How do you obtain an automatic numbering of the field NoAlarm so that, 365 days after the last alarm numbered 1, the count return to 1 ??? PermitNo DateAlarm NoAlarm 1 Jan 4, 2007 1 1 Feb 17, 2007 2 1 April 23, 2007 3 1 Jan 4, 2008 1 ------ back to 1, 365 after the first alarm 1 Nov 5, 2008 2 1 Jan 4, 2009 1 ------ back to 1, 365 after the last alarm numbered 1 Thank you very much for your precious help, Céline |
#13
|
|||
|
|||
Access 2003 : Numbering alarms by PermitNo so that after 365 d
Hi Ken,
Sunday almost noon. I finally tested your last codes and they work fine. I wont use them because I prefer to use the date and time to separate one date from another. I will for sure keep them if I ever need them. Thank you again for your so precious help ! Céline "Ken Sheridan" a écrit dans le message de news: ... Bonjour Céline: Did you try the last code I posted? It seemed to work with my dummy test data, but I'd be interested to know if it worked with your real data. I hope you have somewhere nice to walk your dogs. I live on the edge of Cannock Chase, which is an ancient royal hunting forest, so I go there every morning with my dog, Ben. Mes amitiés à votre mère. Ken Sheridan Stafford, England "Céline Brien" wrote: Hi everybody, Hi Ken, Again, thank you so much for your answer. I highly appreciate your help ! I just came back from walking with my dogs, and during my walk, I had the idea of adding the time of the alarm in the same field has the date (it was in a different field). It works perfectly well, maybe even to much !!!! If the first alarm id Feb, 10 2007 at 22:45 and than you have another alarme Feb,10 2008 at 16:45, it will give the alarm the number 2 because we are missing 6 hours before the 365 days are over !!!! I will study your last proposition and come back tomorrow. Now I am going to visit my old mother. Many many thanks again and again ! Céline "Ken Sheridan" a écrit dans le message de news: ... Céline: Adding some code to the function to test for an existing alarm for the permit on the same day should do it. This seems to work with my test data: Public Function GetNoAlarm(lngPermitNo As Long, dtmDateAlarm As Date) As Integer Dim varStartOfYear As Variant Dim varLastNumber As Variant Dim strCriteria As String Dim intNextNumber As Integer ' if a row exists with an alarm number ' of 1 within last year then next alarm ' number is last number plus 1, ' otherwise new alarm number is 1 strCriteria = _ "PermitNo = " & lngPermitNo & " And DateAlarm #" & _ Format(DateAdd("yyyy", -1, dtmDateAlarm), "mm/dd/yyyy") & _ "# And NoAlarm = 1" varStartOfYear = DLookup("DateAlarm", "Alarms", strCriteria) If IsNull(varStartOfYear) Then GetNoAlarm = 1 Else ' is there aleady row for this permit on same day? strCriteria = _ "PermitNo = " & lngPermitNo & " And DateAlarm = #" & _ Format(dtmDateAlarm, "mm/dd/yyyy") & "# And NoAlarm 0" varLastNumber = DMax("NoAlarm", "Alarms", strCriteria) If Not IsNull(varLastNumber) Then ' add 1 to last number on same day GetNoAlarm = varLastNumber + 1 Else ' look up last alarm number strCriteria = _ "PermitNo = " & lngPermitNo & " And DateAlarm = #" & _ Format(DMax("DateAlarm", "Alarms", "PermitNo = " & _ lngPermitNo & " And NoAlarm 0"), "mm/dd/yyyy") & "#" intNextNumber = DMax("NoAlarm", "Alarms", strCriteria) ' and add 1 GetNoAlarm = intNextNumber + 1 End If End If End Function Ken Sheridan Stafford, England "Céline Brien" wrote: Hi everybody, Hi Ken, It is working perfectly ! Numbering and update of the numbers in case a date has to be retyped. The function UpdateNoAlarms is in the AfterUpdate event procedure of the subform. It is pretty fast to. Our total number of alarms by year should be around 1 200. I don't know how to thank you ! The only little thing is when there is more than two alarms the same day for the same permit. The last entered is given the right numer and all the others are given the number 1. Is there anything that can be done ? It might not happen often, but once is enough... Each alarm as a unique number : CallNo. It is a text field compose of the date and a number : 02262007-001 Can we use this field in the criteria ? Thanks again, Céline "Ken Sheridan" a écrit dans le message de news: ... Céline : In that case I think you probably will have to have a NoAlarm column in the Alarms table and update it when a new row is inserted in the subform. You should be able to do that with a VBA function like this: Public Function GetNoAlarm(lngPermitNo As Long, dtmDateAlarm As Date) As Integer Dim varStartOfYear As Variant Dim strCriteria As String Dim intNextNumber As Integer ' if a row exists with an alarm number ' of 1 within last year then next alarm ' number is last number plus 1, ' otherwise new alarm number is 1 strCriteria = _ "PermitNo = " & lngPermitNo & " And DateAlarm #" & _ Format(DateAdd("yyyy", -1, dtmDateAlarm), "mm/dd/yyyy") & _ "# And NoAlarm = 1" varStartOfYear = DLookup("DateAlarm", "Alarms", strCriteria) If IsNull(varStartOfYear) Then GetNoAlarm = 1 Else ' look up last alarm number strCriteria = _ "PermitNo = " & lngPermitNo & " And DateAlarm = #" & _ Format(DMax("DateAlarm", "Alarms", "PermitNo = " & _ lngPermitNo & " And NoAlarm 0"), "mm/dd/yyyy") & "#" intNextNumber = DLookup("NoAlarm", "Alarms", strCriteria) ' and add 1 GetNoAlarm = intNextNumber + 1 End If End Function In the BeforeUpdate event procedure of the subform update the number with: If Me.NewRecord Then Me.NoAlarm = GetNoAlarm(Me.PermitNo, Me.DateAlarm) End If The drawback of this of course is that it relies on the rows for each permit being inserted in the correct data order. You could not for instance insert a row for May 1, then one for July 14 and then one for July 7. It should be possible to write a global update routine like this which would renumber them correctly, however: Public Function UpdateNoAlarms() Dim dbs As DAO.Database Dim rst As DAO.Recordset Dim strSQL As String Set dbs = CurrentDb 'set all alarm numbers to zero strSQL = "Update Alarms SET NoAlarm = 0" dbs.Execute strSQL ' update alarm numbers calling GetNoAlarm function for each row strSQL = _ "SELECT PermitNo, DateAlarm, NoAlarm " & _ " FROM Alarms ORDER BY PermitNo, DateAlarm" Set rst = dbs.OpenRecordset(strSQL) With rst Do While Not .EOF .Edit .Fields("NoAlarm") = GetNoAlarm(.Fields("PermitNo"), .Fields("DateAlarm")) .Update .MoveNext Loop End With End Function Ken Sheridan Stafford, England "Céline Brien" wrote: Hi everybody, Hi Ken, I just tested again, and I realise I did not made clearly explain the problem to solve. The numbering does not resart at 1 at the beginning of a new year, but 365 after the last number 1. For exemple : If the first false alarm is April 3, 2007, you come back to 1 April 3, 2008. Then if your next false alarm is June 23, 2008, you come back to 1 June 23, 2009. I hope I made the problem more clear. Thank you for any help, Céline "Ken Sheridan" a écrit dans le message de news: ... Céline: Provided there is more than one false alarm per day per permit you could do it with a subquery, but that would make the query non-updatable, so as the query is the RecordSource for a subform which you presumably need to edit the VBA DCount function would be better: SELECT PermitNo, DateAlarm, DCount("*", "Alarms", "PermitNo = " & [PermitNo] & " And Year(DateAlarm) = " & Year([DateAlarm]) & " And DateAlarm = #" & Format([DateAlarm],"mm/dd/yyyy") & "#") AS NoAlarm FROM Alarms ORDER BY DateAlarm; NoAlarm is a computed column in the query here, not a column in the Alarms table. You don't need a column in the table, and should not have one as it would introduce redundancy. You may well find this is rather slow. Do you really need to see the numbering in the subform? A far more efficient way would be in a report grouped by PermitNo then Year(DateAlarm). You could then simply put an unbound text box with a ControlSource property of =1 and a RunningSum property of Over Group. This would also allow for more then one alarm per permit per day. Ken Sheridan Stafford, England "Céline Brien" wrote: Hi everybody, FormPermit with SubFormAlarms writing in QryAlarms. The form describe the owner of a permit for an alarm. The subform the list of false alarms related to that permit. How do you obtain an automatic numbering of the field NoAlarm so that, 365 days after the last alarm numbered 1, the count return to 1 ??? PermitNo DateAlarm NoAlarm 1 Jan 4, 2007 1 1 Feb 17, 2007 2 1 April 23, 2007 3 1 Jan 4, 2008 1 ------ back to 1, 365 after the first alarm 1 Nov 5, 2008 2 1 Jan 4, 2009 1 ------ back to 1, 365 after the last alarm numbered 1 Thank you very much for your precious help, Céline |
|
Thread Tools | |
Display Modes | |
|
|