nz function data type problem

[Ray]

I am using a query to count the number of school days a student has been in
our program - to this end, I am using another query to find each students
start and end dates. If the end date value is null, that means they are
still enrolled, and I want the query to return the last date attendance was
recorded. I use a nz function, and it returns the right value, but it comes
back as a text data type. I need it to be a date/time type so I can use it
as part of the criteria for my other query. Is there a way to make the nz
function return a date/time data type? Or is there a better way to do this?

I can change the query to a make table query and manually change the data
type, but I need it to be automated, so that is not practical in the long run.

Here is the query I am using:

SELECT DISTINCT KCCData.Last_Name, KCCData.First_Name, KCCData.Birth_Date,
dbEnrollment.Start_Date,
nz([dbEnrollment].[End_Date],[qmaxattendance].[maxofdate]) AS End_Date
FROM SchoolDays, qMaxAttendance, KCCData INNER JOIN dbEnrollment ON
KCCData.Student_ID = dbEnrollment.Student_ID
WHERE (((dbEnrollment.Start_Date)>#7/1/2008#))
ORDER BY KCCData.Last_Name, KCCData.First_Name;

Thank you in advance for your help.

 

[Clifford Bass]

Hi Ray,

Wrap the Nz(...) with CDate():

CDate(Nz([dbEnrollment].[End_Date],[qmaxattendance].[maxofdate]))

Clifford Bass

 

[Ray]

That did the trick. Thank you.

Hi Ray,

Wrap the Nz(...) with CDate():

CDate(Nz([dbEnrollment].[End_Date],[qmaxattendance].[maxofdate]))

Clifford Bass

I am using a query to count the number of school days a student has been in
our program - to this end, I am using another query to find each students
start and end dates. If the end date value is null, that means they are
still enrolled, and I want the query to return the last date attendance was
recorded. I use a nz function, and it returns the right value, but it comes
back as a text data type. I need it to be a date/time type so I can use it
as part of the criteria for my other query. Is there a way to make the nz
function return a date/time data type? Or is there a better way to do this?

I can change the query to a make table query and manually change the data
type, but I need it to be automated, so that is not practical in the long run.

Here is the query I am using:

SELECT DISTINCT KCCData.Last_Name, KCCData.First_Name, KCCData.Birth_Date,
dbEnrollment.Start_Date,
nz([dbEnrollment].[End_Date],[qmaxattendance].[maxofdate]) AS End_Date
FROM SchoolDays, qMaxAttendance, KCCData INNER JOIN dbEnrollment ON
KCCData.Student_ID = dbEnrollment.Student_ID
WHERE (((dbEnrollment.Start_Date)>#7/1/2008#))
ORDER BY KCCData.Last_Name, KCCData.First_Name;

Thank you in advance for your help.

 

[Clifford Bass]

Hi Ray,

You are welcome.

Clifford Bass

 

[Allen Browne]

Ray, here's another approach. Instead of using Nz() wrapped in CDate(), try:
IIf([dbEnrollment].[End_Date] Is Null, [qmaxattendance].[maxofdate],
[dbEnrollment].[End_Date])

Advantages:
a) IIf() does not mess up the data types.

b) JET knows how to execute the IIf(), so it avoids 2 VBA function calls.

c) Still works even if maxofdate is null (which would cause CDate() to
fail.)

For more explanation of this opinion, see:
Common query hurdles - IIf(), not Nz()
at:
http://allenbrowne.com/QueryPerfIssue.html#Nz

 

[DawnTreader]

Hello Allen

if i am doing a calculated field on a report is it better to use the nz
function or the iif function?

i have been correcting a lot of nz things in my sql, but i am unsure if a
calculated report field will be using the jetsql to do the calcs or if it
calls VBA.

any idea?

Ray, here's another approach. Instead of using Nz() wrapped in CDate(), try:
IIf([dbEnrollment].[End_Date] Is Null, [qmaxattendance].[maxofdate],
[dbEnrollment].[End_Date])

Advantages:
a) IIf() does not mess up the data types.

b) JET knows how to execute the IIf(), so it avoids 2 VBA function calls.

