?
Solved

VB 6 does not understand Nz function

Posted on 2009-12-17
11
Medium Priority
?
807 Views
Last Modified: 2013-12-20
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
0
Comment
Question by:rfedorov
11 Comments
 
LVL 5

Accepted Solution

by:
felipevidaurri earned 800 total points
ID: 26076584
You can add the query?..
0
 

Author Comment

by:rfedorov
ID: 26076625
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
 
LVL 5

Assisted Solution

by:felipevidaurri
felipevidaurri earned 800 total points
ID: 26076646
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

by:rfedorov
ID: 26076676
you simply replaced Nz to Isnull?
0
 
LVL 5

Expert Comment

by:felipevidaurri
ID: 26076687
Yes...
0
 

Author Comment

by:rfedorov
ID: 26076734
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
 
LVL 14

Assisted Solution

by:Bill Ross
Bill Ross earned 400 total points
ID: 26076836
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
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 400 total points
ID: 26077056
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
 

Author Comment

by:rfedorov
ID: 26195609
still does not work
0
 
LVL 29

Assisted Solution

by:leonstryker
leonstryker earned 400 total points
ID: 26458276
>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
 

Author Comment

by:rfedorov
ID: 26489523
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question