Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Queries _ Nz Not Working In Access 2016

Posted by: CadetITGuy Aug 24 2019, 10:01 PM

I tried using the following on a different computer with Access 2013 however when I use it with 2016 I get an error - Undefined function "Nz" in expression
How can I replace the Nz function to make this work with various versions of Access?

The column comes up blank and I need a zero

TRANSFORM NZ(First([Attendance Query prep].Count)) +0 AS FirstOfCount
SELECT [Attendance Query prep].[Start Date]
FROM [Attendance Query prep]
GROUP BY [Attendance Query prep].[Start Date]
PIVOT [Attendance Query prep].Label In ("MCadetP","FCadetP","MCadetA","FCadetA","MCadetE","FCadetE","MStaffP","FStaffP","MStaffA","FStaffA","MStaffE","FStaffE");


Thanks in advance

Posted by: RJD Aug 24 2019, 10:43 PM

Hi: Looks familiar. I think we worked on this https://www.UtterAccess.com/forum/index.php?showtopic=2054822&hl=. I had suggested an approach other than crosstab - and it does not rely on using NZ. I am sure you can make the crosstab work for you, with the NZ applied correctly, but the attached approach seems easy, and displays per your requirements. Plus, you sometimes get unreliable results using the First function.

I included only the data you posted in the other thread - but you can apply this to your actual data instead. Note that you will have to change the name of the feeder query, and that I changed the field names to remove spaces (which can cause you difficulty in some cases).

See the attached, which is an extension of the approach posted in the ref thread. You should be able to modify it to accommodate the new fields.

Or you could post a db with a table of the records from [Attendance Query prep] and we could help you with the crosstab.

HTH
Joe

 AttendanceByDate.zip ( 23.52K ): 6
 

Posted by: dale.fye Aug 25 2019, 09:43 AM

Couple of questions:

1. have you checked to make sure that you don't have any missing references?
2. Does your query [Attendance Query Prep] actually have a [Count] field? Count is a reserved word, so I would recommend that you wrap it in brackets []
3. The NZ function, when used in a query will always return a string value, even if the result is numeric. So, if you want this column to display right justified in your query, as a number, I would use one of the numeric transformation functions for that field.

Try:

TRANSFORM clng(NZ(First([Attendance Query prep].[Count]),0) AS FirstOfCount
SELECT [Attendance Query prep].[Start Date]
FROM [Attendance Query prep]
GROUP BY [Attendance Query prep].[Start Date]
PIVOT [Attendance Query prep].Label In ("MCadetP","FCadetP","MCadetA","FCadetA","MCadetE","FCadetE","MStaffP","FStaffP","MStaffA","FStaffA","MStaffE","FStaffE");

Posted by: CadetITGuy Aug 25 2019, 12:57 PM

Dale,

Thank you very much. I used your data with a couple of changes (found more stuff I had to play with) and it works!