9. Отчет_по_обедам_f

Private Sub BUT_EXCEL_Click()

Const QNAME = "qout_Заказ_обедов_свод"

On Error GoTo ErrHandler

OpenQuery QNAME

DoCmd.RunCommand acCmdOutputToExcel

CloseObject acQuery, QNAME

Exit Sub

ErrHandler:

Select Case Err.NUMBER

Case 2302 'Приложению не удается сохранить выводимые данные в выбранном файле.

MsgBox "Нельзя выгрузить данные в Excel." & vbNewLine & _

"Возможно файл уже открыт!", vbCritical, Me.Caption

Resume Next

Case Else: MsgBox Err.DESCRIPTION, vbCritical, "Ошибка: " & Err.NUMBER: Exit Sub

Resume

End Select

End Sub

Private Sub DATE_BEG_AfterUpdate()

If Me![DATE_BEG] > Me![DATE_END] Then Me![DATE_END] = Me![DATE_BEG]

UpdateDate

End Sub

Private Sub DATE_END_BeforeUpdate(Cancel As Integer)

If Me![DATE_BEG] > Me![DATE_END] Then Me![DATE_BEG] = Me![DATE_END]

UpdateDate

End Sub

Private Sub Form_Load()

Dim q() As String, i%, db As String, DE As String

Me![DATE_BEG] = DATE - 30

Me![DATE_END] = DATE

UpdateDate

End Sub

Private Sub UpdateDate()

Dim q() As String, i%, db As String, DE As String

db = SQL_date(Me![DATE_BEG])

DE = SQL_date(Me![DATE_END])

i = 1: ReDim q(1 To 1)

q(i) = "DELETE Т_обеды_свод.* FROM Т_обеды_свод"

i = i + 1: ReDim Preserve q(1 To i)

q(i) = "DELETE Tmp.* FROM Tmp"

i = i + 1: ReDim Preserve q(1 To i)

q(i) = "INSERT INTO Tmp ( LngVal, DateVal, LngVal1 )" & vbNewLine

q(i) = q(i) & "SELECT DISTINCTROW Заказ_обедов.KOD_FL, Заказ_обедов.DATE, Count(Заказ_обедов.DATE)" & vbNewLine

q(i) = q(i) & "FROM Заказ_обедов" & vbNewLine

q(i) = q(i) & "WHERE Заказ_обедов.[DATE] Between " & db & " And " & DE & vbNewLine

q(i) = q(i) & "GROUP BY Заказ_обедов.KOD_FL, Заказ_обедов.DATE"

i = i + 1: ReDim Preserve q(1 To i)

q(i) = "INSERT INTO Т_обеды_свод ( KOD_FL, OBED_ALL, OBED_DAY, 1, 2, 3, 4 )" & vbNewLine

q(i) = q(i) & "SELECT Заказ_обедов.KOD_FL, CNT.ALLOBED-CNT.DAYOBED, CNT.DAYOBED, Sum(-[SALAT]), Sum(-[PERVOE]), Sum(-[VTOROE]), Sum(-[VIPECHKA])" & vbNewLine

q(i) = q(i) & "FROM Заказ_обедов INNER JOIN " & vbNewLine

q(i) = q(i) & " (SELECT Tmp.LngVal as KOD_FL, Sum(Tmp.LngVal1) AS [ALLOBED], Count(Tmp.DateVal) AS [DAYOBED]" & vbNewLine

q(i) = q(i) & " FROM Tmp " & vbNewLine

q(i) = q(i) & " GROUP BY Tmp.LngVal) AS CNT ON Заказ_обедов.KOD_FL = CNT.KOD_FL" & vbNewLine

q(i) = q(i) & "WHERE Заказ_обедов.[DATE] Between " & db & " And " & DE & vbNewLine

q(i) = q(i) & "GROUP BY Заказ_обедов.KOD_FL, CNT.ALLOBED, CNT.DAYOBED"

i = i + 1: ReDim Preserve q(1 To i)

q(i) = "INSERT INTO Т_обеды_свод ( KOD_FL, OBED_ALL, OBED_DAY, 1, 2, 3, 4, ORD )" & vbNewLine

q(i) = q(i) & "SELECT 0, Sum(Т_обеды_свод.OBED_ALL), Sum(Т_обеды_свод.OBED_DAY), Sum(Т_обеды_свод.[1]), Sum(Т_обеды_свод.[2]), Sum(Т_обеды_свод.[3]), Sum(Т_обеды_свод.[4]), 1 " & vbNewLine

q(i) = q(i) & "FROM Т_обеды_свод" & vbNewLine

q(i) = q(i) & "GROUP BY 0, 1"

If Not ExecuteTrans("формируем отчет", q) Then Exit Sub

Me![Отчет_по_обедам_SF].Requery

End Sub

Private Sub Form_Resize()

StdResize Me![Отчет_по_обедам_SF], Me

End Sub

Private Sub PRICE_AfterUpdate()

Me![Отчет_по_обедам_SF].Requery

End Sub