c) Still works even if maxofdate is null (which would cause CDate() to
fail.)

For more explanation of this opinion, see:
Common query hurdles - IIf(), not Nz()
at:
http://allenbrowne.com/QueryPerfIssue.html#Nz

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am using a query to count the number of school days a student has been in
our program - to this end, I am using another query to find each students
start and end dates. If the end date value is null, that means they are
still enrolled, and I want the query to return the last date attendance
was
recorded. I use a nz function, and it returns the right value, but it
comes
back as a text data type. I need it to be a date/time type so I can use
it
as part of the criteria for my other query. Is there a way to make the nz
function return a date/time data type? Or is there a better way to do
this?

I can change the query to a make table query and manually change the data
type, but I need it to be automated, so that is not practical in the long
run.

Here is the query I am using:

SELECT DISTINCT KCCData.Last_Name, KCCData.First_Name, KCCData.Birth_Date,
dbEnrollment.Start_Date,
nz([dbEnrollment].[End_Date],[qmaxattendance].[maxofdate]) AS End_Date
FROM SchoolDays, qMaxAttendance, KCCData INNER JOIN dbEnrollment ON
KCCData.Student_ID = dbEnrollment.Student_ID
WHERE (((dbEnrollment.Start_Date)>#7/1/2008#))
ORDER BY KCCData.Last_Name, KCCData.First_Name;

Thank you in advance for your help.

 

[Allen Browne]

My preference is to use IIf() and Is Null rather than Nz() and IsNull() in
expressions in the ControlSource as well.

In practice, it matters less in a report. You probably do everything you can
to make your queries efficient, whereas a report is going to show only a few
calculated records on a page anyway.

Apart from efficiency, making queries independent of VBA means they don't
break (e.g. if a library reference is bad), and can be used from outside the
database (e.g. in another that doesn't if the references don't match.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hello Allen

if i am doing a calculated field on a report is it better to use the nz
function or the iif function?

i have been correcting a lot of nz things in my sql, but i am unsure if a
calculated report field will be using the jetsql to do the calcs or if it
calls VBA.

any idea?

Ray, here's another approach. Instead of using Nz() wrapped in CDate(),
try:
IIf([dbEnrollment].[End_Date] Is Null, [qmaxattendance].[maxofdate],
[dbEnrollment].[End_Date])

Advantages:
a) IIf() does not mess up the data types.

b) JET knows how to execute the IIf(), so it avoids 2 VBA function calls.

c) Still works even if maxofdate is null (which would cause CDate() to
fail.)

For more explanation of this opinion, see:
Common query hurdles - IIf(), not Nz()
at:
http://allenbrowne.com/QueryPerfIssue.html#Nz

I am using a query to count the number of school days a student has been
in
our program - to this end, I am using another query to find each
students
start and end dates. If the end date value is null, that means they
are
still enrolled, and I want the query to return the last date attendance
was
recorded. I use a nz function, and it returns the right value, but it
comes
back as a text data type. I need it to be a date/time type so I can
use
it
as part of the criteria for my other query. Is there a way to make the
nz
function return a date/time data type? Or is there a better way to do
this?

I can change the query to a make table query and manually change the
data
type, but I need it to be automated, so that is not practical in the
long
run.

Here is the query I am using:

SELECT DISTINCT KCCData.Last_Name, KCCData.First_Name,
KCCData.Birth_Date,
dbEnrollment.Start_Date,
nz([dbEnrollment].[End_Date],[qmaxattendance].[maxofdate]) AS End_Date
FROM SchoolDays, qMaxAttendance, KCCData INNER JOIN dbEnrollment ON
KCCData.Student_ID = dbEnrollment.Student_ID
WHERE (((dbEnrollment.Start_Date)>#7/1/2008#))
ORDER BY KCCData.Last_Name, KCCData.First_Name;

 

[Clifford Bass]

Hi Allen,

I remember the issue with Replace(). The others were not functions I
needed in queries. Kind of odd that the domain aggregate functions are not
supported in the table definition, but are supported by Jet/ACE. Oh well, so
it is.

Thanks for the information,

Clifford Bass