[AccessD] Data type mismatch

Brilliant!

Rocky
 




 	
	

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Monday, January 14, 2008 6: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 


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

No virus found in this incoming message.
Checked by AVG Free Edition. 
Version: 7.5.516 / Virus Database: 269.19.2/1223 - Release Date: 1/13/2008
8:23 PM