|
 |
asp_web_howto thread: Undefined function 'Nz' in expression
Message #1 by dirosky@h... on Tue, 5 Nov 2002 11:19:25
|
|
Hi,
I have a query ion access that uses the NZ function to return 0 for null
values. When I run this as a stored procedure from my asp page I get the
above error message.
Has anyone come across this problem and is there a solution.
Thanks,
Patrick
Message #2 by "Ken Schaefer" <ken@a...> on Wed, 6 Nov 2002 11:35:53 +1100
|
|
Can you please post the code you are using?
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: <dirosky@h...>
Subject: [asp_web_howto] Undefined function 'Nz' in expression
: I have a query ion access that uses the NZ function to return 0 for null
: values. When I run this as a stored procedure from my asp page I get the
: above error message.
:
: Has anyone come across this problem and is there a solution.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Message #3 by dirosky@h... on Thu, 7 Nov 2002 10:38:25
|
|
Hi ken,
Access query:
SELECT PermCostsMonth.SumOfTotalCost AS PCosts, Nz
([ReliefStaffCost.SumOfTotalCost],0) AS SumRelief, Nz
([AgencyMonth.SumOfTotalCost],0) AS AgCosts, Nz
([ReliefServiceMonth.Total],0) AS RMCosts, [PCosts]+[SumRelief]+[AgCosts]+
[RMCosts] AS GrandTotal, Services.Service, Services.StaffBudget,
[StaffBudget]-[GrandTotal] AS BudBalance
FROM PermCostsMonth, ReliefStaffCost, AgencyMonth, ReliefServiceMonth,
Services
WHERE (((Services.Service)=[Name of service]));
Asp code:
Dim objR, ObjCom
Set ObjCom = Server.CreateObject("ADODB.Command")
With ObjCom
.ActiveConnection = objConn5
.CommandType = adCmdStoredProc
.CommandText = "GrandTotalQry2"
.Parameters.Append .CreateParameter("param1", advarchar,
adParamInput, 50, Session("Service"))
.Parameters.Append .CreateParameter("param2", advarchar,
adParamInput, 50, Request("Month"))
.Parameters.Append .CreateParameter("param3", advarchar,
adParamInput, 50, Request("Year"))
End With
Set objR = ObjCom.Execute
If Not objR.EOF Then
Response.Write _
"<TABLE BORDER=""1"" CELLSPACING=""3"" CELLPADDING=""3""
ALIGN=""RIGHT"">" & _
"<TD>" & _
"<TABLE BORDER=""1"" CELLSPACING=""3"" CELLPADDING=""3""
ALIGN=""CENTER"">" & _
" <TR>" & _
" <TR><B><FONT SIZE=5>Summary Costs</FONT></B></TR>" & _
" <TH WIDTH=50>SERVICE</TH>" & _
" <TH WIDTH=50>Permanent Costs</TH>" & _
" <TH WIDTH=50>Relief Costs (Shift Bookings)</TH>" & _
" <TH WIDTH=50>Agency Costs</TH>" & _
" </TR>"
Do While Not objR.EOF
Response.Write "<TR ALIGN=""CENTER"">"
Response.Write _
"<TD>"&objR("Service")&"</TD>" & _
"<TD>"&Round(objR("PCosts"),2)&"</TD>" & _
"<TD>"&Round(objR("SumRelief"),2)&"</TD>" & _
"<TD>£"&Round(objR("AgCosts"),2)&"</TD>" & _
"</TR>"
Response.Write "</TABLE>"
Response.Write _
"<TABLE BORDER=""1"" CELLSPACING=""3"" CELLPADDING=""3""
ALIGN=""CENTER"">" & _
" <TR>" & _
" <TH WIDTH=50>Grand Total</TH>" & _
" <TH WIDTH=50>Monthly Budget</TH>" & _
" <TH WIDTH=50>Budget Balance</TH>" & _
" </TR>"
Response.Write "<TR>"
Response.Write _
"<TD>£"&Round(objR("GrandTotal"),2)&"</TD>" & _
"<TD>£"&Round(objR("StaffBudget"),2)&"</TD>" & _
"<TD>£"&Round(objR("BudBalance"),2)&"</TD>" & _
"</TR>"
objR.MoveNext
Loop
Response.Write "</TABLE>"
objR.close
Set objR = Nothing
Else
Response.Write "<CENTER><H2>There are no costs for your
selection</H2></CENTER>"
Response.Write "<CENTER><H2>Service:"&Session("Service")
&"<BR>Month:"&Request("Month")&"<BR>Year:"&Request("Year")
&"</H2></CENTER>"
End If
Error message:
Error Type:
Microsoft JET Database Engine (0x80040E14)
Undefined function 'Nz' in expression.
/../onlineservices/staffing/sumcosts.asp, line 14
Thanks
> Can you please post the code you are using?
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: <dirosky@h...>
Subject: [asp_web_howto] Undefined function 'Nz' in expression
: I have a query ion access that uses the NZ function to return 0 for
null
: values. When I run this as a stored procedure from my asp page I get
the
: above error message.
:
: Has anyone come across this problem and is there a solution.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Message #4 by "Ken Schaefer" <ken@a...> on Mon, 11 Nov 2002 13:30:16 +1100
|
|
Check to see if this applies to you:
http://support.microsoft.com/default.aspx?scid=KB;en-us;q189448
If not, what happens if you use IIF() instead?
IIF(IsNull([ReliefStaffCost.SumOfTotalCost]),0,ReliefStaffCost.SumOfTotalCos
t) AS SumRelief
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: <dirosky@h...>
Subject: [asp_web_howto] Re: Undefined function 'Nz' in expression
: SELECT PermCostsMonth.SumOfTotalCost AS PCosts, Nz
: ([ReliefStaffCost.SumOfTotalCost],0) AS SumRelief, Nz
: ([AgencyMonth.SumOfTotalCost],0) AS AgCosts, Nz
: ([ReliefServiceMonth.Total],0) AS RMCosts, [PCosts]+[SumRelief]+[AgCosts]+
: [RMCosts] AS GrandTotal, Services.Service, Services.StaffBudget,
: [StaffBudget]-[GrandTotal] AS BudBalance
: FROM PermCostsMonth, ReliefStaffCost, AgencyMonth, ReliefServiceMonth,
: Services
: WHERE (((Services.Service)=[Name of service]));
:
: Asp code:
:
: Dim objR, ObjCom
:
: Set ObjCom = Server.CreateObject("ADODB.Command")
: With ObjCom
: .ActiveConnection = objConn5
: .CommandType = adCmdStoredProc
: .CommandText = "GrandTotalQry2"
: .Parameters.Append .CreateParameter("param1", advarchar,
: adParamInput, 50, Session("Service"))
: .Parameters.Append .CreateParameter("param2", advarchar,
: adParamInput, 50, Request("Month"))
: .Parameters.Append .CreateParameter("param3", advarchar,
: adParamInput, 50, Request("Year"))
: End With
:
: Set objR = ObjCom.Execute
:
: If Not objR.EOF Then
: Response.Write _
:
: "<TABLE BORDER=""1"" CELLSPACING=""3"" CELLPADDING=""3""
: ALIGN=""RIGHT"">" & _
: "<TD>" & _
: "<TABLE BORDER=""1"" CELLSPACING=""3"" CELLPADDING=""3""
: ALIGN=""CENTER"">" & _
: " <TR>" & _
: " <TR><B><FONT SIZE=5>Summary Costs</FONT></B></TR>" & _
: " <TH WIDTH=50>SERVICE</TH>" & _
: " <TH WIDTH=50>Permanent Costs</TH>" & _
: " <TH WIDTH=50>Relief Costs (Shift Bookings)</TH>" & _
: " <TH WIDTH=50>Agency Costs</TH>" & _
: " </TR>"
: Do While Not objR.EOF
: Response.Write "<TR ALIGN=""CENTER"">"
: Response.Write _
: "<TD>"&objR("Service")&"</TD>" & _
: "<TD>"&Round(objR("PCosts"),2)&"</TD>" & _
: "<TD>"&Round(objR("SumRelief"),2)&"</TD>" & _
: "<TD>£"&Round(objR("AgCosts"),2)&"</TD>" & _
: "</TR>"
: Response.Write "</TABLE>"
: Response.Write _
: "<TABLE BORDER=""1"" CELLSPACING=""3"" CELLPADDING=""3""
: ALIGN=""CENTER"">" & _
: " <TR>" & _
: " <TH WIDTH=50>Grand Total</TH>" & _
: " <TH WIDTH=50>Monthly Budget</TH>" & _
: " <TH WIDTH=50>Budget Balance</TH>" & _
: " </TR>"
: Response.Write "<TR>"
: Response.Write _
: "<TD>£"&Round(objR("GrandTotal"),2)&"</TD>" & _
: "<TD>£"&Round(objR("StaffBudget"),2)&"</TD>" & _
: "<TD>£"&Round(objR("BudBalance"),2)&"</TD>" & _
: "</TR>"
: objR.MoveNext
: Loop
: Response.Write "</TABLE>"
: objR.close
: Set objR = Nothing
: Else
: Response.Write "<CENTER><H2>There are no costs for your
: selection</H2></CENTER>"
: Response.Write "<CENTER><H2>Service:"&Session("Service")
: &"<BR>Month:"&Request("Month")&"<BR>Year:"&Request("Year")
: &"</H2></CENTER>"
: End If
:
: Error message:
:
: Error Type:
: Microsoft JET Database Engine (0x80040E14)
: Undefined function 'Nz' in expression.
: /../onlineservices/staffing/sumcosts.asp, line 14
:
:
: Thanks
:
: > Can you please post the code you are using?
:
: Cheers
: Ken
:
: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: From: <dirosky@h...>
: Subject: [asp_web_howto] Undefined function 'Nz' in expression
:
:
: : I have a query ion access that uses the NZ function to return 0 for
: null
: : values. When I run this as a stored procedure from my asp page I get
: the
: : above error message.
: :
: : Has anyone come across this problem and is there a solution.
:
: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Message #5 by dirosky@h... on Mon, 11 Nov 2002 13:13:54
|
|
Thanks Ken,
Solved my problem immediately. You REAL:LY are a genius on these pages.
Patrick
> Check to see if this applies to you:
http://support.microsoft.com/default.aspx?scid=KB;en-us;q189448
If not, what happens if you use IIF() instead?
IIF(IsNull
([ReliefStaffCost.SumOfTotalCost]),0,ReliefStaffCost.SumOfTotalCos
t) AS SumRelief
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: <dirosky@h...>
Subject: [asp_web_howto] Re: Undefined function 'Nz' in expression
: SELECT PermCostsMonth.SumOfTotalCost AS PCosts, Nz
: ([ReliefStaffCost.SumOfTotalCost],0) AS SumRelief, Nz
: ([AgencyMonth.SumOfTotalCost],0) AS AgCosts, Nz
: ([ReliefServiceMonth.Total],0) AS RMCosts, [PCosts]+[SumRelief]+
[AgCosts]+
: [RMCosts] AS GrandTotal, Services.Service, Services.StaffBudget,
: [StaffBudget]-[GrandTotal] AS BudBalance
: FROM PermCostsMonth, ReliefStaffCost, AgencyMonth, ReliefServiceMonth,
: Services
: WHERE (((Services.Service)=[Name of service]));
:
: Asp code:
:
: Dim objR, ObjCom
:
: Set ObjCom = Server.CreateObject("ADODB.Command")
: With ObjCom
: .ActiveConnection = objConn5
: .CommandType = adCmdStoredProc
: .CommandText = "GrandTotalQry2"
: .Parameters.Append .CreateParameter("param1", advarchar,
: adParamInput, 50, Session("Service"))
: .Parameters.Append .CreateParameter("param2", advarchar,
: adParamInput, 50, Request("Month"))
: .Parameters.Append .CreateParameter("param3", advarchar,
: adParamInput, 50, Request("Year"))
: End With
:
: Set objR = ObjCom.Execute
:
: If Not objR.EOF Then
: Response.Write _
:
: "<TABLE BORDER=""1"" CELLSPACING=""3"" CELLPADDING=""3""
: ALIGN=""RIGHT"">" & _
: "<TD>" & _
: "<TABLE BORDER=""1"" CELLSPACING=""3"" CELLPADDING=""3""
: ALIGN=""CENTER"">" & _
: " <TR>" & _
: " <TR><B><FONT SIZE=5>Summary Costs</FONT></B></TR>" & _
: " <TH WIDTH=50>SERVICE</TH>" & _
: " <TH WIDTH=50>Permanent Costs</TH>" & _
: " <TH WIDTH=50>Relief Costs (Shift Bookings)</TH>" & _
: " <TH WIDTH=50>Agency Costs</TH>" & _
: " </TR>"
: Do While Not objR.EOF
: Response.Write "<TR ALIGN=""CENTER"">"
: Response.Write _
: "<TD>"&objR("Service")&"</TD>" & _
: "<TD>"&Round(objR("PCosts"),2)&"</TD>" & _
: "<TD>"&Round(objR("SumRelief"),2)&"</TD>" & _
: "<TD>£"&Round(objR("AgCosts"),2)&"</TD>" & _
: "</TR>"
: Response.Write "</TABLE>"
: Response.Write _
: "<TABLE BORDER=""1"" CELLSPACING=""3"" CELLPADDING=""3""
: ALIGN=""CENTER"">" & _
: " <TR>" & _
: " <TH WIDTH=50>Grand Total</TH>" & _
: " <TH WIDTH=50>Monthly Budget</TH>" & _
: " <TH WIDTH=50>Budget Balance</TH>" & _
: " </TR>"
: Response.Write "<TR>"
: Response.Write _
: "<TD>£"&Round(objR("GrandTotal"),2)&"</TD>" & _
: "<TD>£"&Round(objR("StaffBudget"),2)&"</TD>" & _
: "<TD>£"&Round(objR("BudBalance"),2)&"</TD>" & _
: "</TR>"
: objR.MoveNext
: Loop
: Response.Write "</TABLE>"
: objR.close
: Set objR = Nothing
: Else
: Response.Write "<CENTER><H2>There are no costs for your
: selection</H2></CENTER>"
: Response.Write "<CENTER><H2>Service:"&Session("Service")
: &"<BR>Month:"&Request("Month")&"<BR>Year:"&Request("Year")
: &"</H2></CENTER>"
: End If
:
: Error message:
:
: Error Type:
: Microsoft JET Database Engine (0x80040E14)
: Undefined function 'Nz' in expression.
: /../onlineservices/staffing/sumcosts.asp, line 14
:
:
: Thanks
:
: > Can you please post the code you are using?
:
: Cheers
: Ken
:
: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: From: <dirosky@h...>
: Subject: [asp_web_howto] Undefined function 'Nz' in expression
:
:
: : I have a query ion access that uses the NZ function to return 0 for
: null
: : values. When I run this as a stored procedure from my asp page I get
: the
: : above error message.
: :
: : Has anyone come across this problem and is there a solution.
:
: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
|
|
View Cart
