[AccessD] Data type mismatch

Function x(y As Variant) As String
  If y & "!" = "!" Then
    x = ""
  Else
    x = y
  End If
End Function
Max
 

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

I think it is where you use the + and not the &.
Mynull + mystring gives a null
Mynull & mystring gives mystring

Function x()
  Dim y As Variant, s As String
  y = Null
  s = "abc"
  Debug.Print y + s
  Debug.Print y & s
End Function

Max
 

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

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