Access: Data Type Mismatch using boolean function in query criteria

I have a VBA function IsValidEmail() that returns a boolean. I have a query that calls this function: Expr1: IsValidEmail([E-Mail]). When I run the query, it shows -1 for True and 0 for False. So far so good.

Now I want to filter the query to only show invalid emails. I'm using the Query Designer, so I just add a value of 0 开发者_如何学运维to the Criteria field. This gives me a "Data Type Mismatch" error. So does "0" (with quotes) and False. How am I supposed to specify criteria for a boolean function?

For a boolean column, "0" will definitely give you the "Data type mismatch in criteria expression" error. However, 0 or False without quotes should work. I don't understand why they are generating the same error.

See if you can produce a working query by editing the SQL directly. Create a new query, switch to SQL View and paste in this statement (replacing YourTableName with the name of your table).

SELECT IsValidEmail([E-Mail]) AS valid_email
FROM YourTableName
WHERE IsValidEmail([E-Mail]) = False;

Will your query run without error when you create it that way?

Update: Since that query also produced the same error, all I can suggest is trying this one without any criteria.

SELECT
    IsValidEmail([E-Mail]) AS valid_email,
    TypeName(IsValidEmail([E-Mail])) AS type_of_valid_email
FROM YourTableName;

However, that seems like a long shot because you already told us your earlier attempt without criteria ran without error. If this doesn't identify the problem, would you consider emailing me a stripped down copy of your database? Let me know if you're interested and I'll give you my email address.

The error was caused by the fact that some of the records in my table have a null E-Mail. My query has a where condition to exclude null E-Mail records, so when I ran it with no condition on the IsValidEmail column my function was only called for records with a non-null E-Mail. However, when I added the condition on IsValidEmail it called the function for every record, and the error came from trying to pass null to a function expecting a string.

Another way to say all that:

SELECT [E-Mail],
       IsValidEmail([E-Mail]) <--Executed only for rows matching where clause
FROM   Contacts
WHERE  IsValidEmail([E-Mail]) = False; <-- Gets executed for all rows

Changing my query expression from IsValidEmail([E-Mail]) to IsValidEmail(nz([E-Mail],"X")) resolved the issue.