Undefined function in expression

[Brian]

I have this SELECT query in an Access 2003 DB:

SELECT Customer.CustomerID, GetBalanceByCustomerID([BillToCustomerID]) AS
Balance
FROM Customer
WHERE (((Customer.BillToCustomerID)=[parmCustomerID]));

GetBalanceByCustomerID is this custom function:

Public Function GetBalanceByCustomerID(CustomerID As Variant) As Currency
If IsNull(CustomerID) Then
GetBalanceByCustomerID = Null
Else
GetBalanceByCustomerID = Nz(DLookup("[Balance]", "[LookupBalance]",
"[BillToCustomerID] = " & [CustomerID]), 0)
End If
End Function

The query runs fine within Access. However, I have a developer that wrote an
ADO.NET application that passes the query name & parameter via a connection
string and now calls the query using an ADO.NET application. The query works
(i.e. ADO.NET correctly receives the results) when I leave out the function,
but when I include the function, I get the following error:

Undefined function 'GetBalanceByCustomerID' in expression

Here is the connection string:

connectionString="Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=D:\Data\Apps\HartCraft\DeliveryCraft\Web\DCraftWeb.mdb"

Why would ADO.NET even try to interpret the function? Shouldn't that be
internal to Access, with ADO.NET just receiving the results of the query as
called?

 

[Allen Browne]

That's right: you will have to omit the VBA functions if you want to run the
query in another environment.

Since you have a table (or query) named LookupBalance with (presumably) one
record per customer, create a query that uses both your Customer table and
your LookupBalance table/query. In the query design window, join them on the
CustomerID.

The query now gives you the balance without a function call.

If you actually need something more complex than the example you gave, you
may be able to solve the problem by using a subquery rather than a function
call. Here's an introduction to subqueries:
http://allenbrowne.com/subquery-02.html

 

[Brian]

Thank you, Allen.

I had tried the join to call the query directly. I had to do an outer join,
though, to avoid excluding customers having no activity and therefore null
balances. When I attempted to set nulls to 0 using NZ, the call from ADO.NET
also failed when it encountered the NZ function, so I had to omit even that.

In the end, I did use the outer join and simply told my web (ADO.NET)
developer to anticipate the possibility of a null balance--which, given the
fact that he has chosen XML as the data transfer format, resolves to an
absent field rather than a null.

Closely related, and in the same application, do you have a suggestion for
getting Date() to work? In an APPEND query called by the ADO.NET application,
I have a field, Date(), that I use to populate a date field as a datestamp
for the record insertion. This query fails (without error) with the Date()
function included. The Date() function does seem to work on a SELECT query
called via ADO.NET, however. I may have to set the date as a parameter and
have the web developer explicitly send in the Date as an argument.

This is my first foray into cross-platform work. I thought I had this
application all done for my client, and then they decided to let customers do
their own input via the Internet, and thus the ADO.NET/XML data interchange
development.

That's right: you will have to omit the VBA functions if you want to run the
query in another environment.

Since you have a table (or query) named LookupBalance with (presumably) one
record per customer, create a query that uses both your Customer table and
your LookupBalance table/query. In the query design window, join them on the
CustomerID.

The query now gives you the balance without a function call.

If you actually need something more complex than the example you gave, you
may be able to solve the problem by using a subquery rather than a function
call. Here's an introduction to subqueries:
http://allenbrowne.com/subquery-02.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have this SELECT query in an Access 2003 DB:

SELECT Customer.CustomerID, GetBalanceByCustomerID([BillToCustomerID]) AS
Balance
FROM Customer
WHERE (((Customer.BillToCustomerID)=[parmCustomerID]));

GetBalanceByCustomerID is this custom function:

Public Function GetBalanceByCustomerID(CustomerID As Variant) As Currency
If IsNull(CustomerID) Then
GetBalanceByCustomerID = Null
Else
GetBalanceByCustomerID = Nz(DLookup("[Balance]", "[LookupBalance]",
"[BillToCustomerID] = " & [CustomerID]), 0)
End If
End Function

The query runs fine within Access. However, I have a developer that wrote
an
ADO.NET application that passes the query name & parameter via a
connection
string and now calls the query using an ADO.NET application. The query
works
(i.e. ADO.NET correctly receives the results) when I leave out the
function,
but when I include the function, I get the following error:

Undefined function 'GetBalanceByCustomerID' in expression

Here is the connection string:

connectionString="Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=D:\Data\Apps\HartCraft\DeliveryCraft\Web\DCraftWeb.mdb"

Why would ADO.NET even try to interpret the function? Shouldn't that be
internal to Access, with ADO.NET just receiving the results of the query
as
called?

 

[Allen Browne]

Someone who uses ADO.NET regularly may be able to help better.

IIf() is native to JET, so you may be able to use:
IIf([SomeField] Is Null, 0, [SomeField])
instead of:
Nz([SomeField], 0)
More info:
http://allenbrowne.com/QueryPerfIssue.html#Nz
--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thank you, Allen.

I had tried the join to call the query directly. I had to do an outer
join,
though, to avoid excluding customers having no activity and therefore null
balances. When I attempted to set nulls to 0 using NZ, the call from
ADO.NET
also failed when it encountered the NZ function, so I had to omit even
that.

In the end, I did use the outer join and simply told my web (ADO.NET)
developer to anticipate the possibility of a null balance--which, given
the
fact that he has chosen XML as the data transfer format, resolves to an
absent field rather than a null.

