Undefined function 'Nz' in expression
.net, ms-access, sql
Solution
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.
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.
See documentation for `IIf` and `IsNull`
When put together:
`Nz(expr, [valueifnull])`
becomes
`IIf(IsNull(expr), valueifnull, valueifnotnull)`
Examples
Default: `Nz(tbl.A) => IIf(IsNull(tbl.A), '', tbl.A)`
With fallback: `Nz(tbl.A, tbl.B) => IIf(IsNull(tbl.A), tbl.B, tbl.A)`
Problem
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 ``` 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; ``` 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.