[AccessD] Data type mismatch

Only if you use the + operator, not if you use the &.

Charlotte Foust 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Monday, January 14, 2008 6:17 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Data type mismatch

That is interesting.  I always thought that a null appended to a string
was a null. 


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 


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

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