Solved

Can not use function NZ in Query.

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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…

815 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

11 Experts available now in Live!

Get 1:1 Help Now