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 |
#1
|
|||
|
|||
RE : Access 2003 : Numbering alarms by PermitNo so that after 365 days of the last number 1, the count returns to 1
Hi everybody !
In February I had an answer to this question. See the codes below : UpdateNoAlarmeIncendie and GetNoAlarmeIncendie. Maybe I am not in the right news but because it all started in this news I decided to stay here. What I need find now, is a way to keep some alarms out of the numbering system. I added a yes/no field to identify these alarms. I tried to create a new variable : Dim varAlarmeFondee As Variant And these codes : varAlarmeFondee = DLookup("AlarmeFondee", "AlarmesIncendie", strCriteria) If (varAlarmeFondee) = -1 Then GetNoAlarmeIncendie = 0 I got zero at all the alarms of the table :-( Can anybody help me ? Many thanks, Céline ------------------------------------------------------- Public Function UpdateNoAlarmeIncendie() Dim dbs As DAO.Database Dim rst As DAO.Recordset Dim strSQL As String Set dbs = CurrentDb 'Remettre ŕ zéro NoAlarmeIncendie strSQL = "Update AlarmesIncendie SET NoAlarmeIncendie = 0" dbs.Execute strSQL ' Mettre ŕ jour NoAlarmeIncendie en appelant la fonction GetNoAlarmeIncendie pour chacune des lignes strSQL = _ "SELECT NoPermis, DateAppel, NoAlarmeIncendie " & _ " FROM AlarmesIncendie ORDER BY NoPermis, DateAppel" Set rst = dbs.OpenRecordset(strSQL) With rst Do While Not .EOF .Edit .Fields("NoAlarmeIncendie") = GetNoAlarmeIncendie(.Fields("NoPermis"), .Fields("DateAppel")) .Update .MoveNext Loop End With End Function ------------------------------------------- Public Function GetNoAlarmeIncendie(lngNoPermis As Long, dtmDateAppel 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 = _ "NoPermis = " & lngNoPermis & " And DateAppel #" & _ Format(DateAdd("yyyy", -1, dtmDateAppel), "yyyy-mm-dd hh:mm") & _ "# And NoAlarmeIncendie = 1" varStartOfYear = DLookup("DateAppel", "AlarmesIncendie", strCriteria) If IsNull(varStartOfYear) Then GetNoAlarmeIncendie = 1 Else ' look up last alarm number strCriteria = _ "NoPermis = " & lngNoPermis & " And DateAppel = #" & _ Format(DMax("DateAppel", "AlarmesIncendie", "NoPermis = " & _ lngNoPermis & " And NoAlarmeIncendie 0"), "yyyy-mm-dd hh:mm") & "#" intNextNumber = DLookup("NoAlarmeIncendie", "AlarmesIncendie", strCriteria) ' and add 1 GetNoAlarmeIncendie = intNextNumber + 1 End If End Function |
Thread Tools | |
Display Modes | |
|
|