Remove records with 0 values

[riaarora]

Hi All,

I am writing data from Access to excel. There are few records with 0 values. How to remove those records?

Below the code used to export data from Access to Excel.

Dim TempString As String

    Set objXLApp = CreateObject("Excel.Application")
    wb = "F:\MyDoc\Weekly MI Automation\" & sTeamRegionName & Left(Date, 2) & Mid(Date, 4, 2) & Right(Date, 4) & "_" & Left(Time, 2) & Mid(Time, 4, 2) & ".xlsx"
    
    Set objXLWorkbook = objXLApp.Workbooks.Add 'Will Create a new workbook
    Set objXLSheet = objXLWorkbook.Worksheets(1) 'Will create a new worksheet
    'objXLSheet.Name = sTeamRegionName 'Rename the worksheet
    'Set Wkb = AppExcel.Workbooks.Open(excelname)
    
    objXLApp.Visible = True 'Makes the spreasheet visible. False will let you open it behind the scenes

    For count = 1 To recount
        ColumnStart = 1
        objXLSheet.Cells(RowStart, ColumnStart) = rsTmp![Banker]
        ColumnStart = ColumnStart + 1
        objXLSheet.Cells(RowStart, ColumnStart) = rsTmp![RevenueYTD]
        ColumnStart = ColumnStart + 1
        objXLSheet.Cells(RowStart, ColumnStart) = rsTmp![AnnuRevenue]
        ColumnStart = ColumnStart + 1
        objXLSheet.Cells(RowStart, ColumnStart) = rsTmp![RevenueTCY]
        ColumnStart = ColumnStart + 1
        objXLSheet.Cells(RowStart, ColumnStart) = rsTmp![RevenuePFY]
        ColumnStart = ColumnStart + 1
        objXLSheet.Cells(RowStart, ColumnStart) = rsTmp![AnnuVsBud]
        ColumnStart = ColumnStart + 1
        objXLSheet.Cells(RowStart, ColumnStart) = rsTmp![AnnuVsPFYRev]
        ColumnStart = ColumnStart + 1
        ..............
        ..............
        ..............
        ..............
        ..............

        ColumnStart = ColumnStart + 1
        objXLSheet.Cells(RowStart, ColumnStart) = rsTmp![AnnuActRatio]

        If rsTmp.EOF Then Exit For
        RowStart = RowStart + 1
        rsTmp.MoveNext
    Next count

    rsTmp.Close
    Set rsTmp = Nothing
    
    'For double top line and single bottom line for Subtotal
    TempString = "A" & RowStart & ":" & "T" & RowStart
    boldFontSize (TempString)
    applyDoubleTotalLine (TempString)

objXLSheet.range("RowEnd").Select
    objXLSheet.ActiveCell.Formula = "= SUBTOTAL(101, B" & RowEnd & ":B" & recount + 6 & ")"

[seenu_1st]

why dont u avoid 0 values when u open recordset by query?

[riaarora]

Hi Seenu,

I'll prefer to remove from the result set only but now sure how to do that? I please send me the sample for the same.

Please fine below code used for result set

    strSelectSQL = "SELECT " & _
        "[Banker], " & _
        "[Income YTD], [AnnRevenue],  " & _
        "[VRevenueTargetCY], [RevenuePriorFY],  " & _
        "[VAnn vs Budget], [VAnnu vs Prior FY Revenue],  " & _
        "[Income YTD], [Ann ARevenue],  " & _
        "[ARevenueTargetCY], [ARevenuePriorFY],  " & _
	...
	...
	...
	...
	"[AnnuVRevenue] / [TotalAnnuRevenue] AS [Annu Actual V Ratio] " & _
        "FROM tbl_WConsol  " & _
        "INNER JOIN tbl_Bankers ON tbl_WConsol.[Banker] = tbl_Bankers.[BankerNm] " & _
        "WHERE [YearMonthWeek] = '" & txtReportingMonth & CStr(gsReportingWeekNo) & "'" & _
        "AND Team = '" & sTeamRegionName & "'"

         Set rsTmp = gsPnPDatabase.OpenRecordset(strSelectSQL)

    recount = rsTmp.RecordCount
    rsTmp.MoveFirst
    RowEnd = RowStart + recount


    For count = 1 To recount
        ColumnStart = 1
        objXLSheet.Cells(RowStart, ColumnStart) = rsTmp![Banker]
        ColumnStart = ColumnStart + 1
        objXLSheet.Cells(RowStart, ColumnStart) = rsTmp![RevenueYTD]
        ColumnStart = ColumnStart + 1
        objXLSheet.Cells(RowStart, ColumnStart) = rsTmp![AnnuRevenue]
        ColumnStart = ColumnStart + 1
        objXLSheet.Cells(RowStart, ColumnStart) = rsTmp![RevenueTCY]
        ColumnStart = ColumnStart + 1
        objXLSheet.Cells(RowStart, ColumnStart) = rsTmp![RevenuePFY]
        ColumnStart = ColumnStart + 1
        objXLSheet.Cells(RowStart, ColumnStart) = rsTmp![AnnuVsBud]
        ColumnStart = ColumnStart + 1
        objXLSheet.Cells(RowStart, ColumnStart) = rsTmp![AnnuVsPFYRev]
        ColumnStart = ColumnStart + 1
        ..............
        ..............
        ..............
        ..............
        ..............

        ColumnStart = ColumnStart + 1
        objXLSheet.Cells(RowStart, ColumnStart) = rsTmp![AnnuActRatio]

        If rsTmp.EOF Then Exit For
        RowStart = RowStart + 1
        rsTmp.MoveNext
    Next count

    rsTmp.Close
    Set rsTmp = Nothing

[seenu_1st]

something like this

"Select * From Tablename Where Column1 <> 0"

[westconn1]

There are few records with 0 values.

0 value in a specific field or all fields blank?