Queries and User Defined Functions

Hi,

I have two queries that use a user defined function called AlphaOnly.
This function strips all non Alpha characters for making a Key for joining
my two queries on for a final append query. However, I just found out that
when Access runs these queries and tries to evaluate the join, it errors
with a "Data type mismatch in criteria expression". I've run the two
queries separately and it gives me what is expected, but when I try to do a
Filter on that field, I get the same error. I assume this has to do with an
issue with User Defined functions? Any insight on this would be
appreciated. Worst case scenario is I create temp tables for this data.
BTW, my function returns a String. I tried changing it to Variant but that
didn't work either.

Ben,

The most common problem I have when I get the "data type
mismatch" error is that I am passing a null value to a
function that is expecting a string. Check to make sure
that is not what is happening.

HTH
Dale

-----Original Message-----
Hi,

I have two queries that use a user defined function
called AlphaOnly.
This function strips all non Alpha characters for making
a Key for joining
my two queries on for a final append query. However, I
just found out that
when Access runs these queries and tries to evaluate the
join, it errors
with a "Data type mismatch in criteria expression". I've
run the two
queries separately and it gives me what is expected, but
when I try to do a
Filter on that field, I get the same error. I assume
this has to do with an
issue with User Defined functions? Any insight on this
would be
appreciated. Worst case scenario is I create temp tables
for this data.
BTW, my function returns a String. I tried changing it
to Variant but that
didn't work either.


.

It may help if your post the SQL Strings of your Queries
and the code for your UDF.

Van T. Dinh
MVP (Access)



-----Original Message-----
Hi,

I have two queries that use a user defined function
called AlphaOnly.
This function strips all non Alpha characters for making
a Key for joining
my two queries on for a final append query. However, I
just found out that
when Access runs these queries and tries to evaluate the
join, it errors
with a "Data type mismatch in criteria expression". I've
run the two
queries separately and it gives me what is expected, but
when I try to do a
Filter on that field, I get the same error. I assume
this has to do with an
issue with User Defined functions? Any insight on this
would be
appreciated. Worst case scenario is I create temp tables
for this data.
BTW, my function returns a String. I tried changing it
to Variant but that
didn't work either.


.

Author Distribution Query:

SELECT GetHostName() AS HOST_NAME,
AlphaOnly(CD_AUTHORS.AUTHOR_L_NAME+CD_AUTHORS.AUTH OR_F_NAME) AS AUTHOR,
CD_ACCT_CROSS.ACCT_NUM AS SHIP_TO, CD_ACCT_CROSS.ACCT_OT AS ORDER_TYPE,
CD_ACCT_CROSS.ACCT_LOC AS LOCATION, CD_DISTRIBUTIONS.QTY
FROM CD_DISTRIBUTION_NAMES INNER JOIN (CD_AUTHORS INNER JOIN
(CD_DISTRIBUTIONS INNER JOIN CD_ACCT_CROSS ON (CD_DISTRIBUTIONS.ACCT_CODE =
CD_ACCT_CROSS.ACCT_CODE) AND (CD_DISTRIBUTIONS.DIS_ID =
CD_ACCT_CROSS.DIS_ID)) ON (CD_AUTHORS.DIS_ID = CD_DISTRIBUTIONS.DIS_ID) AND
(CD_AUTHORS.AUTHOR_ID = CD_DISTRIBUTIONS.AUTHOR_ID)) ON
CD_DISTRIBUTION_NAMES.DIS_ID = CD_AUTHORS.DIS_ID
WHERE
(((CD_DISTRIBUTION_NAMES.DIS_CLIENT)=[Forms]![Distributions]![cmbClient])
AND ((CD_DISTRIBUTION_NAMES.DIS_NAME)=[Forms]![Distributions]![cmbName]))
ORDER BY AlphaOnly(CD_AUTHORS.AUTHOR_L_NAME+CD_AUTHORS.AUTH OR_F_NAME);


Get Distribution Items Query:

SELECT GetHostName() AS HOST_NAME, Trim([LH_NAME]) AS LG_LHNAME,
NCIP1_LIST_HEADER.LH_SHNBR AS LG_SHNBR, NCIP1_LIST_TITLE.LT_ISBN AS LG_ISBN,
AlphaOnly(UCase([TITLE_AUTHOR])) AS AUTHOR
FROM ([Shopper Number Distributions] INNER JOIN (NCIP1_LIST_HEADER INNER
JOIN NCIP1_LIST_TITLE ON (NCIP1_LIST_HEADER.LH_NAME =
NCIP1_LIST_TITLE.LT_LHNAME) AND (NCIP1_LIST_HEADER.LH_SHNBR =
NCIP1_LIST_TITLE.LT_SHNBR)) ON [Shopper Number Distributions].CNASHNBR =
NCIP1_LIST_HEADER.LH_SHNBR) INNER JOIN NCIP1_TITLE ON
NCIP1_LIST_TITLE.LT_ISBN = NCIP1_TITLE.TITLE_ISBN
WHERE (((Trim([LH_NAME]))=[Forms]![Distributions]![txtListName]));

And finally the AlphaOnly function:

Public Function AlphaOnly(ByVal Data As String, Optional ByVal Spaces As
Boolean = False) As String
Dim Index As Integer
Dim Temp As String
Dim Found As Boolean

Temp = ""
For Index = 1 To Len(Data)
If (Mid(Data, Index, 1) = "A" And Mid(Data, Index, 1) = "Z") Or
(Mid(Data, Index, 1) = "a" And Mid(Data, Index, 1) = "z") Then
Temp = Temp + Mid(Data, Index, 1)
Found = False
ElseIf Spaces And (Mid(Data, Index, 1) = Space(1) Or Mid(Data,
Index, 1) = ",") And Not Found Then
Temp = Temp + Space(1)
Found = True
End If
Next Index

AlphaOnly = Temp
End Function

When I try to JOIN or Filter on the Author column that is created by the
AlphaOnly function, I get the "Data type mismatch" error. I can filter the
Host_Name column without a problem which is populated by the gethostname()
function. I think it has to do with AlphaOnly requiring parameters. I have
since started using two temp tables to append this data into and then do the
join based on those tables data. (Host_Name and Author are the Join columns)
This new way works fine, but I'd still like to know why I couldn't do the
same thing by linking these two queries on the Author column.

Ben

"Ben Hannon" wrote in message
...
Hi,

I have two queries that use a user defined function called AlphaOnly.
This function strips all non Alpha characters for making a Key for joining
my two queries on for a final append query. However, I just found out
that
when Access runs these queries and tries to evaluate the join, it errors
with a "Data type mismatch in criteria expression". I've run the two
queries separately and it gives me what is expected, but when I try to do
a
Filter on that field, I get the same error. I assume this has to do with
an
issue with User Defined functions? Any insight on this would be
appreciated. Worst case scenario is I create temp tables for this data.
BTW, my function returns a String. I tried changing it to Variant but
that
didn't work either.