Remove records with 0 values

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.

Code:

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 & ")"

Re: Remove records with 0 values

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

Re: Remove records with 0 values

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

Code:

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

Re: Remove records with 0 values

something like this

Code:

"Select * From Tablename Where Column1 <> 0"

Re: Remove records with 0 values

Quote:

There are few records with 0 values.

0 value in a specific field or all fields blank?