Data Type Mismatch even with CStr?

[Doctor]

I am trying to do a comparison between a query and a table. And one of the
comparison fields is the Postal Code field. But somehow this is not working
the way that I expect.

I have a function (pasted below) that takes a postal code and formats it so
that both tables have the same format to them for comparison

My query is based on a table and a query. I have forced the zip code in both
to be a string with CStr(). But still get the error. I have tried many
variations of my query. What am I missing?

My query:

SELECT ztblImportChurches.ImportID, *
FROM ztblImportChurches
WHERE (((ztblImportChurches.ImportID) In (SELECT ztblImportChurches.ImportID
FROM ztblImportChurches INNER JOIN zqryImportChurches1 ON
((ztblImportChurches.ChurchName = zqryImportChurches1.ChurchName)
OR (ztblImportChurches.ChurchName Is Null AND
zqryImportChurches1.ChurchName Is Null)) AND
((CStr(FormatZipFirst([ztblImportChurches].[Zip])) =
CStr([zqryImportChurches1].[ZipCode])) OR (ztblImportChurches.Zip Is Null AND
zqryImportChurches1.ZipCode Is Null))
GROUP BY ztblImportChurches.ImportID)));


My Function:
Function FormatZipFirst(sCode As String) As String
'Gets the first characters of USA and Canada Postal Codes properly formated
If sCode Like "#####" Then 'USA
FormatZipFirst = sCode
ElseIf sCode Like "#####-####" Then
FormatZipFirst = Left(sCode, 5)
ElseIf sCode Like "#####-" Then
FormatZipFirst = Left(sCode, 5)
ElseIf sCode Like "[A-Z]#[A-Z] #[A-Z]#" Then 'Canada Zips
FormatZipFirst = sCode
ElseIf sCode Like "[A-Z]#[A-Z]#[A-Z]#" Then
FormatZipFirst = Left(sCode, 3) & " " & Mid(sCode, 4, 3)
Else
FormatZipFirst = sCode
End If
End Function

Thanks in advance for the help.

 

[vanderghast]

If indeed

zqryImportChurches1.ZipCode Is Null

then

CStr([zqryImportChurches1].[ZipCode])


produces an error. Indeed, CStr cannot accept a NULL as argument:


? CStr(null)
.... ' run time error 94



Try, instead,


Nz(zqryImportChurches1.ZipCode, "" )


to transform the NULL into an empty string (as example).



Vanderghast, Access MVP

 

[Doctor]

Yes, I see that the null test is in my query, I should take that part out.
However, none of the fields are null. All have info in them. ??

If indeed

zqryImportChurches1.ZipCode Is Null

then

CStr([zqryImportChurches1].[ZipCode])


produces an error. Indeed, CStr cannot accept a NULL as argument:


? CStr(null)
... ' run time error 94



Try, instead,


Nz(zqryImportChurches1.ZipCode, "" )


to transform the NULL into an empty string (as example).



Vanderghast, Access MVP

I am trying to do a comparison between a query and a table. And one of the
comparison fields is the Postal Code field. But somehow this is not
working
the way that I expect.

I have a function (pasted below) that takes a postal code and formats it
so
that both tables have the same format to them for comparison

My query is based on a table and a query. I have forced the zip code in
both
to be a string with CStr(). But still get the error. I have tried many
variations of my query. What am I missing?

My query:

SELECT ztblImportChurches.ImportID, *
FROM ztblImportChurches
WHERE (((ztblImportChurches.ImportID) In (SELECT
ztblImportChurches.ImportID
FROM ztblImportChurches INNER JOIN zqryImportChurches1 ON
((ztblImportChurches.ChurchName = zqryImportChurches1.ChurchName)
OR (ztblImportChurches.ChurchName Is Null AND
zqryImportChurches1.ChurchName Is Null)) AND
((CStr(FormatZipFirst([ztblImportChurches].[Zip])) =
CStr([zqryImportChurches1].[ZipCode])) OR (ztblImportChurches.Zip Is Null
AND
zqryImportChurches1.ZipCode Is Null))
GROUP BY ztblImportChurches.ImportID)));


