1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122
| Private Sub RefreshQueryAcc()
Dim SQLWhere As String
'---Liste LstResults dans formulaire Recherche---
'---Cette procédure permet de rechercher les données accidents nécéssaires à l'établissement des Etats---
'---Circonstances, fiches, liste---
SQL = "SELECT MAPINFO_ID, ID, Date_a, Heure, N_PV, Secteur, commune, N_voirie, Adresse, SommeDeUTué, SommeDeUBlessé, SommeDeUBH, Tué, Blessé, BH, " _
& "Journée, Jour, Mois, Année, Lieu_dit, Hors_agglomération, Hors_intersection, Causes_accident, Date_saisie, " _
& "Observation, Intersection, PR, Distance, Age_victime, véhicule, Lumière, Type_voirie, Surface, Cond_atmosph, " _
& "Alcool, Stupéfiant, Insee FROM R_Accident_base Where R_Accident_base!MAPINFO_ID <> 0"
If Not Me.chkjournee Then
SQL = SQL & "And R_Accident_base!journée = '" & Me.cmbRechjournee & "' "
End If
If Not Me.chkmois Then
SQL = SQL & "And R_Accident_base!mois = '" & Me.cmbRechmois & "' "
End If
If Not Me.chkannée Then
SQL = SQL & "And R_Accident_base!année = '" & Me.cmbRechannée & "' "
End If
If Not Me.chksemaine Then
SQL = SQL & "And R_Accident_base!Semaine = '" & Me.cmbRechsemaine & "' "
End If
If Not Me.chksecteur Then
SQL = SQL & "And R_Accident_base!Secteur = '" & Me.cmbRechsecteur & "' "
End If
If Not Me.chkcommune Then
SQL = SQL & "And R_Accident_base!Insee = '" & Me.cmbRechcommune & "' "
End If
If Not Me.chktvoie Then
SQL = SQL & "And R_Accident_base!Type_voirie = '" & Me.cmbRechtvoie & "' "
End If
If Not Me.chknvoie Then
SQL = SQL & "And R_Accident_base!N_voirie = '" & Me.cmbRechnvoie & "' "
End If
If Not Me.chkagglo Then
SQL = SQL & "And R_Accident_base!Hors_agglomération = '" & Me.cmbRechagglo & "' "
End If
If Not Me.chkinter Then
SQL = SQL & "And R_Accident_base!Hors_intersection = '" & Me.cmbRechinter & "' "
End If
If Not Me.chkalcool Then
SQL = SQL & "And R_Accident_base!Alcool = '" & Me.cmbRechalcool & "' "
End If
If Not Me.chkstup Then
SQL = SQL & "And R_Accident_base!Stupéfiant = '" & Me.cmbRechstup & "' "
End If
If Not Me.chkluminosite Then
SQL = SQL & "And R_Accident_base!Lumière = '" & Me.cmbRechluminosite & "' "
End If
If Not Me.chkpv Then
SQL = SQL & "And R_Accident_base!PV = '" & Me.cmbRechpv & "' "
End If
If Not Me.chkMortel Then
SQL = SQL & "And R_Accident_base!Acc_mortel = '" & Me.cmbRechMortel & "' "
End If
If Not Me.chkvehicule Then
SQL = SQL & "And R_Accident_base!Véhicule like '*" & Me.txtRechvehicule & "*' "
End If
If Not Me.chkadresse Then
SQL = SQL & "And R_Accident_base!Adresse like '*" & Me.txtRechadresse & "*' "
End If
If Not Me.chkId Then
SQL = SQL & "And R_Accident_base!Id like '*" & Me.TxtRechId & "*' "
End If
If Not Me.chkperiode Then
SQL = SQL & " And R_Accident_base!Date_a BETWEEN # " & Format(Me.txtRechDdebut, "mm/dd/yyyy") & "# AND #" & Format(Me.txtRechDfin, "mm/dd/yyyy") & " # "
End If
SQLWhere = Trim(Right(SQL, Len(SQL) - InStr(SQL, "Where ") - Len("Where ") + 1))
SQL = SQL & " ORDER BY 2;"
Me.lblStatsAcc.Caption = DCount("*", "R_Accident_base", SQLWhere)
Me.lblStatsAccm.Caption = DCount("*", "R_Accident_base", SQLWhere & " and [SommeDeUTué]<>0")
Me.lblStatsTue.Caption = "" & DSum("[SommeDeUTué]", "R_Accident_base", SQLWhere)
Me.lblStatsBl.Caption = "" & DSum("[SommeDeUBlessé]", "R_Accident_base", SQLWhere)
Me.lblStatsBh.Caption = "" & DSum("[SommeDeUBH]", "R_Accident_base", SQLWhere)
Me.lblStatsGenAcc.Caption = DCount("*", "R_Accident_base", SQLWhere & " and [Secteur]= 'Gendarmerie'")
Me.lblStatsGenAccm.Caption = DCount("*", "R_Accident_base", SQLWhere & " and [Secteur]= 'Gendarmerie'" & " and [SommeDeUTué]<>0")
Me.lblStatsGenTue.Caption = "" & DSum("[SommeDeUTué]", "R_Accident_base", SQLWhere & " and [Secteur]= 'Gendarmerie'")
Me.lblStatsGenBl.Caption = "" & DSum("[SommeDeUBlessé]", "R_Accident_base", SQLWhere & " and [Secteur]= 'Gendarmerie'")
Me.lblStatsGenBh.Caption = "" & DSum("[SommeDeUBH]", "R_Accident_base", SQLWhere & " and [Secteur]= 'Gendarmerie'")
Me.lblStatsPolAcc.Caption = DCount("*", "R_Accident_base", SQLWhere & " and [Secteur]= 'Police'")
Me.lblStatsPolAccm.Caption = DCount("*", "R_Accident_base", SQLWhere & " and [Secteur]= 'Police'" & " and [SommeDeUTué]<>0")
Me.lblStatsPolTue.Caption = "" & DSum("[SommeDeUTué]", "R_Accident_base", SQLWhere & " and [Secteur]= 'Police'")
Me.lblStatsPolBl.Caption = "" & DSum("[SommeDeUBlessé]", "R_Accident_base", SQLWhere & " and [Secteur]= 'Police'")
Me.lblStatsPolBh.Caption = "" & DSum("[SommeDeUBH]", "R_Accident_base", SQLWhere & " and [Secteur]= 'Police'")
Me.lstResults.RowSource = SQL
Me.lstResults.Requery
'---------------Requête vituelle de SQL-------------------
Dim Q As QueryDef
Set Q = CurrentDb.QueryDefs("Ri_synthese_Com")
Q.SQL = SQL
Set Q = Nothing
End Sub |
Partager