[AccessD] Data type mismatch

Hi John

Sometimes I concatenate the field and an empty string:

Where Len([fld] & "") > 0

/gustav

>>> jwcolby at colbyconsulting.com 14-01-2008 14:54:52 >>>
There are a lot of places where Access will throw a data type mismatch in a
query.  One of them is if you try to feed a null into a string query such as
left() inside of a query.  I have run into one such issue and need to try to
solve it.  So I created a base query that does a "Where [fld] is not Null"
and then build a query on top of the base query that uses the Left().
Unfortunately that doesn't work.  

You can demonstrate this issue quite easily by building a little table with
a text field, put in a few data points including at least one null.  Build a
base query that filters out the null, and then build a query on top of the
base that tries to use one of the string functions.

Now I can build a function wrapper that uses a variant as the data type and
checks for the null value inside of the wrapper, returning an empty string
(not a null) or the data passes in converted to a string type, but I would
rather not have to do that.  Has anyone found a way to prevent this datatype
mismatch scenario where you have tried to filter out the null values but you
still get the error?

John W. Colby
Colby Consulting
www.ColbyConsulting.com