Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 317
  • Last Modified:

Can not use function NZ in Query.

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
mahmood_tariq
Asked:
mahmood_tariq
  • 2
  • 2
1 Solution
 
deightonCommented:
try replace

NZ(PropertyRH.RentRxd,0)

with

iif(isnull(PropertyRH.RentRxd),0,PropertyRH.RentRxd)
0
 
VincentLawlorCommented:
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
 
TimCotteeCommented:
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
 
VincentLawlorCommented:
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
 
deightonCommented:
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now