Access: Data Type Mismatch using boolean function in query criteria

Question

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?

Answer 1

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.

Answer 2

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.