Undefined function in expression

  • Thread starter Thread starter Brian
  • Start date Start date
B

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?
 
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
 
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.

Allen Browne said:
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.

Brian said:
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?
Click to expand...
Click to expand...
 
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.
Brian said:
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.

Allen Browne said:
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.

Brian said:
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?
Click to expand...
Click to expand...
Click to expand...
 
That should do it. As always, I appreciate your thoughtful and generous help,
Allen.

Allen Browne said:
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.
Brian said:
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.

Allen Browne said:
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?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top