表达式中未定义的函数"Nz" [英] Undefined function 'Nz' in expression

问题描述

我已经用Google搜索了此错误，还没有得出为什么我会收到此错误的结论.我正在尝试用一些数据填充DataGridView.这是我的代码.

I've Googled this error and haven't drawn up a conclusion to why I'm receiving this error. I'm trying to fill a DataGridView with some data. Here is my code.

Private Sub LoadGrid()
    Dim cmd As New OleDbCommand
    Dim dt As DataTable
    With cmd
        .CommandText = "project_master_query"
        .CommandType = CommandType.StoredProcedure
        .Connection = New OleDbConnection(My.Settings.cnnString)
    End With
    dt = GetData(cmd)
    dgvData.DataSource = dt
End Sub

Private Function GetData(ByVal cmd As OleDbCommand) As DataTable
    Dim dt As New DataTable
    Using cmd.Connection
        cmd.Connection.Open()
        dt.Load(cmd.ExecuteReader(CommandBehavior.CloseConnection))
    End Using
    Return dt
End Function

查询存储在Access中的"project_master_query".

Query "project_master_query" stored within Access.

    SELECT project_master.*, location_master.LocationName,
    project_currentmilestonedef.MilestoneDefID, 
    project_currentmilestonedef.MilestoneName, project_regions.RegionName,
    owner_fullname.FullName AS OwnerFullName, designer_fullname.FullName AS DesignerFullName,
    project_issuecount.HasOpenIssues, project_updated_closedate.UpdatedCloseDate, 
    project_bonusdays.BonusDays, project_bonusdays.IsGreen, project_bonusdays.IsYellow, 
    project_bonusdays.IsRed, checklist_days_perproject_defined_1.Week1, 
    checklist_days_perproject_defined_1.Week2, checklist_days_perproject_defined_1.Week3, 
    checklist_days_perproject_defined_1.Week4, project_issueduration.ProjectIssueDurationDays, 
    project_active_status.ProjectIsOpen, project_requirement_status.RequirementStatusName, 
    priority_def.PriorityShortName
    FROM project_requirement_status 
    RIGHT JOIN (project_regions 
    RIGHT JOIN (priority_def RIGHT JOIN (location_master 
    RIGHT JOIN ((((checklist_days_perproject_defined AS checklist_days_perproject_defined_1 
    RIGHT JOIN ((((((((contacts_fullname AS designer_fullname 
    RIGHT JOIN (contacts_fullname AS owner_fullname 
    RIGHT JOIN project_master ON owner_fullname.ContactID = project_master.ContactOwner) 
    ON designer_fullname.ContactID = project_master.ContactDesigner) 
    LEFT JOIN project_issuecount 
    ON project_master.ProjectID = project_issuecount.ProjectID) 
    LEFT JOIN project_currentmilestonedef 
    ON project_master.ProjectID = project_currentmilestonedef.ProjectID) 
    LEFT JOIN project_within_benchmark_week1 
    ON project_master.ProjectID = project_within_benchmark_week1.ProjectID) 
    LEFT JOIN project_within_benchmark_week2 
    ON project_master.ProjectID = project_within_benchmark_week2.ProjectID) 
    LEFT JOIN project_within_benchmark_week3 
    ON project_master.ProjectID = project_within_benchmark_week3.ProjectID) 
    LEFT JOIN project_updated_closedate 
    ON project_master.ProjectID = project_updated_closedate.ProjectID) 
    LEFT JOIN checklist_days_perproject_defined 
    ON project_master.ProjectID = checklist_days_perproject_defined.ProjectID) 
    ON checklist_days_perproject_defined_1.ProjectID = project_master.ProjectID) 
    LEFT JOIN project_issueduration 
    ON project_master.ProjectID = project_issueduration.ProjectID) 
    LEFT JOIN project_active_status 
    ON project_master.ProjectID = project_active_status.ProjectID)    
    LEFT JOIN project_bonusdays ON project_master.ProjectID = project_bonusdays.ProjectID) 
    ON location_master.LocationID = project_master.Location) 
    ON priority_def.PriorityDefID = project_master.ProjectPriority) 
    ON project_regions.RegionID = project_master.Region) 
    ON project_requirement_status.RequirementStatusID = project_master.RequirementStatus;

您可以看到查询中根本没有Nz，所以我根本不明白为什么会这样.一旦调用dt.Load，就会发生错误.

As you can see there is no Nz in the query at all so I don't understand why this happening at all. The error occurs once the dt.Load is called.

推荐答案

Nz函数最有可能在您通过多个联接之一引用的视图/查询中使用.您必须仔细阅读所有这些内容.

The Nz function is most likely in a view/query you are referencing in one of your many joins. You'll have to look through all of them.

由于Nz()是Access应用程序的功能，而不是Access驱动程序的功能，因此，当您尝试从Access应用程序外部使用它时，它将失败.您可以将Nz替换为IIf(IsNull())构造.

As Nz() is a function of the Access application and not the Access driver, it will fail anytime you try to use it from outside the Access application. You can replace the Nz with an IIf(IsNull()) construct.

请参阅IIf 和 IsNull

放在一起时:

Nz(expr, [valueifnull])

成为

IIf(IsNull(expr), valueifnull, valueifnotnull)

示例

默认值:Nz(tbl.A) => IIf(IsNull(tbl.A), '', tbl.A)

有后备广告:Nz(tbl.A, tbl.B) => IIf(IsNull(tbl.A), tbl.B, tbl.A)

这篇关于表达式中未定义的函数"Nz"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！