Code to create a report without zero values

**Deenah** · 11-08-2011, 02:28 PM

Hi,

I have the following code in Access database.It creates a pdf file with YTD and Quarterly numbers. If the Quarterly value is zero, I would like to create a report where it does not show up that quarter number. What can I add to the following code see only non zero numbers.

Thanks,
Deenah

Sub Calculate_YTD_Payments_1()

    DoCmd.SetWarnings False
    
    DoCmd.RunSQL "UPDATE Lease_Turbine SET Lease_Turbine.[Net Nominal Rent] = [Lease_Turbine]![Ownership %]*[Lease_Turbine]![Nominal_Rent]"
    
    DoCmd.RunSQL "UPDATE Lease_Turbine SET Lease_Turbine.[Vendor Code] = UCase([Lease_Turbine]![Vendor Code])"
    
    
    If IsNull([Forms]![frm_Control]![Calc_Year_YTD].Value) Then
        MsgBox "Please select the year in the dropdown box above for which you would like to calculate royalty payments."
        Exit Sub
    End If

    If IsNull([Forms]![frm_Control]![Calc_Qtr_YTD].Value) Then
        MsgBox "Please select the quarter in the dropdown box above for which you would like to calculate royalty payments."
        Exit Sub
    End If
            
            
    DoCmd.RunSQL "SELECT Lease_Turbine.Year, Lease_Turbine.Quarter, Lease_Turbine.LeaseNumber, Lease_Turbine.TurbineID," & _
        " Sum(Lease_Turbine.[Ownership %]) AS [SumOfOwnership %]" & _
        " INTO LT_CheckOP" & _
        " FROM Lease_Turbine" & _
        " GROUP BY Lease_Turbine.Year, Lease_Turbine.Quarter, Lease_Turbine.LeaseNumber, Lease_Turbine.TurbineID" & _
        " HAVING (((Sum(Lease_Turbine.[Ownership %]))<0.999999999 Or (Sum(Lease_Turbine.[Ownership %]))>1.0000001))"
        
    If Nz(DCount("*", "[LT_CheckOP]")) <> 0 Then
        MsgBox "Ownership % in one or more leases in the Lease_Turbine table do not add up to 100%.  Please check the Ownership_Percentage_Check.xls file" & _
            " on your desktop to identify the issues, export the Lease_Turbine table on the Landowner Data Management tab, fix any issues, and try again."
        
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "LT_CheckOP", "C:\Program Files\Royalty Database\Database Exports\Ownership_Percentage_Check.xls", True
        Exit Sub
    End If
            
    'DoCmd.RunSQL "SELECT Lease_Turbine.Year, Lease_Turbine.Quarter, Lease_Turbine.LeaseNumber, Lease_Turbine.TurbineID, Sum(Lease_Turbine.[Net Nominal Rent]) AS [SumOfNet Nominal Rent]" & _
        " INTO LT_Check_NomRnt" & _
        " FROM Lease_Turbine" & _
        " WHERE Lease_Turbine.Year = Forms!frm_Control!CalcYear AND Lease_Turbine.Quarter = Forms!frm_Control!CalcQuarter" & _
        " GROUP BY Lease_Turbine.Year, Lease_Turbine.Quarter, Lease_Turbine.LeaseNumber, Lease_Turbine.TurbineID" & _
        " HAVING (((Sum(Lease_Turbine.[Net Nominal Rent]))<2999 Or (Sum(Lease_Turbine.[Net Nominal Rent]))>3001))"
   
   
    'If Nz(DCount("*", "[LT_Check_NomRnt]")) <> 0 Then
    '    MsgBox "Net Nominal Rent in one or more leases in the Lease_Turbine table do not add up to $3,000.  Please check the Nominal_Rent_Check.xls file" & _
            " on your desktop to identify the issues, export the Lease_Turbine table on the Landowner Data Management tab, fix any issues, and try again."
 
    '    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "LT_Check_NomRnt", "C:\Program Files\Royalty Database\Database Exports\Nominal_Rent_Check.xls", True
    '    Exit Sub
    'End If
    
    
    DoCmd.RunSQL "SELECT Lease_Turbine.Year, Lease_Turbine.Quarter, Lease_Turbine.TurbineID, Lease_Turbine.LeaseNumber, Lease_Turbine.Name," & _
        " Lease_Turbine.Address, Lease_Turbine.City, Lease_Turbine.State, Lease_Turbine.ZipCode, Lease_Turbine.[Ownership %]," & _
        " Lease_Turbine.Nominal_Rent, Lease_Turbine.[Net Nominal Rent], Lease_Turbine.Royalty, Lease_Turbine.[Net Royalty]," & _
        " Lease_Turbine.Nameplate_Payment, Lease_Turbine.[Net Nameplate Payment], Lease_Turbine.[Turbine Size], Lease_Turbine.[Minimun Royalty]," & _
        " Lease_Turbine.[Net Minimum Royalty], Lease_Turbine.[Check To], Lease_Turbine.[Vendor Code]" & _
        " INTO LT_CheckNullVC" & _
        " FROM Lease_Turbine" & _
        " WHERE (((Lease_Turbine.[Vendor Code]) Is Null))"
        
    If Nz(DCount("*", "[LT_CheckNullVC]")) <> 0 Then
        MsgBox "One or more payees in the Lease_Turbine table is missing a vendor code.  Please check the table by exporting it on" & _
        " the Landowner Data Management tab, fix any issues, and try again."
        Exit Sub
    End If
            
    
    Dim Msg, Style, Title, Help, Ctxt, Response, MyString
    
    Msg = "About to calculate payments for the selected year and quarter.  Click YES to continue or NO to cancel."
    Style = vbYesNo + vbCritical + vbDefaultButton2    ' Define buttons.
    Title = "Message..."    ' Define title.
    Help = "DEMO.HLP"    ' Define Help file.
    Ctxt = 1000    ' Define topic
        ' context.
        ' Display message.
    Response = MsgBox(Msg, Style, Title, Help, Ctxt)
    If Response = vbYes Then    ' User chose Yes.
        MyString = "Yes"    ' Perform some action.
    Else    ' User chose No.
        MyString = "No"    ' Perform some action.
        MsgBox "YTD calculation canceled."
        Exit Sub
    End If

