Re: Problem using LEN function in WHERE clause on OLEDB Jet 4.0 driver

From: Val Mazur (group51a_at_hotmail.com)
Date: 10/14/04 

Date: Wed, 13 Oct 2004 21:45:52 -0400

Hi,

Actually using Trim VBA function in a SQL statement will not work in all
cases, because VBA functions are not supported when you call SQL statement
using Jet OLEDB provider. If value is NULL, then it is better to check for
NULL rather than trim it. 

-- 
Val Mazur
Microsoft MVP
"Brendan Reynolds" <brenreyn at indigo dot ie> wrote in message 
news:OGXtRCUsEHA.2536@TK2MSFTNGP11.phx.gbl...
> The field could contain a Null value or a string of one or more spaces. 
> Try Len(Trim$([Last Name] & "")) = 0
>
> The concatenation of [Last Name] and an empty string will transform any 
> Null values to empty strings, and the Trim$() will get rid of any leading 
> or trailing spaces.
> -- 
> Brendan Reynolds (MVP)
> http://brenreyn.blogspot.com
>
> The spammers and script-kiddies have succeeded in making it impossible for
> me to use a real e-mail address in public newsgroups. E-mail replies to
> this post will be deleted without being read. Any e-mail claiming to be
> from brenreyn at indigo dot ie that is not digitally signed by me with a
> GlobalSign digital certificate is a forgery and should be deleted without
> being read. Follow-up questions should in general be posted to the
> newsgroup, but if you have a good reason to send me e-mail, you'll find
> a useable e-mail address at the URL above.
>
>
> "John Wilson" <JohnWilson@discussions.microsoft.com> wrote in message 
> news:CEF6FC29-E217-4368-B259-DF3C352A60D9@microsoft.com...
>>I have a "standard" name and address style Access 2000 database where I am
>> trying to do the following in Access:
>>
>> SELECT * FROM Clients WHERE LEN([Last Name]) = 0
>>
>> Altho I have 2 records with no last name entered, Access does not return 
>> any
>> records. This same statement works on an SQL Server 2000 database with a
>> similar structure.
>>
>> Am I missing something here? Is this an unsupported function in the Jet 4
>> driver or a bug?
>>
>> If I do the following, it works just great:
>>
>> SELECT LEN([Last Name]) AS LastNameLength, [Last Name], [First Name] FROM
>> Clients ORDER BY LEN([Last Name])
>>
>> then the two blank records appear at the top of the list. If I add the 
>> WHERE
>> clause from the first example, it returns nothing.
>>
>> Any ideas?
>> Thanks
>> -- 
>> John L Wilson, Proprietor
>> Crossroads Software Solutions
>>
>
>

Relevant Pages

  • Re: How to get rid of those spaces efficiently?
    ... Trim in the SQL statement for e.g ... > In the returned records in a DataSet, all the VARCHARfield values are ... > efficiently, say, only trimmed VARCHAR() field values are returned? ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: SQL command help
    ... John wrote in message: ... I want to create an SQL statement that will return the following result ... VBA function, too. ... Check out Duane Hookoms "Generic Function To Concatenate Child Records" ...
    (comp.databases.ms-access)
  • Re: Textbox Web Control
    ... Karen, you can trim it as you retrieve data, that is taking this to your ... datalayer in your sql statement, use the trim function in sql like: ... Or after having bound your textbox that is after it is filled with data use ... the trim function of you textbox like i showed you in my previous post. ...
    (microsoft.public.dotnet.framework.aspnet.webcontrols)