Undefined Function 'Nz' In Expression - Stack Overflow

Just browsing Stack Overflow? Help us improve your experience. Sign up for research

1. 1. Home
   2. Questions
   3. Tags
   4. Users
   5. Companies
   6. Labs
   7. Jobs
   8. Discussions
   9. Collectives

   10. Communities for your favorite technologies. Explore all Collectives

2. Teams

   Ask questions, find answers and collaborate at work with Stack Overflow for Teams.

   Try Teams for free Explore Teams
3. Teams

4. Ask questions, find answers and collaborate at work with Stack Overflow for Teams. Explore Teams

Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Learn more about Collectives

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Learn more about Teams

Get early access and see previews of new features.

Learn more about Labs Undefined function 'Nz' in expression Ask Question Asked 11 years, 10 months ago Modified 4 years, 11 months ago Viewed 13k times 10

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.

• .net
• sql
• ms-access

Share Improve this question Follow edited Jan 29, 2013 at 13:49 bonCodigo 14.3k11 gold badge5050 silver badges9292 bronze badges asked Jan 29, 2013 at 13:41 Shane LeBlancShane LeBlanc 2,6331313 gold badges4444 silver badges7474 bronze badges 2

• Are you sure this is the query you are getting the issue with? – bonCodigo Commented Jan 29, 2013 at 13:46
• This is the only query I'm calling. LoadGrid() is called on the Loading of the application. – Shane LeBlanc Commented Jan 29, 2013 at 13:52

Add a comment |

2 Answers 2

Sorted by: Reset to default Highest score (default) Trending (recent votes count more) Date modified (newest first) Date created (oldest first) 24

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)

Share Improve this answer Follow edited Apr 20, 2018 at 19:10 jocull 21k2525 gold badges108108 silver badges154154 bronze badges answered Jan 29, 2013 at 13:53 Philip RieckPhilip Rieck 32.6k1111 gold badges8989 silver badges9999 bronze badges 0 Add a comment | 0

I've created a function to use in this case. I called it ez.

Public Function ez(ByVal vVal1 As Variant, Optional ByVal vVal2 As Variant = 0) As Variant If VBA.IsNull(vVal1) Or VBA.Len(vVal1) = 0 Then ez = vVal2 Else ez = vVal1 End If End Function

As you can see, it goes a little further than the original function since it not only considers the second value if the first one is null but also if it is empty

Also, the default return value is 0 while with Nz is an empty string.

Share Improve this answer Follow answered Dec 8, 2019 at 11:36 DavidDavid 5966 bronze badges Add a comment |

Your Answer

Reminder: Answers generated by artificial intelligence tools are not allowed on Stack Overflow. Learn more

Thanks for contributing an answer to Stack Overflow!

• Please be sure to answer the question. Provide details and share your research!

But avoid …

• Asking for help, clarification, or responding to other answers.
• Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

Draft saved Draft discarded

Sign up or log in

Sign up using Google Sign up using Email and Password Submit

Post as a guest

Name Email

Required, but never shown

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged

• .net
• sql
• ms-access

or ask your own question.

• The Overflow Blog
• Your docs are your infrastructure
• Featured on Meta
• More network sites to see advertising test [updated with phase 2]
• We’re (finally!) going to the cloud!
• Call for testers for an early access release of a Stack Overflow extension...

Visit chat

Linked

4 Alternative to Nz and IIF(Is Null)

Related

0 Access Nz() function doesn't work in query 0 Nz() not working in MS Access 0 access syntax error in FROM 0 query does not include specified function 2 You tried to execute a query that does not include the specified expression error 0 SQL Syntax Error Missing Operator MS Access 0 Access SQL Syntax error: missing operator 0 Access query: iif function error 2 MS Access Nz() Function not Recognised in MS Excel 0 The NZ() function seems not to work when called from VB, is there an alternative

Hot Network Questions

• Bash builtin 'command' ignoring option '-p'
• Student is almost always late, and expects me to re-explain everything he missed
• Expectation of a random variable and its reciprocal
• Is Holy Terra Earth?
• Are there any examples of exponential algorithms that use a polynomial-time algorithm for a special case as a subroutine (exponentially many times)?
• Identifying a TNG episode where Dr. Pulaski instructs another doctor on a sling
• What is another word for when someone’s statement lacks integrity or meaning?
• Does "binary" affect our Being?
• How can I reference sky photos to a star map?
• Can a German citizen visit Shenzhen for 6 days and go to Hong Kong for a day without a visa?
• Find the Smallest Data Type for a Number
• Is it possible to add arbitrary amounts of quantum resistance cheaply?
• A novel about Earth crossing a toxic cloud of cosmic size
• What's a good way to append a nonce to ciphertext in Python for AES GCM in Python?
• Movie about a post apocalyptic world with a scorching hot sun
• What is small arch between two notes and how to play it?
• Being honest with rejection vs. "grooming" applicant for future possibility
• Is the A321 XLR really necessary to fly MAD-BOS?
• How Do Copulas Provide Insight Into Dependence Between Random Variables?
• Frogs on lily pads want to make a party
• What do border officials do with my passport when I tell them that I'm entering as the spouse of an EU national?
• Syllables of noun ‹cavalier›?
• Inheritance Tax: the Estate or the Beneficiaries?
• Can you make 5 x 3 “magic” rectangles?

more hot questions Question feed Subscribe to RSS Question feed

To subscribe to this RSS feed, copy and paste this URL into your RSS reader.

lang-sql