**arthurbr** · 11-08-2011, 02:44 PM

You also posted an Access question in an Excel forum. I will move it for you this time

**Deenah** · 11-08-2011, 04:06 PM

Sorry I am new at the forum. I will read the rules and comply going forward.

**davegugg** · 11-09-2011, 10:15 AM

You posted way too much code to decipher. Can you just post the single SQL statement that is bringing in the zeros you don't want to see?

In general, you can specify in the WHERE clause that you want to exclude results = 0. It'd be something like:

SELECT * FROM table WHERE QuarterlyData <> 0

**Deenah** · 11-09-2011, 12:15 PM

Hi Dave,

Thanks for your reply. Can you please tell me where I should put the Where code. I am reposting the code where I need to create a statement without zero amounts.

Sub Create_Statements()


    DoCmd.SetWarnings False
    
            If IsNull([Forms]![frm_Control]![Statements_Year].Value) Then
                MsgBox "Please select the year in the dropdown box above for which you would like to create statements."
                Exit Sub
            End If
    
            If IsNull([Forms]![frm_Control]![Statements_Qtr].Value) Then
                MsgBox "Please select the quarter in the dropdown box above for which you would like to create statements."
                Exit Sub
            End If
            
    If Nz(DCount("[Quarter]", "[Payment_History]", "[Quarter] = [Forms]![frm_Control]![Statements_Qtr]")) = 0 Or Nz(DCount("[Year]", "[Payment_History]", "[Year] = [Forms]![frm_Control]![Statements_Year]")) = 0 Then
        MsgBox "OPERATION ABORTED.  The Year and Quarter specified are not contained in the Payment_History table.  Please select a valid Year/Quarter combination."
        Exit Sub
    End If
            
            
            

            
    
    Dim Msg, Style, Title, Help, Ctxt, Response, MyString
    
    Msg = "About to run statements for the year and quarter specified, based on the Payment_History archive table.  Click YES to continue or NO to cancel."
    Style = vbYesNo + vbCritical + vbDefaultButton2    ' Define buttons.
    Title = "Message..."    ' Define title.
    Help = "DEMO.HLP"    ' Define Help file.
    Ctxt = 1000    ' Define topic
        ' context.
        ' Display message.
    Response = MsgBox(Msg, Style, Title, Help, Ctxt)
    If Response = vbYes Then    ' User chose Yes.
        MyString = "Yes"    ' Perform some action.
    Else    ' User chose No.
        MyString = "No"    ' Perform some action.
        MsgBox "Statement creation canceled."
        Exit Sub
    End If




 DoCmd.RunSQL "SELECT Payment_History.Year AS Statements_Year, [Forms]![frm_Control]![Statements_Qtr]*1 AS Statement_Quarter, Payment_History.[Check To], Payment_History.Name, Payment_History.Address, Payment_History.City, Payment_History.State, Payment_History.ZipCode, Payment_History.LeaseNumber, Payment_History.TurbineID, Sum(Payment_History.YTD_MWHrs) AS YTD_MWHrs, Sum(Payment_History.YTD_Gross_Revenue) AS YTD_Gross_Revenue, Sum(Payment_History.YTD_Minimum_Payment) AS YTD_Minimum_Payment, Sum(Payment_History.YTD_Royalty_Earned) AS YTD_Royalty_Earned, Sum(Payment_History.YTD_Payment_Earned) AS YTD_Payment_Earned INTO Statements_Earned_YTD" & _
        " FROM Payment_History" & _
        " WHERE (((Payment_History.Quarter) <= [Forms]![frm_Control]![Statements_Qtr]))" & _
        " GROUP BY Payment_History.Year, [Forms]![frm_Control]![Statements_Qtr]*1, Payment_History.[Check To], Payment_History.Name, Payment_History.Address, Payment_History.City, Payment_History.State, Payment_History.ZipCode, Payment_History.LeaseNumber, Payment_History.TurbineID" & _
        " HAVING (((Payment_History.Year)=[Forms]![frm_Control]![Statements_Year]*1))"

    DoCmd.RunSQL "SELECT Payment_History.Year AS Statements_Year, [Forms]![frm_Control]![Statements_Qtr]*1 AS Statement_Quarter, Payment_History.[Check To], Payment_History.Name, Payment_History.Address, Payment_History.City, Payment_History.State, Payment_History.ZipCode, Payment_History.LeaseNumber, Payment_History.TurbineID, Sum(Payment_History.YTD_Payment_Earned) AS Current_Quarter_Payment_Earned INTO Statements_Curr_Qtr_Pmt" & _
        " FROM Payment_History" & _
        " WHERE (((Payment_History.Quarter) = [Forms]![frm_Control]![Statements_Qtr]))" & _
        " GROUP BY Payment_History.Year, [Forms]![frm_Control]![Statements_Qtr]*1, Payment_History.[Check To], Payment_History.Name, Payment_History.Address, Payment_History.City, Payment_History.State, Payment_History.ZipCode, Payment_History.LeaseNumber, Payment_History.TurbineID" & _
        " HAVING (((Payment_History.Year)=[Forms]![frm_Control]![Statements_Year]*1))"
        
    DoCmd.RunSQL "SELECT Payment_History.Year AS Statements_Year, [Forms]![frm_Control]![Statements_Qtr]*1 AS Statement_Quarter, Payment_History.[Check To], Payment_History.Name, Payment_History.Address, Payment_History.City, Payment_History.State, Payment_History.ZipCode, Payment_History.LeaseNumber, Payment_History.TurbineID, Sum(Payment_History.YTD_Payment_Earned) AS YTD_Payments_Made INTO Statements_Payments_Made_YTD" & _
        " FROM Payment_History" & _
        " WHERE (((Payment_History.Quarter) <= [Forms]![frm_Control]![Statements_Qtr] - 1))" & _
        " GROUP BY Payment_History.Year, [Forms]![frm_Control]![Statements_Qtr]*1, Payment_History.[Check To], Payment_History.Name, Payment_History.Address, Payment_History.City, Payment_History.State, Payment_History.ZipCode, Payment_History.LeaseNumber, Payment_History.TurbineID" & _
        " HAVING (((Payment_History.Year)=[Forms]![frm_Control]![Statements_Year]*1))"
        
    DoCmd.Close acReport, "Royalty_Payments_Statements_YTD"
        
    DoCmd.RunSQL "SELECT Statements_Earned_YTD.Statements_Year, Statements_Earned_YTD.Statement_Quarter, Statements_Earned_YTD.[Check To], Statements_Earned_YTD.Name, Statements_Earned_YTD.Address, Statements_Earned_YTD.City, Statements_Earned_YTD.State, Statements_Earned_YTD.ZipCode, Statements_Earned_YTD.LeaseNumber, Statements_Earned_YTD.TurbineID, Statements_Earned_YTD.YTD_MWHrs, Statements_Earned_YTD.YTD_Gross_Revenue, Statements_Earned_YTD.YTD_Minimum_Payment, Statements_Earned_YTD.YTD_Royalty_Earned, Statements_Earned_YTD.YTD_Payment_Earned, Statements_Payments_Made_YTD.YTD_Payments_Made, Statements_Curr_Qtr_Pmt.Current_Quarter_Payment_Earned INTO Statements_FINAL" & _
        " FROM (Statements_Earned_YTD LEFT JOIN Statements_Curr_Qtr_Pmt ON (Statements_Earned_YTD.TurbineID = Statements_Curr_Qtr_Pmt.TurbineID) AND (Statements_Earned_YTD.LeaseNumber = Statements_Curr_Qtr_Pmt.LeaseNumber) AND (Statements_Earned_YTD.[Check To] = Statements_Curr_Qtr_Pmt.[Check To]) AND (Statements_Earned_YTD.Statement_Quarter = Statements_Curr_Qtr_Pmt.Statement_Quarter) AND (Statements_Earned_YTD.Statements_Year = Statements_Curr_Qtr_Pmt.Statements_Year)) LEFT JOIN Statements_Payments_Made_YTD ON (Statements_Earned_YTD.Statements_Year = Statements_Payments_Made_YTD.Statements_Year) AND (Statements_Earned_YTD.Statement_Quarter = Statements_Payments_Made_YTD.Statement_Quarter) AND (Statements_Earned_YTD.[Check To] = Statements_Payments_Made_YTD.[Check To]) AND (Statements_Earned_YTD.LeaseNumber = Statements_Payments_Made_YTD.LeaseNumber) AND (Statements_Earned_YTD.TurbineID = Statements_Payments_Made_YTD.TurbineID)"

    DoCmd.OpenReport "Royalty_Payments_Statements_YTD", acViewPreview

    
    MsgBox "Statements have been created.  Please review carefully, and if satisfied with the results, print to send with checks.

