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
| Dim Vporte As String
Dim txt_ChaineSQL As String
Dim strSQLSELECT As String
Dim strSQLFROM As String
Dim strSQLWHERE As String
Dim strSQLGROUPBY As String
Dim strSQLHAVING As String
Dim strSQLORDERBY As String
Dim objSpeech As Object
Dim strPhrase As String
Dim intPitch As Integer
Private Sub Cmd_vrac_Click()
Dim strInsert As String
Vdatedebut = DateAuFormatUS(Me.Texte_DateDebut)
Vdatefin = DateAuFormatUS(Me.Texte_dateFin)
Vdatedebut = CDate(Texte_DateDebut)
Vdatefin = CDate(Texte_dateFin)
Vporte = Val(Texte_porte)
With Me.Liste_journee_postale
.RowSource = "Table/Requête"
.RowSource = "SELECT First(CVDate(Fix(([heure_debut]-5/24)))) AS jour FROM maTabletemporaire"
.Requery
End With
With Me.Liste_Vrac
.RowSourceType = "Table/Requête"
.RowSource = " Select * from maTableTemporaire order by porte, heure_debut"
.ColumnCount = 5 ' nombre de colonne que dois avoir le formulaire
.BoundColumn = 1 ' la colonne de reference
strSQLSELECT = "SELECT Min(dbo_vwItemEventHistory.EventTime) AS [heure debut], Max(dbo_vwItemEventHistory.EventTime) AS [heure fin], T_vracs.PORTE, T_vracs.PFC_Destinataire, Count(dbo_vwItemEventHistory.ItemID) AS nombre_colis_tries, Date() AS creationDate "
strSQLFROM = "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)]) INNER JOIN T_vracs ON [table_Affich-general].[Nom Porte principale] = T_vracs.PFC_Destinataire "
strSQLWHERE = "WHERE (((dbo_vwItemEventHistory.ItemEventTypeID) = 4) And ((dbo_vwItemEventHistory.ResultTypeID) = 23) And (([table_Affich-general].Allée) = 'vrac') AND (dbo_vwItemEventHistory.EventTime) >=#" & Format(Vdatedebut, "MM/dd/yyyy HH:mm") & "# And (dbo_vwItemEventHistory.EventTime) <=#" & Format(Vdatefin, "MM/dd/yyyy HH:mm") & "#) "
strSQLGROUPBY = "GROUP BY CVDate(Fix(([EventTime]-5/24))), T_vracs.PORTE, T_vracs.PFC_Destinataire "
strSQLHAVING = "HAVING ((T_vracs.PORTE) = '" & Vporte & "')"
strSQLORDERBY = "ORDER BY CVDate(Fix(([EventTime]-5/24))) DESC, T_vracs.PFC_Destinataire;"
txt_ChaineSQL = strSQLSELECT & vbCrLf & _
strSQLFROM & vbCrLf & _
strSQLWHERE & vbCrLf & _
strSQLGROUPBY & vbCrLf & _
strSQLHAVING & vbCrLf & _
strSQLORDERBY
'MsgBox (txt_ChaineSQL)
strInsert = "INSERT INTO maTableTemporaire (heure_debut, heure_fin, PORTE, PFC_Destinataire, nombre_colis_tries, creationDate) " & txt_ChaineSQL
CurrentDb.Execute strInsert
.Requery
End With
With Me.Liste_journee_postale
.RowSource = "Table/Requête"
.RowSource = "SELECT First(CVDate(Fix(([heure_debut]-5/24)))) AS jour FROM maTabletemporaire"
.Requery
End With
Debug.Print txt_ChaineSQL
End Sub |
Partager