nz function data type problem

R

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.
 
C

Clifford Bass

Hi Ray,

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

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

Clifford Bass
 
R

Ray

That did the trick. Thank you.

Clifford Bass said:
Hi Ray,

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

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

Clifford Bass

Ray said:
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.
Click to expand...
Click to expand...
 
A

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
 
D

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?

Allen Browne said:
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.

Ray said:
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.
Click to expand...
Click to expand...
 
A

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.

DawnTreader said:
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?

Allen Browne said:
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

Ray said:
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;
Click to expand...
Click to expand...
Click to expand...
 
C

Clifford Bass

Hi Allen,

I would agree with your approach, using IIf() function. It is better
than my solution. It would be nice if the other functions would deal with
nulls. And it would be nice if Microsoft would both add the Nz function to
the Jet and ACE engines and fix it so that it deals with the types correctly.
Or provide an enhanced version so as not cause potential problems with
existing usages.

However, your item b struck me as maybe not being quite correct. I did
know about Nz() not being known to Jet. But it seemed to me that Jet did
know about other functions. So I went to your web site that you listed
(below) to see what your thoughts were. I learned some new stuff there!
However, on it you stated that a number of functions (IsNull(), DLookup(),
DSum(), etc.) are not known to Jet and therefore cause it to call out to VBA.
Again, that did not quite seem to match up with my memory. But my memory
could be faulty. So I decided to do some testing. At first I tried the
testing from within Access 2007, but that did not really give me a definitive
answer. So I decided to shift to using C# to do the testing. That way,
there should not be any doubt. It would succeed if Jet or ACE knew about the
function. It would fail if they do not.

Here is the code I used (somewhat scrunched to be readable in the
newsgroup window):

-------------------------------------------------

private void btnTestJetACE_Click(object sender, EventArgs e)
{
ADODB.Connection cnnAccessDB = new ADODB.Connection();
ADODB.Recordset rstTest = new ADODB.Recordset();
int intProviderIndex;
int intFunctionIndex;
String strResults = String.Empty;
String [] strarrFunctions =
{"CBool(5)",
"CByte(1)",
"CCur(\"123.12\")",
"CDate(\"12/31/2008\")",
"CDbl(\"123.456\")",
"CDec(\"543.2101234\")",
"CDec(\"543.2101234\", 3)",
"CInt(\"123\")",
"CSng(\"321.21\")",
"CStr(123)",
"DLookUp(\"[Last Name]\", \"[tblSome Table]\", " +
"\"ID = 4\")",
"DMax(\"ID\", \"[tblSome Table]\")",
"DSum(\"IND_Curr\", \"[tblSome Table]\")",
"IIf(True, \"ABC\", 123)",
"IsNull(Null)",
"Nz(Null, \"123\")"};
String [] strarrProviders =
{"Microsoft.Jet.OLEDB.4.0",
"Microsoft.ACE.OLEDB.12.0"};

for (intProviderIndex = 0; intProviderIndex <=
strarrProviders.GetUpperBound(0);
intProviderIndex++)
{
if (strResults != String.Empty)
strResults += "\r\n\r\n" +
"==============================\r\n\r\n";
strResults += "Provider: " +
strarrProviders[intProviderIndex];
cnnAccessDB.Open("Provider=" +
strarrProviders[intProviderIndex] +
";Data Source=D:\\ProgSrcs\\Access\\Database1.mdb",
String.Empty, String.Empty, 0);
for (intFunctionIndex = 0; intFunctionIndex <=
strarrFunctions.GetUpperBound(0);
intFunctionIndex++)
{
strResults += "\r\n\r\n" +
strarrFunctions[intFunctionIndex] + ":\r\n\t";
try
{
rstTest.Open("select " +
strarrFunctions[intFunctionIndex], cnnAccessDB,
ADODB.CursorTypeEnum.adOpenStatic,
ADODB.LockTypeEnum.adLockReadOnly,
(int) ADODB.CommandTypeEnum.adCmdText);
strResults += "Succeeded: " +
rstTest.Fields[0].Value.ToString();
rstTest.Close();
}
catch (Exception excpt)
{
strResults += "Failed: " + excpt.Message;
}
}
cnnAccessDB.Close();
}
txtResults.Text = strResults;
}

-------------------------------------------------

And here are the results that I got:

-------------------------------------------------

Provider: Microsoft.Jet.OLEDB.4.0

CBool(5):
Succeeded: -1

CByte(1):
Succeeded: 1

CCur("123.12"):
Succeeded: 123.12

CDate("12/31/2008"):
Succeeded: 12/31/2008 12:00:00 AM

CDbl("123.456"):
Succeeded: 123.456

CDec("543.2101234"):
Failed: Wrong number of arguments used with function in query expression
'CDec("543.2101234")'.

