Zero Length Strings

[Frank]

I spent over an hour on the Internet & MSDN looking for an answer that
worked for this problem. Lots of answers, none work. It's time to post a
question.

Using: VB6, ADO, MS Jet 4.0

How do you retrieve records (lines, rows...whatever ) using SQL, searching
for a specific field containing a "zero length string". I have tried every
combination of quotes (single and double) and "IsEmpty". Many syntax
combinations did not return an error, but none returned a row with the field
containg an empty string (nothing returned). I, of course, can return all
and sequentially search for this condition in my program. I'd rather not.

The basic syntax:

SQLstr = "SELECT PartNo, Description FROM Inventory WHERE Description = '' "

The above example contains 2 single quotes after the =

Thanks for any help.

Frank

[Wayne Morgan]

Re: Zero Length Strings

A zero length string would normally be "" (two double quotes with no space
between them). Are you sure the value you're looking for is a zero length
string and not a Null? What happens if you try

.....WHERE Nz(Description, "") = ""

or

.....WHERE Description Is Null

--
Wayne Morgan
MS Access MVP


"Frank" <fnoell_cisi@ho tmail.com> wrote in message
news:10p9o8faob knnc8@corp.supe rnews.com...[color=blue]
>I spent over an hour on the Internet & MSDN looking for an answer that
> worked for this problem. Lots of answers, none work. It's time to post a
> question.
>
> Using: VB6, ADO, MS Jet 4.0
>
> How do you retrieve records (lines, rows...whatever ) using SQL, searching
> for a specific field containing a "zero length string". I have tried every
> combination of quotes (single and double) and "IsEmpty". Many syntax
> combinations did not return an error, but none returned a row with the
> field
> containg an empty string (nothing returned). I, of course, can return all
> and sequentially search for this condition in my program. I'd rather not.
>
> The basic syntax:
>
> SQLstr = "SELECT PartNo, Description FROM Inventory WHERE Description = ''
> "
>
> The above example contains 2 single quotes after the =
>
> Thanks for any help.
>
> Frank
>
>
>[/color]

[Wayne Morgan]

Re: Zero Length Strings

PS.
[color=blue]
> SQLstr = "SELECT PartNo, Description FROM Inventory WHERE Description = ''
> "[/color]


Also, to put quotes inside a quoted string such as this, you have to "double
them up". So, the above would be

SQLstr = "SELECT PartNo, Description FROM Inventory WHERE Description =
"""""

That's 5 double quotes at the end.

--
Wayne Morgan
MS Access MVP


"Frank" <fnoell_cisi@ho tmail.com> wrote in message
news:10p9o8faob knnc8@corp.supe rnews.com...[color=blue]
>I spent over an hour on the Internet & MSDN looking for an answer that
> worked for this problem. Lots of answers, none work. It's time to post a
> question.
>
> Using: VB6, ADO, MS Jet 4.0
>
> How do you retrieve records (lines, rows...whatever ) using SQL, searching
> for a specific field containing a "zero length string". I have tried every
> combination of quotes (single and double) and "IsEmpty". Many syntax
> combinations did not return an error, but none returned a row with the
> field
> containg an empty string (nothing returned). I, of course, can return all
> and sequentially search for this condition in my program. I'd rather not.
>
> The basic syntax:
>
> SQLstr = "SELECT PartNo, Description FROM Inventory WHERE Description = ''
> "
>
> The above example contains 2 single quotes after the =
>
> Thanks for any help.
>
> Frank
>
>
>[/color]

[Frank]

Re: Zero Length Strings

Tried all that - but see my other response.

Frank

"Wayne Morgan" <comprev_gothro ughthenewsgroup @hotmail.com> wrote in message
news:Xr5ld.2130 0$bP2.9972@news svr12.news.prod igy.com...[color=blue]
> PS.
>[color=green]
> > SQLstr = "SELECT PartNo, Description FROM Inventory WHERE Description =[/color][/color]
''[color=blue][color=green]
> > "[/color]
>
>
> Also, to put quotes inside a quoted string such as this, you have to[/color]
"double[color=blue]
> them up". So, the above would be
>
> SQLstr = "SELECT PartNo, Description FROM Inventory WHERE Description =
> """""
>
> That's 5 double quotes at the end.
>
> --
> Wayne Morgan
> MS Access MVP
>
>
> "Frank" <fnoell_cisi@ho tmail.com> wrote in message
> news:10p9o8faob knnc8@corp.supe rnews.com...[color=green]
> >I spent over an hour on the Internet & MSDN looking for an answer that
> > worked for this problem. Lots of answers, none work. It's time to post a
> > question.
> >
> > Using: VB6, ADO, MS Jet 4.0
> >
> > How do you retrieve records (lines, rows...whatever ) using SQL,[/color][/color]
searching[color=blue][color=green]
> > for a specific field containing a "zero length string". I have tried[/color][/color]
every[color=blue][color=green]
> > combination of quotes (single and double) and "IsEmpty". Many syntax
> > combinations did not return an error, but none returned a row with the
> > field
> > containg an empty string (nothing returned). I, of course, can return[/color][/color]
all[color=blue][color=green]
> > and sequentially search for this condition in my program. I'd rather[/color][/color]
not.[color=blue][color=green]
> >
> > The basic syntax:
> >
> > SQLstr = "SELECT PartNo, Description FROM Inventory WHERE Description =[/color][/color]
''[color=blue][color=green]
> > "
> >
> > The above example contains 2 single quotes after the =
> >
> > Thanks for any help.
> >
> > Frank
> >
> >
> >[/color]
>
>[/color]

