NZ Function in a MsAccess Query, fetching records to VB6 project

[JerryKlmns]

From the MS-Access enviroment, I use the NZ Function to convert nulls to zeros, from different fields of joint queries, in a parameter query gathering all needed fields, in order to sum them up.

From the VB6 enviroment, I execute a command object (TheCmd), over an open connection (Cnn), with query name (TheQuery), parameters (Dates:From - To) and pass the resulting set to a recordset object (rstRecordSource) like this

DateFrom = txtFrom.Text
Set TheParam1 = New ADODB.Parameter
With TheParam1
    .Type = adDate
    .Direction = adParamInput
    .Value = DateFrom
End With

DateTo = txtTo.Text
Set TheParam2 = New ADODB.Parameter
With TheParam2
    .Type = adDate
    .Direction = adParamInput
    .Value = DateTo
End With

Set TheCmd = New ADODB.Command
Set TheCmd.ActiveConnection = Cnn
With TheCmd
    .CommandText = TheQuery
    .CommandType = adCmdStoredProc
    .Parameters.Append TheParam1
    .Parameters.Append TheParam2
    .Prepared = True
End With
Set rstRecordSource = TheCmd.Execute

Set TheParam1 = Nothing
Set TheParam2 = Nothing
Set TheCmd = Nothing

Then I get the
err.Number -2147217900
err.Source Microsoft JET Database Engine
err.Description Undefined function 'nz' in expression.

Deleting the field using the nz function from the parameter query residing within MS-Access, everything works fine!

That 's a general call to many queries I need to run so, it is not possible to rewrite all these queries using that nz function!!!!!!!!!!!!!!

Any ideas ????? Thank 's in advance.

 

[MacChris]

Hi
plesa check in the Access DB the Query for
defined Parameter like TheParam1 type Text
an on the SQL criteria the [TheParam1] and not "TheParam1"

Chris

 

[johnwm]

You need to show us TheQuery in order to get any further

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

http://www.essexsteam.co.uk

for steam enthusiasts

 

[JerryKlmns]

MacChris and johnwm, thank you for your time.

TheQuery runs perfectly fine within Access mdb and if I delete the fields using the NZ function, runs perfectly fine from VB6, also. The problem is that from the VB6 enviroment, the NZ function (an Access function used to sum up fields -nulls and numbers- from different queries) is not recognized! That 's what I 'm trying to find out.

Well johnwm, a part of TheQuery looks like this

SELECT Branches.Description, Branches.MngCenter, Branches.Branch, Branches.Name, nz([B0_A_Report_N].[temaxia])+nz([B0_A_Report_X].[temaxia])+nz([B0_A_Report_P].[temaxia])+nz([B0_A_Report_R].[temaxia]) AS temaxia FROM (((Branches INNER JOIN B0_A_Report_N ON Branches.Branch = B0_A_Report_N.Branch) INNER JOIN B0_A_Report_P ON Branches.Branch = B0_A_Report_P.Branch) INNER JOIN B0_A_Report_X ON Branches.Branch = B0_A_Report_X.Branch) INNER JOIN [B0_A_Report_R] ON Branches.Branch = [B0_A_Report_R].Branch
ORDER BY Branches.Description, Branches.MngCenter, Branches.Branch;
[\code]

So, I have 4 simple fields from a table named 'Branches' and a fifth calculated field named 'temaxia', that uses this NZ Function. All fields that sum up to the field 'temaxia', come from queries retrieving data from different tables using the parameters mentioned before. Those fields ( i.e. ([B0_A_Report_X].[temaxia]) ) could be nulls or numbers. The real query uses more fields but they are simple like the first 4. And this is a part of one query. There exist 5 more queries of this kind.

 

[RoyVidar]

I don't think you're able to reach the Access.Application library, which is where the NZ function is a member, through a connection. I think the connection utilizes the Jet engine, not the Access.application. If I'm correct, I'm afraid a rewrite of the queries seems to be the alternative...

Roy-Vidar

 

[JerryKlmns]

Roy-Vidar, you are correct!

Well, I 've changed all NZs to an IIf([Field] Is Null;0;[Fiedl])inside every query.

Thank you all for your time and knowledge.