Queries and User Defined Functions

  • Thread starter Thread starter Ben Hannon
  • Start date Start date
B

Ben Hannon

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
 
It may help if your post the SQL Strings of your Queries
and the code for your UDF.

Van T. Dinh
MVP (Access)
 
Author Distribution Query:

SELECT GetHostName() AS HOST_NAME,
AlphaOnly(CD_AUTHORS.AUTHOR_L_NAME+CD_AUTHORS.AUTHOR_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.AUTHOR_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
 
Back
Top