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
| Public Function getCommandFinance() As Variant
Dim db As dao.Database
Dim rs As dao.Recordset
Set db = CurrentDb
Dim sql, sql1, sql2, sql3, sql4, sql5 As String
sql1 = "SELECT tbl_Journal.REFFIN, tbl_Journal.DAT_OPERAT, tbl_Journal.MONTANT, tbl_Journal.RECETTE, Sum(IIf([recette]=1,[Montant],0)) AS TotalIN, Sum(IIf([recette]=2,[Montant],0)) AS TotalOUT, tbl_Journal.FIN_AVT, tbl_Journal.FIN_APR "
sql2 = "FROM Ts_Banques RIGHT JOIN tbl_Journal ON Ts_Banques.NM_REDUIT = tbl_Journal.REFFIN "
sql3 = "GROUP BY tbl_Journal.REFFIN, tbl_Journal.DAT_OPERAT, tbl_Journal.MONTANT, tbl_Journal.RECETTE, tbl_Journal.FIN_AVT, tbl_Journal.FIN_APR "
sql5 = "ORDER BY tbl_Journal.REFFIN, tbl_Journal.DAT_OPERAT;"
sql4 = "HAVING (((tbl_Journal.REFFIN) = ""CP"")) "
sql = sql1 & sql2 & sql3 & sql4 & sql5
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)
If Not rs.EOF() Then
rs.MoveFirst 'Récupère les soldes bancaires au début de l'exercice puis les inscrits dans le sous-formulaire
Me.txt_CP1 = rs!fin_avt
Me.txt_Dat1CP = rs!dat_operat
rs.MoveLast 'Récupère les soldes bancaires en fin d'exercice puis les inscrits dans le sous-formulaire
Me.txt_CP = rs!FIN_APR
Me.txt_DatCP = rs!dat_operat
End If
'......
rs.Close
Set rs = Nothing
Set db = Nothing
End Function |
Partager