CDec("543.2101234", 3):
Succeeded: 0

CInt("123"):
Succeeded: 123

CSng("321.21"):
Succeeded: 321.209991455078

CStr(123):
Succeeded: 123

DLookUp("[Last Name]", "[tblSome Table]", "ID = 4"):
Succeeded: Smith

DMax("ID", "[tblSome Table]"):
Succeeded: 5

DSum("IND_Curr", "[tblSome Table]"):
Succeeded: 5300089.64

IIf(True, "ABC", 123):
Succeeded: ABC

IsNull(Null):
Succeeded: -1

Nz(Null, "123"):
Failed: Undefined function 'Nz' in expression.

==============================

Provider: Microsoft.ACE.OLEDB.12.0

CBool(5):
Succeeded: -1

CByte(1):
Succeeded: 1

CCur("123.12"):
Succeeded: 123.12

CDate("12/31/2008"):
Succeeded: 12/31/2008 12:00:00 AM

CDbl("123.456"):
Succeeded: 123.456

CDec("543.2101234"):
Failed: Wrong number of arguments used with function in query expression
'CDec("543.2101234"'.

CDec("543.2101234", 3):
Succeeded: 0

CInt("123"):
Succeeded: 123

CSng("321.21"):
Succeeded: 321.209991455078

CStr(123):
Succeeded: 123

DLookUp("[Last Name]", "[tblSome Table]", "ID = 4"):
Succeeded: Smith

DMax("ID", "[tblSome Table]"):
Succeeded: 5

DSum("IND_Curr", "[tblSome Table]"):
Succeeded: 5300089.64

IIf(True, "ABC", 123):
Succeeded: ABC

IsNull(Null):
Succeeded: -1

Nz(Null, "123"):
Failed: Undefined function 'Nz' in expression.

-------------------------------------------------

So, Jet and ACE know about all of those functions, except, as expected,
for the Nz() function. One interesting oddity was the CDec() function. In
VBA it uses just one parameter. Jet and ACE both expect two parameters. But
both return zero to the input I provided when I gave it two parameters. Not
certain why. A bug? Or just ignorance of what that second parameter should
be? Anyway, just thought you would want to know.

Sincerely,

Clifford Bass

Allen Browne said:
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
Click to expand...
 
A

Allen Browne

Interesting approach, Clifford

The items that are recognised within JET can be used in table design (e.g.
in the Validation Rule of a field.) This includes IIf(), Date(), and several
others. From memory, DLookup() is not supported in this context.

CDec() is buggy. Several functions had some serious hiccups in Access 2000:
FormatCurrency(), FormatDateTime(), FormatNumber(), FormatPercent(),
InStrRev(), MonthName(), Replace(), Round(), StrReverse() and WeekdayName()
http://support.microsoft.com/kb/225956

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

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

Clifford Bass said:
Hi Allen,

I would agree with your approach, using IIf() function. It is better
than my solution. It would be nice if the other functions would deal with
nulls. And it would be nice if Microsoft would both add the Nz function
to
the Jet and ACE engines and fix it so that it deals with the types
correctly.
Or provide an enhanced version so as not cause potential problems with
existing usages.

However, your item b struck me as maybe not being quite correct. I
did
know about Nz() not being known to Jet. But it seemed to me that Jet did
know about other functions. So I went to your web site that you listed
(below) to see what your thoughts were. I learned some new stuff there!
However, on it you stated that a number of functions (IsNull(), DLookup(),
DSum(), etc.) are not known to Jet and therefore cause it to call out to
VBA.
Again, that did not quite seem to match up with my memory. But my memory
could be faulty. So I decided to do some testing. At first I tried the
testing from within Access 2007, but that did not really give me a
definitive
answer. So I decided to shift to using C# to do the testing. That way,
there should not be any doubt. It would succeed if Jet or ACE knew about
the
function. It would fail if they do not.

Here is the code I used (somewhat scrunched to be readable in the
newsgroup window):

-------------------------------------------------

