[AccessD] Data type mismatch

Hi John

That looks similar to the built-in function Nz() ...

/gustav

>>> jwcolby at colbyconsulting.com 14-01-2008 15:20:07 >>>
I wrote a function NoNullStr(varDataToCheck as variant) as string.  I then
just checked inside of the wrapper and if it was null, returned a "" else
return varDataToCheck.  I then wrap the problematic text field with that.  I
was really hoping that someone knew a fix that would just let a base query
do the work. 


John W. Colby
Colby Consulting
www.ColbyConsulting.com 
-----Original Message-----
From: accessd-bounces at databaseadvisors.com 
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Monday, January 14, 2008 9:00 AM
To: accessd at databaseadvisors.com 
Subject: Re: [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