Private Sub btnGenerate_Click()
Dim sSQL1 As String
Dim sSQL2 As String
Dim sSQL3 As String
Dim sSQL4 As String
Dim sFinalSQL As String
'==============
If Me.frmPaymentType = 1 Then
sSQL1 = "SELECT tblCases.CaseID, StrConv([title] & ' ' & [forename] & ' ' & [surename],3) AS Client, tblPayments.Amount, tblCases.Consultant, tblPayments.Date FROM tblCustomers INNER JOIN (tblCases INNER JOIN tblPayments ON tblCases.CaseID = tblPayments.CaseID) ON tblCustomers.CustomerID = tblCases.CustomerID"
ElseIf Me.frmPaymentType = 2 Then
sSQL1 = "SELECT tblCases.CaseID, StrConv([title] & ' ' & [forename] & ' ' & [surename],3) AS Client, tblInvoicedCharges.Invoice AS [Invoice No], tblInvoicedCharges.Net, tblInvoicedCharges.Date, tblCases.Consultant FROM tblCustomers INNER JOIN (tblInvoicedCharges INNER JOIN tblCases ON tblInvoicedCharges.CaseID = tblCases.CaseID) ON tblCustomers.CustomerID = tblCases.CustomerID"
End If
If Me.cbxDateType = "Quick Date Range" Then
Select Case Me.cbxDateTypeSelection
Case "Today"
sSQL2 = " WHERE Left([Date],10)=Left(Now(),10)"
Case "Yesterday"
sSQL2 = " WHERE Left([Date],10)=Left(Now()-1,10)"
Case "Last 7 Days"
sSQL2 = " WHERE (((CDate(Left([Date],10)))>CDate(Left(Now()-7,10))))"
Case "This Month"
sSQL2 = " WHERE (((CDate(Left([Date],10)))>=CDate('01/' & Month(Now()) & '/2009') And (CDate(Left([Date],10)))<CDate('01/' & Month(Now())+1 & '/2009')))"
Case "Last Month"
sSQL2 = " WHERE (((CDate(Left([Date],10)))>=CDate('01/' & Month(Now())-1 & '/2009') And (CDate(Left([Date],10)))<CDate('01/' & Month(Now()) & '/2009')))"
Case "All Time"
sSQL2 = " Where"
End Select
ElseIf Me.cbxDateType = "Exact Date Range" Then
If (Me.tbxFirstDate = "" Or IsNull(Me.tbxFirstDate)) Or (Me.tbxSecondDate = "" Or IsNull(Me.tbxSecondDate)) Then
MsgBox "Please supply both the From Date and the To Date."
Exit Sub
End If
sSQL2 = " WHERE (((CDate(Left([Date],10)))>=CDate([Forms]![frmReports]![tbxFirstDate]) And (CDate(Left([Date],10)))<=CDate([Forms]![frmReports]![tbxSecondDate])))"
End If
'==============
If Me.cbxDateTypeSelection = "All Time" And Me.cbxFor = "Capital Visas" Then
sSQL3 = ""
ElseIf Me.cbxDateTypeSelection = "All Time" Then
sSQL3 = " tblCases.Consultant =" & "'" & Me.cbxFor & "'"
ElseIf Me.cbxFor <> "Capital Visas" Then
sSQL3 = " And tblCases.Consultant =" & "'" & Me.cbxFor & "'"
Else: sSQL3 = ""
End If
If Me.frmPaymentType = 1 Then
sSQL4 = " ORDER BY tblPayments.Date"
ElseIf Me.frmPaymentType = 2 Then
sSQL4 = " ORDER BY tblInvoicedCharges.Date"
End If
Select Case Me.frmPaymentType
'Cash
Case 1
If Me.cbxDateType = "Quick Date Range" Then
If Me.cbxFor = "Capital Visas" And Me.cbxDateTypeSelection = "All Time" Then
sFinalSQL = sSQL1 & sSQL3 & sSQL4
Else: sFinalSQL = sSQL1 & sSQL2 & sSQL3 & sSQL4
End If
ElseIf Me.cbxDateType = "Exact Date Range" Then
sFinalSQL = sSQL1 & sSQL2 & sSQL4
End If
DoCmd.OpenReport "rptCashIn", acViewReport
Reports![rptCashIn].RecordSource = sFinalSQL
Forms!frmReports.Visible = False
Me.tbxMemo = sFinalSQL
'Invoice
Case 2
If Me.cbxDateType = "Quick Date Range" Then
If Me.cbxFor = "Capital Visas" And Me.cbxDateTypeSelection = "All Time" Then
sFinalSQL = sSQL1 & sSQL3 & sSQL4
Else: sFinalSQL = sSQL1 & sSQL2 & sSQL3 & sSQL4
End If
ElseIf Me.cbxDateType = "Exact Date Range" Then
sFinalSQL = sSQL1 & sSQL2 & sSQL4
End If
DoCmd.OpenReport "rptInvoiceIn", acViewReport
Reports![rptInvoiceIn].RecordSource = sFinalSQL
Forms!frmReports.Visible = False
Me.tbxMemo = sFinalSQL
End Select
End Sub