private void btnTestJetACE_Click(object sender, EventArgs e)
{
ADODB.Connection cnnAccessDB = new ADODB.Connection();
ADODB.Recordset rstTest = new ADODB.Recordset();
int intProviderIndex;
int intFunctionIndex;
String strResults = String.Empty;
String [] strarrFunctions =
{"CBool(5)",
"CByte(1)",
"CCur(\"123.12\")",
"CDate(\"12/31/2008\")",
"CDbl(\"123.456\")",
"CDec(\"543.2101234\")",
"CDec(\"543.2101234\", 3)",
"CInt(\"123\")",
"CSng(\"321.21\")",
"CStr(123)",
"DLookUp(\"[Last Name]\", \"[tblSome Table]\", " +
"\"ID = 4\")",
"DMax(\"ID\", \"[tblSome Table]\")",
"DSum(\"IND_Curr\", \"[tblSome Table]\")",
"IIf(True, \"ABC\", 123)",
"IsNull(Null)",
"Nz(Null, \"123\")"};
String [] strarrProviders =
{"Microsoft.Jet.OLEDB.4.0",
"Microsoft.ACE.OLEDB.12.0"};

for (intProviderIndex = 0; intProviderIndex <=
strarrProviders.GetUpperBound(0);
intProviderIndex++)
{
if (strResults != String.Empty)
strResults += "\r\n\r\n" +
"==============================\r\n\r\n";
strResults += "Provider: " +
strarrProviders[intProviderIndex];
cnnAccessDB.Open("Provider=" +
strarrProviders[intProviderIndex] +
";Data Source=D:\\ProgSrcs\\Access\\Database1.mdb",
String.Empty, String.Empty, 0);
for (intFunctionIndex = 0; intFunctionIndex <=
strarrFunctions.GetUpperBound(0);
intFunctionIndex++)
{
strResults += "\r\n\r\n" +
strarrFunctions[intFunctionIndex] + ":\r\n\t";
try
{
rstTest.Open("select " +
strarrFunctions[intFunctionIndex], cnnAccessDB,
ADODB.CursorTypeEnum.adOpenStatic,
ADODB.LockTypeEnum.adLockReadOnly,
(int) ADODB.CommandTypeEnum.adCmdText);
strResults += "Succeeded: " +
rstTest.Fields[0].Value.ToString();
rstTest.Close();
}
catch (Exception excpt)
{
strResults += "Failed: " + excpt.Message;
}
}
cnnAccessDB.Close();
}
txtResults.Text = strResults;
}

-------------------------------------------------

And here are the results that I got:

-------------------------------------------------

Provider: Microsoft.Jet.OLEDB.4.0

CBool(5):
Succeeded: -1

CByte(1):
Succeeded: 1

CCur("123.12"):
Succeeded: 123.12

CDate("12/31/2008"):
Succeeded: 12/31/2008 12:00:00 AM

CDbl("123.456"):
Succeeded: 123.456

CDec("543.2101234"):
Failed: Wrong number of arguments used with function in query expression
'CDec("543.2101234")'.

CDec("543.2101234", 3):
Succeeded: 0

CInt("123"):
Succeeded: 123

CSng("321.21"):
Succeeded: 321.209991455078

CStr(123):
Succeeded: 123

DLookUp("[Last Name]", "[tblSome Table]", "ID = 4"):
Succeeded: Smith

DMax("ID", "[tblSome Table]"):
Succeeded: 5

DSum("IND_Curr", "[tblSome Table]"):
Succeeded: 5300089.64

IIf(True, "ABC", 123):
Succeeded: ABC

IsNull(Null):
Succeeded: -1

Nz(Null, "123"):
Failed: Undefined function 'Nz' in expression.

==============================

Provider: Microsoft.ACE.OLEDB.12.0

CBool(5):
Succeeded: -1

CByte(1):
Succeeded: 1

CCur("123.12"):
Succeeded: 123.12

CDate("12/31/2008"):
Succeeded: 12/31/2008 12:00:00 AM

CDbl("123.456"):
Succeeded: 123.456

CDec("543.2101234"):
Failed: Wrong number of arguments used with function in query expression
'CDec("543.2101234"'.

CDec("543.2101234", 3):
Succeeded: 0

CInt("123"):
Succeeded: 123

CSng("321.21"):
Succeeded: 321.209991455078

CStr(123):
Succeeded: 123

DLookUp("[Last Name]", "[tblSome Table]", "ID = 4"):
Succeeded: Smith

DMax("ID", "[tblSome Table]"):
Succeeded: 5

DSum("IND_Curr", "[tblSome Table]"):
Succeeded: 5300089.64

IIf(True, "ABC", 123):
Succeeded: ABC

IsNull(Null):
Succeeded: -1

Nz(Null, "123"):
Failed: Undefined function 'Nz' in expression.

-------------------------------------------------

So, Jet and ACE know about all of those functions, except, as
expected,
for the Nz() function. One interesting oddity was the CDec() function.
In
VBA it uses just one parameter. Jet and ACE both expect two parameters.
But
both return zero to the input I provided when I gave it two parameters.
Not
certain why. A bug? Or just ignorance of what that second parameter
should
be? Anyway, just thought you would want to know.

Sincerely,

Clifford Bass

Allen Browne said:
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
Click to expand...
Click to expand...
 
C

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Nz not working in a cross tab 1
Access #Error & Excel Pivot 0
Nz Function, query criteria with Between 2
Return ALL records with NZ function 4
Query- missing values- Nz function 1
Nz function question 1
NZ function 1
Help! i cant get Nz function to work in report! 1

Top