VB 6 does not understand Nz function

the database i use is MS access 2003
after all, the report based on query. I created union query and use a lot of Nz functions
No, i  am calling the report from VB and getting the error Unkown fucntion NZ
what to do?
i already set the reference to MS Access, but it seems it does not work
rfedorovAsked:
Who is Participating?
 
felipevidaurriConnect With a Mentor Commented:
You can add the query?..
0
 
rfedorovAuthor Commented:
thank you, the query is bigger then that, but at least you will see
SELECT 
ForLetter.Category,
ForLetter.MailedDate,
0 AS ID,
0 AS ID1, 
ForLetter.RecInput, 
0 AS  ID2,
0 AS  ID3, 
0 AS  ID4,
0 AS FTE

FROM ForLetter 
where
ForLetter.MailedDate = format(now -1,"mm/dd/yyyy");

UNION ALL 

SELECT    
rm.CodeValue,    
(select min(DateRec) from QueryCount2 where CodeValue=rm.CodeValue and DayCompleted is null) AS [Oldest Uncompleted],   
nz(DateDiff("d",(select min(DateRec) from QueryCount2 where CodeValue=rm.CodeValue and DayCompleted is  null),Now()-1),0) AS [Days Diff],   
nz((select sum(CountApp) from  QueryCount2 where CodeValue=rm.CodeValue and DateRec=format(now-1,"mm/dd/yyyy")),0 ) as [Incoming Today],  


nz((select sum(a.Complete) as Total from (  
SELECT DateRec, CodeValue, nz(DateCompl3, nz(DateCompl2, DateCompl1)) as DateCompl, iif(Day3=0, iif(Day2=0, Day1,Day2), Day3) as Complete   
FROM QueryCount2) a where a.DateCompl = date()-1 and a.CodeValue = rm.CodeValue),0) as [Completed Today], 

nz((select sum(CountApp) from  QueryCount2 where CodeValue=rm.CodeValue and DayCompleted is  null ),0) AS [Total Input],
nz((select sum(TotalPro) from  QueryCount2 where CodeValue=rm.CodeValue and DayCompleted is  null ),0) AS [Total Completed],

nz((select sum(CountApp) from  QueryCount2 where CodeValue=rm.CodeValue and DayCompleted is  null ),0)-nz((select sum(TotalPro) from  QueryCount2 where CodeValue=rm.CodeValue and DayCompleted is  null ),0) as [Total Pending],

nz((SELECT Format((Sum(a.Complete)/60),"0.00" ) AS Total FROM  (  
SELECT CodeValue,    
nz(DateCompl3, nz(DateCompl2, DateCompl1)) as DateCompl, iif(Time3=0, iif(Time2=0, Time1,Time2), Time3) as Complete     
FROM QueryCount2a   )  a WHERE a.DateCompl=date()-1  and a.CodeValue = rm.CodeValue),0) as [FTE] 
FROM    
QueryCount2 AS rm   
GROUP BY rm.CodeValue

Open in new window

0
 
felipevidaurriConnect With a Mentor Commented:
Try using isNull instead nz
SELECT 
ForLetter.Category,
ForLetter.MailedDate,
0 AS ID,
0 AS ID1, 
ForLetter.RecInput, 
0 AS  ID2,
0 AS  ID3, 
0 AS  ID4,
0 AS FTE

FROM ForLetter 
where
ForLetter.MailedDate = format(now -1,"mm/dd/yyyy");

UNION ALL

SELECT
rm.CodeValue,    
(select min(DateRec) from QueryCount2 where CodeValue=rm.CodeValue and DayCompleted is null) AS [Oldest Uncompleted],   
isnull(DateDiff("d",(select min(DateRec) from QueryCount2 where CodeValue=rm.CodeValue and DayCompleted is  null),Now()-1),0) AS [Days Diff],   
isnull((select sum(CountApp) from  QueryCount2 where CodeValue=rm.CodeValue and DateRec=format(now-1,"mm/dd/yyyy")),0 ) as [Incoming Today],  


isnull((select sum(a.Complete) as Total from (  
SELECT DateRec, CodeValue, isnull(DateCompl3, isnull(DateCompl2, DateCompl1)) as DateCompl, iif(Day3=0, iif(Day2=0, Day1,Day2), Day3) as Complete   
FROM QueryCount2) a where a.DateCompl = date()-1 and a.CodeValue = rm.CodeValue),0) as [Completed Today], 