[Frank]

Re: Zero Length Strings

Yes, I am sure they are not NULL strings. Of course, I am wrong! I really
thought I had tried that. Thanks for pointing out the obvious (red face).

Frank

"Wayne Morgan" <comprev_gothro ughthenewsgroup @hotmail.com> wrote in message
news:3q5ld.2129 8$bP2.14102@new ssvr12.news.pro digy.com...[color=blue]
> A zero length string would normally be "" (two double quotes with no space
> between them). Are you sure the value you're looking for is a zero length
> string and not a Null? What happens if you try
>
> ....WHERE Nz(Description, "") = ""
>
> or
>
> ....WHERE Description Is Null
>
> --
> Wayne Morgan
> MS Access MVP
>
>
> "Frank" <fnoell_cisi@ho tmail.com> wrote in message
> news:10p9o8faob knnc8@corp.supe rnews.com...[color=green]
> >I spent over an hour on the Internet & MSDN looking for an answer that
> > worked for this problem. Lots of answers, none work. It's time to post a
> > question.
> >
> > Using: VB6, ADO, MS Jet 4.0
> >
> > How do you retrieve records (lines, rows...whatever ) using SQL,[/color][/color]
searching[color=blue][color=green]
> > for a specific field containing a "zero length string". I have tried[/color][/color]
every[color=blue][color=green]
> > combination of quotes (single and double) and "IsEmpty". Many syntax
> > combinations did not return an error, but none returned a row with the
> > field
> > containg an empty string (nothing returned). I, of course, can return[/color][/color]
all[color=blue][color=green]
> > and sequentially search for this condition in my program. I'd rather[/color][/color]
not.[color=blue][color=green]
> >
> > The basic syntax:
> >
> > SQLstr = "SELECT PartNo, Description FROM Inventory WHERE Description =[/color][/color]
''[color=blue][color=green]
> > "
> >
> > The above example contains 2 single quotes after the =
> >
> > Thanks for any help.
> >
> > Frank
> >
> >
> >[/color]
>
>[/color]

[Alan Webb]

Re: Zero Length Strings

And . . .
In VBA it takes eight double quotes to embed double-quotes in a string. So,
"WHERE DESCRIPTION=""" & variable & """". To look for an empty string would
be: "WHERE DESCRIPTION=""" """".

"Frank" <fnoell_cisi@ho tmail.com> wrote in message
news:10p9rgaq86 5ag29@corp.supe rnews.com...[color=blue]
> Yes, I am sure they are not NULL strings. Of course, I am wrong! I
> really
> thought I had tried that. Thanks for pointing out the obvious (red face).
>
> Frank
>
> "Wayne Morgan" <comprev_gothro ughthenewsgroup @hotmail.com> wrote in
> message
> news:3q5ld.2129 8$bP2.14102@new ssvr12.news.pro digy.com...[color=green]
>> A zero length string would normally be "" (two double quotes with no
>> space
>> between them). Are you sure the value you're looking for is a zero length
>> string and not a Null? What happens if you try
>>
>> ....WHERE Nz(Description, "") = ""
>>
>> or
>>
>> ....WHERE Description Is Null
>>
>> --
>> Wayne Morgan
>> MS Access MVP
>>
>>
>> "Frank" <fnoell_cisi@ho tmail.com> wrote in message
>> news:10p9o8faob knnc8@corp.supe rnews.com...[color=darkred]
>> >I spent over an hour on the Internet & MSDN looking for an answer that
>> > worked for this problem. Lots of answers, none work. It's time to post
>> > a
>> > question.
>> >
>> > Using: VB6, ADO, MS Jet 4.0
>> >
>> > How do you retrieve records (lines, rows...whatever ) using SQL,[/color][/color]
> searching[color=green][color=darkred]
>> > for a specific field containing a "zero length string". I have tried[/color][/color]
> every[color=green][color=darkred]
>> > combination of quotes (single and double) and "IsEmpty". Many syntax
>> > combinations did not return an error, but none returned a row with the
>> > field
>> > containg an empty string (nothing returned). I, of course, can return[/color][/color]
> all[color=green][color=darkred]
>> > and sequentially search for this condition in my program. I'd rather[/color][/color]
> not.[color=green][color=darkred]
>> >
>> > The basic syntax:
>> >
>> > SQLstr = "SELECT PartNo, Description FROM Inventory WHERE Description =[/color][/color]
> ''[color=green][color=darkred]
>> > "
>> >
>> > The above example contains 2 single quotes after the =
>> >
>> > Thanks for any help.
>> >
>> > Frank
>> >
>> >
>> >[/color]
>>
>>[/color]
>
>[/color]

[Wayne Morgan]

Re: Zero Length Strings

Because once you get used to it, the multiple quotes are easier to type.
Other than that, both work just fine and, you're correct, the Chr$(34) is
much easier to read than trying to count quotes when you don't get them pair
correctly.

--
Wayne Morgan
MS Access MVP


"Chuck Grimsby" <c.grimsby@worl dnet.att.net.in valid> wrote in message
news:72jep054hl khg2hs0ne3u9esm mbc170993@4ax.c om...[color=blue]
>
> Why not just use Chr$(34)? Much more readable code.[/color]