UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Nz Not Working In Access 2016, Access 2016    
 
   
CadetITGuy
post Aug 24 2019, 10:01 PM
Post#1



Posts: 7
Joined: 22-July 12



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
Go to the top of the page
 
RJD
post Aug 24 2019, 10:43 PM
Post#2


UtterAccess VIP
Posts: 10,018
Joined: 25-October 10
From: Gulf South USA


Hi: Looks familiar. I think we worked on this HERE. 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
Attached File(s)
Attached File  AttendanceByDate.zip ( 23.52K )Number of downloads: 4
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
dale.fye
post Aug 25 2019, 09:43 AM
Post#3



Posts: 160
Joined: 28-March 18
From: Virginia


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");

--------------------
Dale Fye
Microsoft Access MVP 2013-2016
Developing Solutions, LLC
Go to the top of the page
 
CadetITGuy
post Aug 25 2019, 12:57 PM
Post#4



Posts: 7
Joined: 22-July 12



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!
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    17th September 2019 - 10:04 PM