?
Solved

Can not use function NZ in Query.

Posted on 2001-07-17
5
Medium Priority
?
313 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 300 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
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.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…
Suggested Courses

719 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