Solved

Can not use function NZ in Query.

Posted on 2001-07-17
5
304 Views
Last Modified: 2012-08-14
I'm using MSDataShape as the 'Provider' and issuing a query to an ACCESS DB - see code snipit below. When I run the program I get error "Undefined function 'NZ' in expressin". If I run the query from ACCESS it works ok.
Code snipit -

Dim cnn As New ADODB.Connection
Dim rsT As New ADODB.Recordset
Dim strSQL As String
Dim datDate1 As Date
Dim datDate2 As Date

...
...

    'open a connection
    cnn.Open "Provider=MSDataShape;Data Provider=Microsoft.Jet.OLEDB.4.0;" _
        & "Data Source= myDB"
   
    datDate1 = dtpDate(0).Value
    datDate2 = dtpDate(1).Value
   
   
    strSQL = "SELECT PropertyRH.PropertyID, Sum(NZ(PropertyRH.RentRxd,0)) AS SumOfRentRxd " & _
                "FROM (LLords INNER JOIN properties ON LLords.ID = properties.LLID) INNER JOIN PropertyRH ON properties.ID = PropertyRH.PropertyID " & _
                "WHERE PropertyRH.WhenRxd BETWEEN #" & Format(datDate1, "mmm d yyyy") & "# " & _
                "AND #" & Format(datDate2, "mmm d yyyy") & "# AND " & _
                " LLords.ID = " & frmLandLords.txtLLname(2).Text & _
                " GROUP BY PropertyRH.PropertyID "

    'open the RS
    rsT.Open strSQL, cnn, adOpenStatic, adLockReadOnly, adCmdText




CAN ANYONE HELP?
0
Comment
Question by:mahmood_tariq
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 18

Accepted Solution

by:
deighton earned 100 total points
ID: 6290298
try replace

NZ(PropertyRH.RentRxd,0)

with

iif(isnull(PropertyRH.RentRxd),0,PropertyRH.RentRxd)
0
 
LVL 4

Expert Comment

by:VincentLawlor
ID: 6290299
Have you checked the format of the SQL string after you have formatted it.
i.e. add a watch and cut and paste into notepad to see what the exact sepression you are trying to run is ??

Vin.
0
 
LVL 43

Expert Comment

by:TimCottee
ID: 6290331
If the NZ function is in a module in access then you cannot use this from an external query. The data access layer does not have any ability to access functions in access modules. As deighton says, replace it with VBA or SQL standard syntax.
0
 
LVL 4

Expert Comment

by:VincentLawlor
ID: 6290389
Better still why not create a query in access and call it from VB. You should do it this way if at all possible.

eg.

New Query in Access

qryGetBlaBlah

PARAMETERS datDate1 TimeStamp, datDate2 TimeStamp,LLName Text;
"SELECT PropertyRH.PropertyID, Sum(NZ(PropertyRH.RentRxd,0)) AS SumOfRentRxd " & _
               "FROM (LLords INNER JOIN properties ON LLords.ID = properties.LLID) INNER JOIN PropertyRH
ON properties.ID = PropertyRH.PropertyID " & _
               "WHERE PropertyRH.WhenRxd BETWEEN #" & Format(datDate1, "mmm d yyyy") & "# " & _
               "AND #" & Format(datDate2, "mmm d yyyy") & "# AND " & _
               " LLords.ID = " & LLname & _
               " GROUP BY PropertyRH.PropertyID "

In VB

Dim cnn as New ADODB.Connection
Dim param as New ADODB.Parameter
Dim command as New ADODB.Command

command.CommandText = "qryBlahBlah"
command.CommandType = adCmdStoredProc

Set param = command.CreateParameter(, adDBTimeStamp, adParamInput, , datDate1)
command.Parameters.Append param

Set param = command.CreateParameter(, adDBTimeStamp, adParamInput, , datDate2)
command.Parameters.Append param

Set param = command.CreateParameter(, adVarWChar, adParamInput, Len(frmLandLords.txtLLname(2).Text), frmLandLords.txtLLname(2).Text)
command.Parameters.Append param

cnn.Open MyDSN

command.ActiveConnection = cnn

Dim rst as ADODB.Recorset
rst = command.Execute

That should work.

Vin.
0
 
LVL 18

Expert Comment

by:deighton
ID: 6293308
nz is a built in access function, why you can't use it via VB, I don't know.  The IIF alternative is the workaround.
0

Featured Post

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

738 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