My Function:
Function FormatZipFirst(sCode As String) As String
'Gets the first characters of USA and Canada Postal Codes properly
formated
If sCode Like "#####" Then 'USA
FormatZipFirst = sCode
ElseIf sCode Like "#####-####" Then
FormatZipFirst = Left(sCode, 5)
ElseIf sCode Like "#####-" Then
FormatZipFirst = Left(sCode, 5)
ElseIf sCode Like "[A-Z]#[A-Z] #[A-Z]#" Then 'Canada Zips
FormatZipFirst = sCode
ElseIf sCode Like "[A-Z]#[A-Z]#[A-Z]#" Then
FormatZipFirst = Left(sCode, 3) & " " & Mid(sCode, 4, 3)
Else
FormatZipFirst = sCode
End If
End Function

Thanks in advance for the help.

 

[vanderghast]

Is the sub query runs fine all by itseld (in a new query) ?




SELECT ztblImportChurches.ImportID

FROM ztblImportChurches INNER JOIN zqryImportChurches1 ON
((ztblImportChurches.ChurchName = zqryImportChurches1.ChurchName)
OR (ztblImportChurches.ChurchName Is Null AND
zqryImportChurches1.ChurchName Is Null)) AND
((CStr(FormatZipFirst([ztblImportChurches].[Zip])) =
CStr([zqryImportChurches1].[ZipCode])) OR (ztblImportChurches.Zip Is Null
AND
zqryImportChurches1.ZipCode Is Null))

GROUP BY ztblImportChurches.ImportID





Note that if there is at least one ImportID value which is NULL, in that
sub-query, then NO record will be returned by the main query using the
IN(SELECT ... ) construction.

If there are no ChurchName which are NULL, neither any ZIP which are NULL,
then you could use:





SELECT ztblImportChurches.ImportID

FROM ztblImportChurches INNER JOIN zqryImportChurches1
ON ztblImportChurches.ChurchName = zqryImportChurches1.ChurchName AND
FormatZipFirst([ztblImportChurches].[Zip]) =
[zqryImportChurches1].[ZipCode]

WHERE NOT ztblImportChurches.ImportID IS NULL

GROUP BY ztblImportChurches.ImportID




If the query does not work, is it possible that the error comes from your
function FormatZipFirst ?



Vanderghast, Access MVP

Yes, I see that the null test is in my query, I should take that part out.
However, none of the fields are null. All have info in them. ??

If indeed

zqryImportChurches1.ZipCode Is Null

then

CStr([zqryImportChurches1].[ZipCode])


produces an error. Indeed, CStr cannot accept a NULL as argument:


? CStr(null)
... ' run time error 94



Try, instead,


Nz(zqryImportChurches1.ZipCode, "" )


to transform the NULL into an empty string (as example).



Vanderghast, Access MVP

I am trying to do a comparison between a query and a table. And one of
the
comparison fields is the Postal Code field. But somehow this is not
working
the way that I expect.

I have a function (pasted below) that takes a postal code and formats
it
so
that both tables have the same format to them for comparison

My query is based on a table and a query. I have forced the zip code in
both
to be a string with CStr(). But still get the error. I have tried many
variations of my query. What am I missing?

My query:

SELECT ztblImportChurches.ImportID, *
FROM ztblImportChurches
WHERE (((ztblImportChurches.ImportID) In (SELECT
ztblImportChurches.ImportID
FROM ztblImportChurches INNER JOIN zqryImportChurches1 ON
((ztblImportChurches.ChurchName = zqryImportChurches1.ChurchName)
OR (ztblImportChurches.ChurchName Is Null AND
zqryImportChurches1.ChurchName Is Null)) AND
((CStr(FormatZipFirst([ztblImportChurches].[Zip])) =
CStr([zqryImportChurches1].[ZipCode])) OR (ztblImportChurches.Zip Is
Null
AND
zqryImportChurches1.ZipCode Is Null))
GROUP BY ztblImportChurches.ImportID)));


My Function:
Function FormatZipFirst(sCode As String) As String
'Gets the first characters of USA and Canada Postal Codes properly
formated
If sCode Like "#####" Then 'USA
FormatZipFirst = sCode
ElseIf sCode Like "#####-####" Then
FormatZipFirst = Left(sCode, 5)
ElseIf sCode Like "#####-" Then
FormatZipFirst = Left(sCode, 5)
ElseIf sCode Like "[A-Z]#[A-Z] #[A-Z]#" Then 'Canada Zips
FormatZipFirst = sCode
ElseIf sCode Like "[A-Z]#[A-Z]#[A-Z]#" Then
FormatZipFirst = Left(sCode, 3) & " " & Mid(sCode, 4, 3)
Else
FormatZipFirst = sCode
End If
End Function

Thanks in advance for the help.