Andrew Jensen wrote:
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
Thanks Drew for the help. I cut and pasted my original search
expression, minus the "/" on the end, into SQL and the query worked just
fine. It just threw me a little because it worked fine in earlier versions.
John Batt
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