Thanks,
deenah

**davegugg** · 11-09-2011, 12:20 PM

deenah, as I said before there is WAY too much code there to make sense of any of it. If you don't understand your code, you need to find a way to learn what it is doing. You have several SQL statements there, and there is no way I can know which one(s) need to be changed.

If you can post your db I may be able to figure it out, but otherwise I don't think I can help you.

**Deenah** · 11-09-2011, 12:50 PM

I am sorry. But I think I need to make a change somewhere in the following code. where there is WHERE command.

 DoCmd.RunSQL "SELECT Payment_History.Year AS Statements_Year, [Forms]![frm_Control]![Statements_Qtr]*1 AS Statement_Quarter, Payment_History.[Check To], Payment_History.Name, Payment_History.Address, Payment_History.City, Payment_History.State, Payment_History.ZipCode, Payment_History.LeaseNumber, Payment_History.TurbineID, Sum(Payment_History.YTD_MWHrs) AS YTD_MWHrs, Sum(Payment_History.YTD_Gross_Revenue) AS YTD_Gross_Revenue, Sum(Payment_History.YTD_Minimum_Payment) AS YTD_Minimum_Payment, Sum(Payment_History.YTD_Royalty_Earned) AS YTD_Royalty_Earned, Sum(Payment_History.YTD_Payment_Earned) AS YTD_Payment_Earned INTO Statements_Earned_YTD" & _
        " FROM Payment_History" & _
        " WHERE (((Payment_History.Quarter) <= [Forms]![frm_Control]![Statements_Qtr]))" & _
        " GROUP BY Payment_History.Year, [Forms]![frm_Control]![Statements_Qtr]*1, Payment_History.[Check To], Payment_History.Name, Payment_History.Address, Payment_History.City, Payment_History.State, Payment_History.ZipCode, Payment_History.LeaseNumber, Payment_History.TurbineID" & _
        " HAVING (((Payment_History.Year)=[Forms]![frm_Control]![Statements_Year]*1))"

    DoCmd.RunSQL "SELECT Payment_History.Year AS Statements_Year, [Forms]![frm_Control]![Statements_Qtr]*1 AS Statement_Quarter, Payment_History.[Check To], Payment_History.Name, Payment_History.Address, Payment_History.City, Payment_History.State, Payment_History.ZipCode, Payment_History.LeaseNumber, Payment_History.TurbineID, Sum(Payment_History.YTD_Payment_Earned) AS Current_Quarter_Payment_Earned INTO Statements_Curr_Qtr_Pmt" & _
        " FROM Payment_History" & _
        " WHERE (((Payment_History.Quarter) = [Forms]![frm_Control]![Statements_Qtr]))" & _
        " GROUP BY Payment_History.Year, [Forms]![frm_Control]![Statements_Qtr]*1, Payment_History.[Check To], Payment_History.Name, Payment_History.Address, Payment_History.City, Payment_History.State, Payment_History.ZipCode, Payment_History.LeaseNumber, Payment_History.TurbineID" & _
        " HAVING (((Payment_History.Year)=[Forms]![frm_Control]![Statements_Year]*1))"
        
    DoCmd.RunSQL "SELECT Payment_History.Year AS Statements_Year, [Forms]![frm_Control]![Statements_Qtr]*1 AS Statement_Quarter, Payment_History.[Check To], Payment_History.Name, Payment_History.Address, Payment_History.City, Payment_History.State, Payment_History.ZipCode, Payment_History.LeaseNumber, Payment_History.TurbineID, Sum(Payment_History.YTD_Payment_Earned) AS YTD_Payments_Made INTO Statements_Payments_Made_YTD" & _
        " FROM Payment_History" & _
        " WHERE (((Payment_History.Quarter) <= [Forms]![frm_Control]![Statements_Qtr] - 1))" & _
        " GROUP BY Payment_History.Year, [Forms]![frm_Control]![Statements_Qtr]*1, Payment_History.[Check To], Payment_History.Name, Payment_History.Address, Payment_History.City, Payment_History.State, Payment_History.ZipCode, Payment_History.LeaseNumber, Payment_History.TurbineID" & _
        " HAVING (((Payment_History.Year)=[Forms]![frm_Control]![Statements_Year]*1))"

**davegugg** · 11-09-2011, 01:00 PM

When yuo are using aggregate functions, the HAVING takes the place of the WHERE clause. So for those three statements, add something like
AND Statement_Quarter <> 0.

Additionally, if this is being displayed on a report you can use conditional formatting to hide any zero values.

**Deenah** · 11-09-2011, 01:46 PM

Thanks Dave. I figured out where I had to make the change.

Regards,
Deenah

**Deenah** · 11-10-2011, 11:58 AM

Thanks again Dave for the solution.

Thread: Code to create a report without zero values

Thread Tools

Rate This Thread

Display

Code to create a report without zero values

Re: Code to create a report without zero values

Re: Code to create a report without zero values

Re: Code to create a report without zero values

Re: Code to create a report without zero values

Re: Code to create a report without zero values

Re: Code to create a report without zero values

Re: Code to create a report without zero values

Re: Code to create a report without zero values

Re: Code to create a report without zero values

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions