John Batt wrote:
This query is designed to show all beads of type Delica sorted
(ascending) by their DB numbers. The search conditions were Delica =
true. This search condition is missing from the overview. When I
click on finish to run the query I get the following error:
/SQL Status: HY000
Error code: 1000
Syntax error in SQL expression
SELECT "FMG"."Color", "FMG"."reorder", "FMG"."Delica", "FMG"."DB#"
FROM "FMG" "FMG" WHERE ("FMG"."Delica" = )/
You will notice that it says "WHERE ("FMG"."Delica" = )" the search
criteria "true" is missing. It would seem that doing searches on
Boolean fields is not permitted under 2.0.3 since I can search all
other fields in the database with no problem. The problem is not
local to this database either, I setup the Recipe database, entered
several recipes two of which were marked vegetarian, and constructed a
query to search for recipes of type vegetarian (also Boolean) and got
the same results. Neither is it local to using the wizard to create
queries, the same thing happens using design view. I can enter any
search condition in the criterion field EXCEPT = true or = false. My
question, How do I get around this problem
Hopefully this message will get on to you, as my connecton to the net
has been pretty much gone for the last 10 days.
What follows is a recap of a post originally put up on the oooforum
board, I believe it is still valid.
I will use a table named for examples
words ( word as varchar(50), used as boolean )
The Query wizard seems to have an software annomily (*BUG*) that will
not allow the criteria to be entered in the GUI. You can use the GUI to
make all your field selections and joins and such, but you must flip to
the SQL view to add the criteria for Boolean fields.
SELECT "word", "used" FROM "words" "words" WHERE "used" = 'TRUE'
SELECT "word", "used" FROM "words" "words" WHERE "used" = 'FALSE'
SELECT "word", "used" FROM "words" "words" WHERE "used" IS NULL
SELECT "word", "used" FROM "words" "words" WHERE "used" IS NOT NULL
Above are statement forms that will generate accurate results with
EscapeProcessing turned on or off in Base.
SELECT "word", "used" FROM "words" "words" WHERE "used" = null
SELECT "word", "used" FROM "words" "words" WHERE "used" <> null
HOWEVER with these last two satements the Base query parser will accept
them and generate erroneous result sets. If you turn escapeProcessing
off, then HSQL will return zero record result sets for either one.
Drew Jensen
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
