Query fails on non-indexed field

[billybobk]

This works:
SELECT accountno FROM tblTable WHERE Key5 = ''
(Key5 is indexed)

This fails:
SELECT accountno FROM tblTable WHERE Secr = ''
(Secr is NOT indexed)

Anybody ever seen this b4? Thanks!



--Bill
One may not reach the dawn save by the path of the night
--Kahlil Gibran

 

[tviman]

How is it failing? Are you not getting any results or do you get an actual error message? Also, what database are you using?

There's always a better way. The fun is trying to find it!

 

[billybobk]

It returns no results (even though there are records that meet the criteria). No error msg. It is Goldmine (dBase IV). Thanx

--Bill
One may not reach the dawn save by the path of the night
--Kahlil Gibran

 

[tviman]

Do the following:

1. check spelling (including upper/lower case) of field names.
2. if Secr is string data, make sure that the length of your test value is the exact length of the field. you may have to pad it with blanks (Secr = "abcde &quot. the cleaner way is like this: trim(Secr) = "abcde". (DO NOT do this if the field is numeric)
3. run the query with no active indexes.

I'm thinking that #2 will solve the problem. I've fallen into that trap before and darn near pulled my hair out before I found my error.

There's always a better way. The fun is trying to find it!

 

[billybobk]

I got the answer from a colleague. The solution is that the SECR column contains NULL values instead of empty strings (these values ARE treated differently!).
------------------------
SELECT accountno FROM tblTable WHERE Secr = '' OR
Secr IS NULL
------------------------
That worked! Thanks for your help tviman!


--Bill
One may not reach the dawn save by the path of the night
--Kahlil Gibran