Solved

Can not use function NZ in Query.

Posted on 2001-07-17
5
295 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
  • 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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now