isnull((select sum(CountApp) from  QueryCount2 where CodeValue=rm.CodeValue and DayCompleted is  null ),0) AS [Total Input],
isnull((select sum(TotalPro) from  QueryCount2 where CodeValue=rm.CodeValue and DayCompleted is  null ),0) AS [Total Completed],

isnull((select sum(CountApp) from  QueryCount2 where CodeValue=rm.CodeValue and DayCompleted is  null ),0)-isnull((select sum(TotalPro) from  QueryCount2 where CodeValue=rm.CodeValue and DayCompleted is  null ),0) as [Total Pending],

isnull((SELECT Format((Sum(a.Complete)/60),"0.00" ) AS Total FROM  (  
SELECT CodeValue,    
isnull(DateCompl3, isnull(DateCompl2, DateCompl1)) as DateCompl, iif(Time3=0, iif(Time2=0, Time1,Time2), Time3) as Complete     
FROM QueryCount2a   )  a WHERE a.DateCompl=date()-1  and a.CodeValue = rm.CodeValue),0) as [FTE] 
FROM    
QueryCount2 AS rm   
GROUP BY rm.CodeValue

Open in new window

0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
rfedorovAuthor Commented:
you simply replaced Nz to Isnull?
0
 
felipevidaurriCommented:
Yes...
0
 
rfedorovAuthor Commented:
could you look at full query? i am getting the error, you did it fast!!!
SELECT 
rm.ProType, 
(select min(recordeddate) from returnmail where protype = rm.protype and completedate is null) AS [Oldest Uncompleted], 
nz(DateDiff("d",(select min(recordeddate) from returnmail where protype = rm.protype and completedate is null),Now()-1),0) AS [Days Diff], 
nz((select sum(recinput) from returnmail where protype = rm.protype and recordeddate = format(now -1,"mm/dd/yyyy")),0) AS [Incoming Today], 
nz((select sum (TotalAppsUpdated) from returnmail r, returnmailupdated u where r.batchid = u.batchid and r.protype = rm.protype and u.updateddate =  format(now -1,"mm/dd/yyyy") ),0) AS [Completed Today], 
nz((select sum(recinput) from returnmail where protype = rm.protype and completedate is null),0) AS [Total Input], 
NZ((select sum(TotalAppsUpdated) from returnmail  r, returnmailupdated u where r.batchid = u.batchid and protype = rm.protype and r.Status=0),0) AS [Total Completed], 
nz((select sum(recinput) from returnmail where protype = rm.protype and completedate is null),0)-NZ((select sum(TotalAppsUpdated) from returnmail  r, returnmailupdated u where r.batchid = u.batchid and protype = rm.protype and r.Status=0),0) AS [Total  Pending]
FROM returnmail AS rm
GROUP BY rm.ProType;

UNION ALL
SELECT 
rm.ProType, 
(select min(recordeddate) from RecordOthers where protype = rm.protype and completedate is null) AS [Oldest Uncompleted], 
nz(DateDiff("d",(select min(recordeddate) from RecordOthers where protype = rm.protype and completedate is null),Now()-1),0) AS [Days Diff], 
nz((select sum(recinput) from RecordOthers where protype = rm.protype and recordeddate = format(now -1,"mm/dd/yyyy")),0) AS [Incoming Today], 
nz((select sum (TotalAppsUpdated) from RecordOthers r, history u where r.batchid = u.batchid and r.protype = rm.protype and u.updateddate =  format(now -1,"mm/dd/yyyy") ),0) AS [Completed Today], 
nz((select sum(recinput) from RecordOthers where protype = rm.protype and completedate is null),0) AS [Total Input], 
nz( (select sum(TotalAppsUpdated) from RecordOthers  r, History u where r.batchid = u.batchid and protype = rm.protype and r.Status=0),0) AS [Total Completed], 
nz((select sum(recinput) from RecordOthers where protype = rm.protype and completedate is null),0)-nz((select sum(TotalAppsUpdated) from RecordOthers  r, History u where r.batchid = u.batchid and protype = rm.protype and r.Status=0),0) AS [Total  Pending] 
FROM RecordOthers AS rm
GROUP BY rm.ProType;

UNION ALL 
SELECT 
ForLetter.Category,
ForLetter.MailedDate,
0 AS ID,
0 AS ID1, 
ForLetter.RecInput, 
0 AS  ID2,
0 AS  ID3, 
0 AS  ID4 

FROM ForLetter 
where
ForLetter.MailedDate = format(now -1,"mm/dd/yyyy");