Closely related, and in the same application, do you have a suggestion
for
getting Date() to work? In an APPEND query called by the ADO.NET
application,
I have a field, Date(), that I use to populate a date field as a datestamp
for the record insertion. This query fails (without error) with the Date()
function included. The Date() function does seem to work on a SELECT query
called via ADO.NET, however. I may have to set the date as a parameter and
have the web developer explicitly send in the Date as an argument.

This is my first foray into cross-platform work. I thought I had this
application all done for my client, and then they decided to let customers
do
their own input via the Internet, and thus the ADO.NET/XML data
interchange
development.

That's right: you will have to omit the VBA functions if you want to run
the
query in another environment.

Since you have a table (or query) named LookupBalance with (presumably)
one
record per customer, create a query that uses both your Customer table
and
your LookupBalance table/query. In the query design window, join them on
the
CustomerID.

The query now gives you the balance without a function call.

If you actually need something more complex than the example you gave,
you
may be able to solve the problem by using a subquery rather than a
function
call. Here's an introduction to subqueries:
http://allenbrowne.com/subquery-02.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have this SELECT query in an Access 2003 DB:

SELECT Customer.CustomerID, GetBalanceByCustomerID([BillToCustomerID])
AS
Balance
FROM Customer
WHERE (((Customer.BillToCustomerID)=[parmCustomerID]));

GetBalanceByCustomerID is this custom function:

Public Function GetBalanceByCustomerID(CustomerID As Variant) As
Currency
If IsNull(CustomerID) Then
GetBalanceByCustomerID = Null
Else
GetBalanceByCustomerID = Nz(DLookup("[Balance]", "[LookupBalance]",
"[BillToCustomerID] = " & [CustomerID]), 0)
End If
End Function

The query runs fine within Access. However, I have a developer that
wrote
an
ADO.NET application that passes the query name & parameter via a
connection
string and now calls the query using an ADO.NET application. The query
works
(i.e. ADO.NET correctly receives the results) when I leave out the
function,
but when I include the function, I get the following error:

Undefined function 'GetBalanceByCustomerID' in expression

Here is the connection string:

connectionString="Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=D:\Data\Apps\HartCraft\DeliveryCraft\Web\DCraftWeb.mdb"

Why would ADO.NET even try to interpret the function? Shouldn't that be
internal to Access, with ADO.NET just receiving the results of the
query
as
called?

 

[Brian]

That should do it. As always, I appreciate your thoughtful and generous help,
Allen.

Someone who uses ADO.NET regularly may be able to help better.

IIf() is native to JET, so you may be able to use:
IIf([SomeField] Is Null, 0, [SomeField])
instead of:
Nz([SomeField], 0)
More info:
http://allenbrowne.com/QueryPerfIssue.html#Nz
--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thank you, Allen.

I had tried the join to call the query directly. I had to do an outer
join,
though, to avoid excluding customers having no activity and therefore null
balances. When I attempted to set nulls to 0 using NZ, the call from
ADO.NET
also failed when it encountered the NZ function, so I had to omit even
that.

In the end, I did use the outer join and simply told my web (ADO.NET)
developer to anticipate the possibility of a null balance--which, given
the
fact that he has chosen XML as the data transfer format, resolves to an
absent field rather than a null.

Closely related, and in the same application, do you have a suggestion
for
getting Date() to work? In an APPEND query called by the ADO.NET
application,
I have a field, Date(), that I use to populate a date field as a datestamp
for the record insertion. This query fails (without error) with the Date()
function included. The Date() function does seem to work on a SELECT query
called via ADO.NET, however. I may have to set the date as a parameter and
have the web developer explicitly send in the Date as an argument.

This is my first foray into cross-platform work. I thought I had this
application all done for my client, and then they decided to let customers
do
their own input via the Internet, and thus the ADO.NET/XML data
interchange
development.

That's right: you will have to omit the VBA functions if you want to run
the
query in another environment.

Since you have a table (or query) named LookupBalance with (presumably)
one
record per customer, create a query that uses both your Customer table
and
your LookupBalance table/query. In the query design window, join them on
the
CustomerID.

The query now gives you the balance without a function call.

If you actually need something more complex than the example you gave,
you
may be able to solve the problem by using a subquery rather than a
function
call. Here's an introduction to subqueries:
http://allenbrowne.com/subquery-02.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have this SELECT query in an Access 2003 DB:

SELECT Customer.CustomerID, GetBalanceByCustomerID([BillToCustomerID])
AS
Balance
FROM Customer
WHERE (((Customer.BillToCustomerID)=[parmCustomerID]));

GetBalanceByCustomerID is this custom function:

Public Function GetBalanceByCustomerID(CustomerID As Variant) As
Currency
If IsNull(CustomerID) Then
GetBalanceByCustomerID = Null
Else
GetBalanceByCustomerID = Nz(DLookup("[Balance]", "[LookupBalance]",
"[BillToCustomerID] = " & [CustomerID]), 0)
End If
End Function

The query runs fine within Access. However, I have a developer that
wrote
an
ADO.NET application that passes the query name & parameter via a
connection
string and now calls the query using an ADO.NET application. The query
works
(i.e. ADO.NET correctly receives the results) when I leave out the
function,
but when I include the function, I get the following error:

Undefined function 'GetBalanceByCustomerID' in expression

Here is the connection string:

connectionString="Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=D:\Data\Apps\HartCraft\DeliveryCraft\Web\DCraftWeb.mdb"

Why would ADO.NET even try to interpret the function? Shouldn't that be
internal to Access, with ADO.NET just receiving the results of the
query
as
called?