By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,526 Members | 1,895 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,526 IT Pros & Developers. It's quick & easy.

Zero Length Strings

P: n/a
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

Nov 13 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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" <fn*********@hotmail.com> wrote in message
news:10*************@corp.supernews.com...
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

Nov 13 '05 #2

P: n/a
PS.
SQLstr = "SELECT PartNo, Description FROM Inventory WHERE Description = ''
"

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" <fn*********@hotmail.com> wrote in message
news:10*************@corp.supernews.com...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

Nov 13 '05 #3

P: n/a
Tried all that - but see my other response.

Frank

"Wayne Morgan" <co***************************@hotmail.com> wrote in message
news:Xr******************@newssvr12.news.prodigy.c om...
PS.
SQLstr = "SELECT PartNo, Description FROM Inventory WHERE Description = '' "

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" <fn*********@hotmail.com> wrote in message
news:10*************@corp.supernews.com...
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


Nov 13 '05 #4

P: n/a
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" <co***************************@hotmail.com> wrote in message
news:3q*******************@newssvr12.news.prodigy. com...
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" <fn*********@hotmail.com> wrote in message
news:10*************@corp.supernews.com...
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


Nov 13 '05 #5

P: n/a
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" <fn*********@hotmail.com> wrote in message
news:10*************@corp.supernews.com...
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" <co***************************@hotmail.com> wrote in
message
news:3q*******************@newssvr12.news.prodigy. com...
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" <fn*********@hotmail.com> wrote in message
news:10*************@corp.supernews.com...
>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
>
>
>



Nov 13 '05 #6

P: n/a
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.*******@worldnet.att.net.invalid> wrote in message
news:72********************************@4ax.com...

Why not just use Chr$(34)? Much more readable code.

Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.