将NULL替换为zero

Question

我试图创建一个VBA例程从MS Access数据库表导出一些数据。数据由另一个系统使用，因此需要采用特定的格式，类似于PIVOT表。
代码运行良好，但有一个问题。一些字段后正在返回NULL值变换命令用于创建数据透视表。下面是查询VW_DEMAND_XL

的SQL语句

TRANSFORM FIRST([VALUE])
SELECT STRLOC AS ROWNAMES, [DESC] AS [TEXT], CODE
FROM TB_DEMAND_PVT
GROUP BY STRLOC, [DESC], CODE
PIVOT [PARAMETER] & PERIOD;

周期的个数是可变的。
此查询由执行导出部分的VBA代码调用:

Private Sub BTN_EXPORTA_DEMAND_Click()
'https://btabdevelopment.com/export-tablequery-to-excel-to-new-named-sheet/
'
Dim rst As DAO.Recordset
Dim ApXL As Object
Dim xlWBk As Object
Dim xlWSh As Object
Dim fld As DAO.Field
Const xlCenter As Long = -4108
Const xlBottom As Long = -4107
On Error GoTo err_handler

Dim SFile As String
Dim SName As String
Dim QueryNM As String
Dim SheetNM As String
Dim TableNM As String

TableNM = "TB_DEMAND_XL"

'Verifica se a tabela existe. Se existir, ela é eliminada.
Importa.DeleteIfExists (TableNM)

'Desliga os avisos
DoCmd.SetWarnings False
'Executa a consulta e cria a tabela
DoCmd.OpenQuery "CT_DEMAND_PVT"
'Liga os Avisos
DoCmd.SetWarnings True

QueryNM = "VW_DEMAND_XL"
SheetNM = "DEMAND"

SPath = Application.CurrentProject.Path
DH = Format(Now, "ddmmyyyy_hhmmss")
SFile = "" & SheetNM & "_" & DH & ".xlsx"
SName = SPath & SFile

Set rst = CurrentDb.OpenRecordset(QueryNM)
'NULL values check was put here'
Set ApXL = CreateObject("Excel.Application")
'Adiciona o arquivo Excel de destino
Set xlWBk = ApXL.Workbooks.Add

ApXL.Visible = False
'Salva o arquivo com o nome desejado
xlWBk.SaveAs FileName:=SName

xlWBk.Worksheets("Planilha1").Name = SheetNM
Set xlWSh = xlWBk.Worksheets(SheetNM)
xlWSh.Activate
'Cria os indícies da tabela
xlWSh.Range("A1") = "TABLE"
xlWSh.Range("B1") = "DEMAND"
xlWSh.Range("A2") = "*"

'Seleciona a primeira célula
xlWSh.Range("A3").Select
'Cola os rótulos
For Each fld In rst.Fields
ApXL.ActiveCell = fld.Name
ApXL.ActiveCell.Offset(0, 1).Select
Next
xlWSh.Range("C3") = "!CODE"

rst.MoveFirst
'Seleciona a próxima linha e cola os dados
xlWSh.Range("A4").CopyFromRecordset rst
xlWSh.Range("3:3").Select
' This is included to show some of what you can do about formatting.  You can comment out or delete
' any of this that you don't want to use in your own export.
With ApXL.Selection.Font
.Name = "Arial"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
End With
ApXL.Selection.Font.Bold = True
With ApXL.Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
' selects all of the cells
ApXL.ActiveSheet.Cells.Select
' does the "autofit" for all columns
ApXL.ActiveSheet.Cells.EntireColumn.AutoFit
' selects the first cell to unselect all cells
xlWSh.Range("A1").Select

xlWBk.Save
xlWBk.Close
rst.Close
Set rst = Nothing

MsgBox ("Arquivo exportado!")
Exit_SendTQ2XLWbSheet:
Exit Sub
err_handler:
DoCmd.SetWarnings True
MsgBox Err.Description, vbExclamation, Err.Number
Resume Exit_SendTQ2XLWbSheet
End Sub

CT_DEMAND_PVT是一个查询，它以适当的格式创建一个表，以使用TRANSFORM命令。

我尝试使用下面的代码来处理NULL值，就在Set rst = CurrentDb.OpenRecordset(QueryNM)之后。

With rst

.MoveFirst
Dim objfield
Do While Not .EOF
For Each objfield In .Fields
If IsNull(objfield.Value) Then
.Edit
objfield.Value = 0
.Update
End If
Next objfield
.MoveNext
Loop
.MoveFirst
End With

但是当有NULL值时，我有一个运行时错误3027(无法更新。数据库或对象为只读)。

谁能指出我做错了什么?有可能做我想做的事吗?
敬祝

Answer 1

您的底层记录集不可更新，并且您的Null处理代码正试图实际更改记录集中字段的值。正如@Nathan_Sav所建议的，当遇到null时，使用Nz()函数返回0。