Boolean data type mismatch

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

Brian

I have a public function that does Luhn validation (verifying whether a
credit card number is of valid format). It looks something like this:

Public Function LuhnCheck(CC As String) As Boolean
Dim LuhnVal As Integer
LuhnVal = blah, blah, blah 'several lines to evalute LuhnVal as a derivative
of CC
LuhnCheck = (LuhnVal Mod 10 = 0) 'will be True or False
End Function

This correctly evaluates to True or False when called from a form so that I
can do this:

If Not LuhnCheck(CCNumber) Then
MsgBox "The card number entered has failed Luhn validation.",
vbCritical, ""
End If

I now want to call this function from within a query, thus:

SELECT Customer.CustomerID, LuhnCheck([CCNumber]) AS LuhnCheck
FROM Customer;

This works fine, but returns LuhnCheck as -1 or 0. Any attempt to include a
WHERE clause involving the LuhnCheck result fails with "Data type mismatch in
criteria expression", as in both of these:

SELECT Customer.CustomerID, LuhnCheck([CCNumber]) AS LuhnCheck
FROM Customer
WHERE LuhnCheck([CCNumber])=False;

SELECT Customer.CustomerID, LuhnCheck([CCNumber]) AS LuhnCheck
FROM Customer
WHERE LuhnCheck([CCNumber])=0;

In fact, if I run the query manually (without the WHERE clause), then
attempt to manually filter either to the LuhnCheck or to exclude it, I get
the same data type mismatch error.

How can I get my function to return a usable result?
 
In ACCESS, True is represented as -1, and False as 0. So the values you are
getting from your function are correct.

Your queries should work the way you've posted them for the WHERE clauses.
Just for giggles, try this:


SELECT Customer.CustomerID, LuhnCheck([CCNumber]) AS LuhnCheck
FROM Customer
WHERE CBool(LuhnCheck([CCNumber]))=False;

SELECT Customer.CustomerID, LuhnCheck([CCNumber]) AS LuhnCheck
FROM Customer
WHERE CBool(LuhnCheck([CCNumber]))=0;

