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
| Option Compare Database
Dim VJournee As Date
Dim txt_ChaineSQL As String
Dim strSQLSELECT As String
Dim strSQLWHERE As String
Dim strSQLGROUPBY As String
Dim strSQLORDERBY As String
Private Sub Cmd_envoyer_Click()
VJournee = CDate(Texte_journee) + 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 [table_Affich-general].DESTINATION, [table_Affich-general].[Chute (format access)], [table_Affich-general].Type, FormatDateTime([DischargeEventTime],2) AS jour, Sum(dbo_vwItemData.RecirculationCount) AS [nbre de colis en recirculation], Round((Sum([RecirculationCount])/Count([ItemID])*100),2) AS taux , FormatDateTime((CVDate((Fix([DischargeEventTime]*24*1)/24/1))),4) AS [tranche horaire], FormatDateTime(CVDate(Fix([DischargeEventTime]*24*6)/24/6),4) AS [10 min]" & _
" FROM dbo_vwItemData INNER JOIN (dbo_vwParts INNER JOIN [table_Affich-general] ON dbo_vwParts.DisplayName = [table_Affich-general].[Chute (format access)]) ON dbo_vwItemData.DischargePartID = dbo_vwParts.ID"
strSQLORDERBY = "ORDER BY FormatDateTime([DischargeEventTime],2)ASC;"
strSQLGROUPBY = " GROUP BY [table_Affich-general].DESTINATION, [table_Affich-general].[Chute (format access)], [table_Affich-general].Type, FormatDateTime([DischargeEventTime],2), FormatDateTime((CVDate((Fix([DischargeEventTime]*24*1)/24/1))),4), FormatDateTime(CVDate(Fix([DischargeEventTime]*24*6)/24/6),4)"
strSQLWHERE = " WHERE (dbo_vwItemData.DischargeEventTime) BETWEEN #" & VJournee & "# And #" & VJournee + 1 & "# "
txt_ChaineSQL = strSQLSELECT & vbCrLf & _
strSQLWHERE & vbCrLf & _
strSQLGROUPBY & vbCrLf & _
strSQLORDERBY
.RowSource = txt_ChaineSQL
.Requery
End With
End Sub |
Partager