[AccessD] Data type mismatch

John,
Try and create the base query with a computed field like:

vMyText: "" & [MyTextFieldWithNullValues]

Another query on the base query handles left(vMyText,4) ok 
with no datatype mismatch.

Regards
Borge


---- Original message ----
>Date: Mon, 14 Jan 2008 08:54:52 -0500
>From: "jwcolby" <jwcolby at colbyconsulting.com>  
>Subject: [AccessD] Data type mismatch  
>To: "'Access Developers discussion and problem solving'" 
<accessd at databaseadvisors.com>
>
>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 
>
>-- 
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com