Let's see if explicitly casting the result from the function as a boolean in
the function is helpful (I know, it shouldn't be needed, but....).
 
Well, all you have to do is pass in one Null value and you are going to get an
error when you try to have LuhnCheck return NULL. Perhaps you are handling
that in the function, but you didn't post anything about that handling null
values. Also, if you the Luhncheck function does any division, you need to be
careful about not dividing by zero, and what if the credit card # is
improperly entered - for example oh instead of zero.

One error with one record when evaluating a calculation in a where clause can
definitely cause the entire query to fail

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Thanks, Ken.

I still get "Data type mismatch in criteria expression", even explicitly
casting the output as Boolean, upon any inclusion of a WHERE clause involving
that field or an attempt to manually filter after opening the query w/o a
WHERE clause. If I change my query to a make-table query, it creates that
field as an integer, whether the field is cast as Boolean or not.

SELECT Customer.CustomerID, Customer.CCNumber, CBool(LuhnCheck([CCNumber]))
AS Luhn
FROM Customer
WHERE (((Customer.CCNumber) Is Not Null));

The plot thickens: I changed my public function to ouput a string and use an
if/then loop to set it to "A" if the Luhn validation passes and "B" if it
does not. I still get a data mismatch in my query when attempting to filter
to "A" or "B". Perhaps it is in my Function. I have been using the function
for a couple of years, but only via VBA; this is the first attempt to call it
in a query. Many other functions work just fine in queries. Here is the full
function (the CCNumber field being passed to this funciton is a String):

Public Function LuhnCheck(CC As String) As Boolean
Dim strTemp As String
Dim strLength As Integer
Dim Pos As Integer
Dim Char As String
Dim CharVal As Integer
Dim LuhnVal As Integer
'remove non-numeric characters
strLength = Len(CC)
For Pos = 1 To strLength
Char = Mid(CC, Pos, 1)
If IsNumeric(Char) Then strTemp = strTemp & Char
Next Pos
'calculate
strLength = Len(strTemp)
For Pos = strLength To 1 Step -1
Char = Mid(strTemp, Pos, 1)
CharVal = Val(Char)
If (strLength - Pos) Mod 2 <> 0 Then 'even-numbered steps from end
of string
CharVal = CharVal * 2
If CharVal > 9 Then CharVal = CharVal - 9
End If
LuhnVal = LuhnVal + CharVal
Next Pos
LuhnCheck = (LuhnVal Mod 10 = 0)
End Function

I got the same result when I reduced it to this for testing:

Public Function LuhnCheck(CC As String) As Boolean
LuhnCheck = True
End Function

Going one step further, I removed the function argument so that my query was
just this:

SELECT Customer.CustomerID, Customer.CCNumber, LuhnCheck() AS Luhn
FROM Customer
WHERE (((Customer.CCNumber) Is Not Null) AND ((LuhnCheck())=True));

I then removed the argument from the function, thus:

Public Function LuhnCheck() As Boolean
LuhnCheck = True
End Function

Now, of course, I get all True, and the WHERE clause works. As a final test,
I explicitly passed the value of one CCNumber in the function, and it seems
to work just fine:

SELECT Customer.CustomerID
FROM Customer
WHERE (((Customer.CCNumber) Is Not Null) AND
((LuhnCheck("1234567890123456"))=False));

This makes it seem as though it is something aobut my CCNumber field, which
is a text field, length 20; however, that should not matter, since my
function tears it down, character-by-character and does not include its
original value in the result, just the Boolean calculated value (and the
correct value, at that).

I'm lost on this one.

Ken Snell MVP said:
In ACCESS, True is represented as -1, and False as 0. So the values you are
getting from your function are correct.

Your queries should work the way you've posted them for the WHERE clauses.
Just for giggles, try this:


SELECT Customer.CustomerID, LuhnCheck([CCNumber]) AS LuhnCheck
FROM Customer
WHERE CBool(LuhnCheck([CCNumber]))=False;

SELECT Customer.CustomerID, LuhnCheck([CCNumber]) AS LuhnCheck
FROM Customer
WHERE CBool(LuhnCheck([CCNumber]))=0;

Let's see if explicitly casting the result from the function as a boolean in
the function is helpful (I know, it shouldn't be needed, but....).
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/





Brian said:
I have a public function that does Luhn validation (verifying whether a
credit card number is of valid format). It looks something like this:

Public Function LuhnCheck(CC As String) As Boolean
Dim LuhnVal As Integer
LuhnVal = blah, blah, blah 'several lines to evalute LuhnVal as a
derivative
of CC
LuhnCheck = (LuhnVal Mod 10 = 0) 'will be True or False
End Function

This correctly evaluates to True or False when called from a form so that
I
can do this:

If Not LuhnCheck(CCNumber) Then
MsgBox "The card number entered has failed Luhn validation.",
vbCritical, ""
End If

I now want to call this function from within a query, thus:

SELECT Customer.CustomerID, LuhnCheck([CCNumber]) AS LuhnCheck
FROM Customer;

This works fine, but returns LuhnCheck as -1 or 0. Any attempt to include
a
WHERE clause involving the LuhnCheck result fails with "Data type mismatch
in
criteria expression", as in both of these:

SELECT Customer.CustomerID, LuhnCheck([CCNumber]) AS LuhnCheck
FROM Customer
WHERE LuhnCheck([CCNumber])=False;

SELECT Customer.CustomerID, LuhnCheck([CCNumber]) AS LuhnCheck
FROM Customer
WHERE LuhnCheck([CCNumber])=0;

In fact, if I run the query manually (without the WHERE clause), then
attempt to manually filter either to the LuhnCheck or to exclude it, I get
the same data type mismatch error.

How can I get my function to return a usable result?
Click to expand...
Click to expand...
 
Hi, John.

Good catch! I thought I had that handled in my query, but 'twas not so. I
oversimplified a little for clarity in my first post, thereby confusing
matters a little. I do have a WHERE clause in my live query so that it
exludes records where CCNumber is not null. My intermediate post in response
to Ken gave the full query, as well as the full function, where you will see
that it bypasses non-numberic characters (mainly to get rid of dashes, but
also handling the exclusion of literals).

Having said that, your post got me thinking about whether the first part of
my WHERE clause (CCNumber Is Not Null) would prevent the query from
attempting to validate every record against the LuhnCheck function. From VBA,
it was moot, because I could check for the null of the single value I was
validating before calling the function.

So, I changed the function's INPUT (argument) from a String to a Variant,
and things became a little more clear. Interestingly, while the query returns
the same set of records whether I handle the nulls or not, the query results
become is filter-able when I change the function's input to a Variant and
provide an If/Then loop to handle the nulls. It must be doing something with
the function, even for records excluded by the Is Not Null in the WHERE
clause.

I even tried using an IN(SELECT...) criteria for the Customer ID, but got
the same result. I did get it to work by build two queries and joining them
on CustomerID. The first selects CustomerID where CCNUmber is not null; the
second selects CustomerID where LuhnCheck(CCNumber) is False.

This leaves me just a little confused as to the order of evaluation of the
fields, but at least it works now. This is all vaguely (ore perhaps, not so
vaguely) remeniscent about failed IIF statements when the second part results
in a division-byzero error; if I recall correctly, Access evaluates both
segments of the IIf even if the first part evaluates to True.

Am I correct in seeing this as another manifestation of a similar behavior?

John Spencer said:
Well, all you have to do is pass in one Null value and you are going to get an
error when you try to have LuhnCheck return NULL. Perhaps you are handling
that in the function, but you didn't post anything about that handling null
values. Also, if you the Luhncheck function does any division, you need to be
careful about not dividing by zero, and what if the credit card # is
improperly entered - for example oh instead of zero.

One error with one record when evaluating a calculation in a where clause can
definitely cause the entire query to fail

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I have a public function that does Luhn validation (verifying whether a
credit card number is of valid format). It looks something like this:

Public Function LuhnCheck(CC As String) As Boolean
Dim LuhnVal As Integer
LuhnVal = blah, blah, blah 'several lines to evalute LuhnVal as a derivative
of CC
LuhnCheck = (LuhnVal Mod 10 = 0) 'will be True or False
End Function

This correctly evaluates to True or False when called from a form so that I
can do this:

If Not LuhnCheck(CCNumber) Then
MsgBox "The card number entered has failed Luhn validation.",
vbCritical, ""
End If

I now want to call this function from within a query, thus:

SELECT Customer.CustomerID, LuhnCheck([CCNumber]) AS LuhnCheck
FROM Customer;

This works fine, but returns LuhnCheck as -1 or 0. Any attempt to include a
WHERE clause involving the LuhnCheck result fails with "Data type mismatch in
criteria expression", as in both of these:

SELECT Customer.CustomerID, LuhnCheck([CCNumber]) AS LuhnCheck
FROM Customer
WHERE LuhnCheck([CCNumber])=False;

SELECT Customer.CustomerID, LuhnCheck([CCNumber]) AS LuhnCheck
FROM Customer
WHERE LuhnCheck([CCNumber])=0;

In fact, if I run the query manually (without the WHERE clause), then
attempt to manually filter either to the LuhnCheck or to exclude it, I get
the same data type mismatch error.

How can I get my function to return a usable result?
Click to expand...
Click to expand...
 
I think John's point about NULL value for CCNumber field being passed to the
function identifies the source of the mismatch error.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Brian said:
Thanks, Ken.

I still get "Data type mismatch in criteria expression", even explicitly
casting the output as Boolean, upon any inclusion of a WHERE clause
involving
that field or an attempt to manually filter after opening the query w/o a
WHERE clause. If I change my query to a make-table query, it creates that
field as an integer, whether the field is cast as Boolean or not.

SELECT Customer.CustomerID, Customer.CCNumber,
CBool(LuhnCheck([CCNumber]))
AS Luhn
FROM Customer
WHERE (((Customer.CCNumber) Is Not Null));

The plot thickens: I changed my public function to ouput a string and use
an
if/then loop to set it to "A" if the Luhn validation passes and "B" if it
does not. I still get a data mismatch in my query when attempting to
filter
to "A" or "B". Perhaps it is in my Function. I have been using the
function
for a couple of years, but only via VBA; this is the first attempt to call
it
in a query. Many other functions work just fine in queries. Here is the
full
function (the CCNumber field being passed to this funciton is a String):

Public Function LuhnCheck(CC As String) As Boolean
Dim strTemp As String
Dim strLength As Integer
Dim Pos As Integer
Dim Char As String
Dim CharVal As Integer
Dim LuhnVal As Integer
'remove non-numeric characters
strLength = Len(CC)
For Pos = 1 To strLength
Char = Mid(CC, Pos, 1)
If IsNumeric(Char) Then strTemp = strTemp & Char
Next Pos
'calculate
strLength = Len(strTemp)
For Pos = strLength To 1 Step -1
Char = Mid(strTemp, Pos, 1)
CharVal = Val(Char)
If (strLength - Pos) Mod 2 <> 0 Then 'even-numbered steps from end
of string
CharVal = CharVal * 2
If CharVal > 9 Then CharVal = CharVal - 9
End If
LuhnVal = LuhnVal + CharVal
Next Pos
LuhnCheck = (LuhnVal Mod 10 = 0)
End Function

I got the same result when I reduced it to this for testing:

Public Function LuhnCheck(CC As String) As Boolean
LuhnCheck = True
End Function

Going one step further, I removed the function argument so that my query
was
just this:

SELECT Customer.CustomerID, Customer.CCNumber, LuhnCheck() AS Luhn
FROM Customer
WHERE (((Customer.CCNumber) Is Not Null) AND ((LuhnCheck())=True));

I then removed the argument from the function, thus:

Public Function LuhnCheck() As Boolean
LuhnCheck = True
End Function

Now, of course, I get all True, and the WHERE clause works. As a final
test,
I explicitly passed the value of one CCNumber in the function, and it
seems
to work just fine:

SELECT Customer.CustomerID
FROM Customer
WHERE (((Customer.CCNumber) Is Not Null) AND
((LuhnCheck("1234567890123456"))=False));

This makes it seem as though it is something aobut my CCNumber field,
which
is a text field, length 20; however, that should not matter, since my
function tears it down, character-by-character and does not include its
original value in the result, just the Boolean calculated value (and the
correct value, at that).

I'm lost on this one.

Ken Snell MVP said:
In ACCESS, True is represented as -1, and False as 0. So the values you
are
getting from your function are correct.

Your queries should work the way you've posted them for the WHERE
clauses.
Just for giggles, try this:


SELECT Customer.CustomerID, LuhnCheck([CCNumber]) AS LuhnCheck
FROM Customer
WHERE CBool(LuhnCheck([CCNumber]))=False;

SELECT Customer.CustomerID, LuhnCheck([CCNumber]) AS LuhnCheck
FROM Customer
WHERE CBool(LuhnCheck([CCNumber]))=0;

Let's see if explicitly casting the result from the function as a boolean
in
the function is helpful (I know, it shouldn't be needed, but....).
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/





Brian said:
I have a public function that does Luhn validation (verifying whether a
credit card number is of valid format). It looks something like this:

Public Function LuhnCheck(CC As String) As Boolean
Dim LuhnVal As Integer
LuhnVal = blah, blah, blah 'several lines to evalute LuhnVal as a
derivative
of CC
LuhnCheck = (LuhnVal Mod 10 = 0) 'will be True or False
End Function

This correctly evaluates to True or False when called from a form so
that
I
can do this:

If Not LuhnCheck(CCNumber) Then
MsgBox "The card number entered has failed Luhn validation.",
vbCritical, ""
End If

I now want to call this function from within a query, thus:

SELECT Customer.CustomerID, LuhnCheck([CCNumber]) AS LuhnCheck
FROM Customer;

This works fine, but returns LuhnCheck as -1 or 0. Any attempt to
include
a
WHERE clause involving the LuhnCheck result fails with "Data type
mismatch
in
criteria expression", as in both of these:

SELECT Customer.CustomerID, LuhnCheck([CCNumber]) AS LuhnCheck
FROM Customer
WHERE LuhnCheck([CCNumber])=False;

SELECT Customer.CustomerID, LuhnCheck([CCNumber]) AS LuhnCheck
FROM Customer
WHERE LuhnCheck([CCNumber])=0;

In fact, if I run the query manually (without the WHERE clause), then
attempt to manually filter either to the LuhnCheck or to exclude it, I
get
the same data type mismatch error.

How can I get my function to return a usable result?
Click to expand...
Click to expand...
Click to expand...
 
IF your where clause has two conditions in it, they will BOTH be evaluated.

IF you nest two queries, then you can control the evaluation order.

SELECT *
FROM SomeTable
WHERE SomeField is Not Null

SELECT *
FROM SavedNoNullsQuery
WHERE LuhnCheck(CC) = True

You accomplished the same thing by using two queries and joining them. JOINS
get evaluated before the WHERE clause.

In VBA, you are correct that both the 2nd and 3rd arguments are evaluated. I
believe that in a query the IIF function may evaluates only one of the 2nd or
3rd arguments. You can test this with this simple expression

IIF(True,1,1/0)

In VBA that will generate an error. But used in an SQL statement, you will get
no errors and every record will return 1.



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Hi, John.

Good catch! I thought I had that handled in my query, but 'twas not so. I
oversimplified a little for clarity in my first post, thereby confusing
matters a little. I do have a WHERE clause in my live query so that it
exludes records where CCNumber is not null. My intermediate post in response
to Ken gave the full query, as well as the full function, where you will see
that it bypasses non-numberic characters (mainly to get rid of dashes, but
also handling the exclusion of literals).

Having said that, your post got me thinking about whether the first part of
my WHERE clause (CCNumber Is Not Null) would prevent the query from
attempting to validate every record against the LuhnCheck function. From VBA,
it was moot, because I could check for the null of the single value I was
validating before calling the function.

So, I changed the function's INPUT (argument) from a String to a Variant,
and things became a little more clear. Interestingly, while the query returns
the same set of records whether I handle the nulls or not, the query results
become is filter-able when I change the function's input to a Variant and
provide an If/Then loop to handle the nulls. It must be doing something with
the function, even for records excluded by the Is Not Null in the WHERE
clause.

I even tried using an IN(SELECT...) criteria for the Customer ID, but got
the same result. I did get it to work by build two queries and joining them
on CustomerID. The first selects CustomerID where CCNUmber is not null; the
second selects CustomerID where LuhnCheck(CCNumber) is False.

This leaves me just a little confused as to the order of evaluation of the
fields, but at least it works now. This is all vaguely (ore perhaps, not so
vaguely) remeniscent about failed IIF statements when the second part results
in a division-byzero error; if I recall correctly, Access evaluates both
segments of the IIf even if the first part evaluates to True.

Am I correct in seeing this as another manifestation of a similar behavior?

John Spencer said:
Well, all you have to do is pass in one Null value and you are going to get an
error when you try to have LuhnCheck return NULL. Perhaps you are handling
that in the function, but you didn't post anything about that handling null
values. Also, if you the Luhncheck function does any division, you need to be
careful about not dividing by zero, and what if the credit card # is
improperly entered - for example oh instead of zero.

One error with one record when evaluating a calculation in a where clause can
definitely cause the entire query to fail

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I have a public function that does Luhn validation (verifying whether a
credit card number is of valid format). It looks something like this:

Public Function LuhnCheck(CC As String) As Boolean
Dim LuhnVal As Integer
LuhnVal = blah, blah, blah 'several lines to evalute LuhnVal as a derivative
of CC
LuhnCheck = (LuhnVal Mod 10 = 0) 'will be True or False
End Function

This correctly evaluates to True or False when called from a form so that I
can do this:

If Not LuhnCheck(CCNumber) Then
MsgBox "The card number entered has failed Luhn validation.",
vbCritical, ""
End If

I now want to call this function from within a query, thus:

SELECT Customer.CustomerID, LuhnCheck([CCNumber]) AS LuhnCheck
FROM Customer;

This works fine, but returns LuhnCheck as -1 or 0. Any attempt to include a
WHERE clause involving the LuhnCheck result fails with "Data type mismatch in
criteria expression", as in both of these:

SELECT Customer.CustomerID, LuhnCheck([CCNumber]) AS LuhnCheck
FROM Customer
WHERE LuhnCheck([CCNumber])=False;

SELECT Customer.CustomerID, LuhnCheck([CCNumber]) AS LuhnCheck
FROM Customer
WHERE LuhnCheck([CCNumber])=0;

In fact, if I run the query manually (without the WHERE clause), then
attempt to manually filter either to the LuhnCheck or to exclude it, I get
the same data type mismatch error.

How can I get my function to return a usable result?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top