[AccessD] Data type mismatch

Gustav Brock Gustav at cactus.dk
Mon Jan 14 07:59:44 CST 2008


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 





More information about the AccessD mailing list