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
| Option Compare Database
Dim VJournee As Date
Dim VJourneeDebut As Date, VJourneeFin As Date, VTrancheHoraire As String
Dim txt_ChaineSQL As String
Dim strSQLSELECT As String
Dim strSQLWHERE As String
Dim strSQLGROUPBY As String
Dim strSQLORDERBY As String
Dim strSQLHAVING As String
Private Sub Cmd_envoyer_Click()
VJournee = CDate(Texte_journee) + TimeSerial(5, 0, 0)
VTrancheHoraire = ChampVacationFormulaire
Select Case VTrancheHoraire
Case "Matin" 'A faire correctement si string ou numérique VTrancheHoraire
VJourneeDebut = CDate(Texte_journee) + TimeSerial(5, 0, 0)
VJourneeFin = CDate(Texte_journee) + TimeSerial(12, 30, 0)
Case "Apres_Midi"
VJourneeDebut = CDate(Texte_journee) + TimeSerial(12, 30, 0)
VJourneeFin = CDate(Texte_journee) + TimeSerial(19, 30, 0)
Case "Nuit"
VJourneeDebut = CDate(Texte_journee) + TimeSerial(19, 30, 0)
VJourneeFin = (CDate(Texte_journee) + 1) + TimeSerial(5, 0, 0)
Case "Journee_Complete"
VJourneeDebut = CDate(Texte_journee) + TimeSerial(5, 0, 0)
VJourneeFin = (CDate(Texte_journee) + 1) + TimeSerial(5, 0, 0)
With Me.Listerecirculation
.RowSourceType = "Table/Requête"
.ColumnCount = 8 ' nombre de colonne que dois avoir le formulaire intitulé "Listerecirculation"
.BoundColumn = 1 ' la colonne de reference
strSQLSELECT = "SELECT dbo_vwParts.DisplayName, Count(dbo_vwItemEventHistory.ItemID) AS CompteDeItemID, [table_Affich-general].DESTINATION, [table_Affich-general].[Nom Porte principale], [table_Affich-general].Type, CVDate(Fix(([EventTime]-5/24))) AS journee, MonthName(Month(FormatDateTime([EventTime]-5/24))) AS Mois, Year(CVDate(Fix(dbo_vwItemEventHistory!EventTime-5/24))) AS année, [table_Affich-general].Département, WeekdayName((Weekday((FormatDateTime((CVDate(Fix(([EventTime]-5/24)))))))),0,1) AS [jour semaine], DatePart('ww',CVDate(Fix(([EventTime]-5/24))),2,2) AS n°semaine" & vbCrLf & _
"FROM (dbo_vwItemEventHistory INNER JOIN dbo_vwParts ON dbo_vwItemEventHistory.PartID = dbo_vwParts.ID) INNER JOIN [table_Affich-general] ON dbo_vwParts.DisplayName = [table_Affich-general].[Chute (format access)]"
strSQLWHERE = "WHERE (((dbo_vwItemEventHistory.EventTime) BETWEEN #" & VJourneeDebut & "# And #" & VJourneeFin & "# "
strSQLGROUPBY = "GROUP BY dbo_vwParts.DisplayName, [table_Affich-general].DESTINATION, [table_Affich-general].[Nom Porte principale], [table_Affich-general].Type, CVDate(Fix(([EventTime]-5/24))), MonthName(Month(FormatDateTime([EventTime]-5/24))), Year(CVDate(Fix(dbo_vwItemEventHistory!EventTime-5/24))), [table_Affich-general].Département, WeekdayName((Weekday((FormatDateTime((CVDate(Fix(([EventTime]-5/24)))))))),0,1), DatePart('ww',CVDate(Fix(([EventTime]-5/24))),2,2), dbo_vwItemEventHistory.ItemEventTypeID, dbo_vwItemEventHistory.ResultTypeID"
strSQLHAVING = "HAVING (((dbo_vwItemEventHistory.ItemEventTypeID) = 4) And ((dbo_vwItemEventHistory.ResultTypeID) = 23))"
strSQLORDERBY = "ORDER BY CVDate(Fix(([EventTime]-5/24))) DESC;"
txt_ChaineSQL = strSQLSELECT & vbCrLf & _
strSQLWHERE & vbCrLf & _
strSQLGROUPBY & vbCrLf & _
strSQLHAVING & vbCrLf & _
strSQLORDERBY
.RowSource = txt_ChaineSQL
.Requery
MsgBox txt_ChaineSQL
End With
End Sub |