UNION ALL
SELECT rm.CodeValue,
(select min(DateRec) from QueryCount2 where CodeValue=rm.CodeValue and DayCompleted is null) AS [Oldest Uncompleted], 
nz(DateDiff("d",(select min(DateRec) from QueryCount2 where CodeValue=rm.CodeValue and DayCompleted is  null),Now()-1),0) AS [Days Diff], 
nz((select sum(CountApp) from  QueryCount2 where CodeValue=rm.CodeValue and DateRec=format(now-1,"mm/dd/yyyy")),0) AS [Incoming Today], 
nz ((select sum(a.[Completed Today]) as Total from ( 
SELECT DateRec, CodeValue, nz(DateCompl3, nz(DateCompl2, DateCompl1)) as DateCompl, iif(Day3=0, iif(Day2=0, Day1,Day2), Day3) as [Completed Today]  
FROM QueryCount2) a where a.DateCompl = date() and a.CodeValue = rm.CodeValue),0) AS  [Completed Today]  ,

nz((select sum(CountApp) from  QueryCount2 where CodeValue=rm.CodeValue and DayCompleted is  null ),0) AS [Total Input],
nz((select sum(TotalPro) from  QueryCount2 where CodeValue=rm.CodeValue and DayCompleted is  null ),0) AS [Total Completed],

nz((select sum(CountApp) from  QueryCount2 where CodeValue=rm.CodeValue and DayCompleted is  null ),0)-nz((select sum(TotalPro) from  QueryCount2 where CodeValue=rm.CodeValue and DayCompleted is  null ),0) as [Total Pending]

FROM QueryCount2 AS rm
GROUP BY rm.CodeValue;

Open in new window

0
 
Bill RossConnect With a Mentor Commented:
Hi,

Syntax is not exact replacement so you cannot just "replace":

nz(fieldname,0)
returns fieldname or 0 if it is null

isnull(fieldname)
returns true if null, false if not null

you can replace nz with iif and isnull
iif(isnull(fieldname),0,fieldname)
returns 0 if null or fieldname if not null


Bill
0
 
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
rfedorov,

The NZ() function is an MS Access construct.
It does not exist in true SQL.

But you must know when to use it.
Null *IS NOT* the same as Zero
(and should not be blindly interchanged)
For example:

The average of: 10, Null, 20, 30, 40, 50= 30
So if you are averaging values, and a value does not exist, it is Null and will not be included in the calculation.
Because Null is the absence of a value, this is the same as averaging:
10, 20, 30, 40, 50

The average of: 10, 0, 20, 30, 40, 50= 24
So if you are averaging values, and a value is Zero, it *IS* included in the calculation.

So if a value is null and you blindly convert it to zero your result may be incorrect.
;-)

So, you must know when using NZ() is applicable.

;-)


JeffCoachman
0
 
rfedorovAuthor Commented:
still does not work
0
 
leonstrykerConnect With a Mentor Commented:
>i  am calling the report from VB and getting the error Unkown fucntion NZ

Could you show your VB code on how you are calling this query and how you are porcessing the data after it is returned by your code. It maybe easier to replace NULL afterwards than durinfg execusion.

Leon
0
 
rfedorovAuthor Commented:
look attached, thank you
Private Sub cmdPrintAccessReport_Click()
   On Error GoTo ErrorHandler
   Dim appAccess As Access.Application
   Dim rpt As Access.Report
   Dim strReport As String
   Dim strDBNameAndPath As String
    
   strDBNameAndPath = App.PATH & "\CARE_DB.mdb"
   'MsgBox strDBNameAndPath
   conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
            App.PATH & "\CARE_DB.mdb"
   
   strReport = "QueryCombinedTwo"
   
   Set appAccess = GetObject(, "Access.Application")
   appAccess.OpenCurrentDatabase (strDBNameAndPath)
   appAccess.DoCmd.OpenReport reportname:=strReport
   appAccess.CloseCurrentDatabase
  
ErrorHandlerExit:
   Set appAccess = Nothing
   Exit Sub
  
ErrorHandler:
   'Access is not running; open Access with CreateObject
   If Err.Number = 429 Then
      Set appAccess = CreateObject("Access.Application")
      Resume Next
   Else
      MsgBox "Error No: " & Err.Number _
         & " in cmdReports_Click procedure; " _
         & "Description: " & Err.Description
      Resume ErrorHandlerExit
   End If
    
End Sub

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.