Where boolean and broken query wizard

[claytoncramer]

Importing my spreadsheet was easy, but when I used the query wizard, I asked to see records where year =1911 (or "1911" in case someone was confused about its type), I got all records. Here is the SQL the wizard created:

Code: Select all

SELECT "MassMurder"."ID" AS "ID", "MassMurder"."month" AS "month", "MassMurder"."date" AS "date", "MassMurder"."year" AS "year", "MassMurder"."city" AS "city", "MassMurder"."state" AS "state", "MassMurder"."dead victim count" AS "dead victim count", "MassMurder"."undead victim count" AS "undead victim count", "MassMurder"."suicide?" AS "suicide?", "MassMurder"."UNKNOWN" AS "UNKNOWN", "MassMurder"."AX" AS "AX", "MassMurder"."HATCHET" AS "HATCHET", "MassMurder"."KNIFE" AS "KNIFE", "MassMurder"."OTHER SHARP" AS "OTHER SHARP", "MassMurder"."BLUNT" AS "BLUNT", "MassMurder"."EXPLOSIVE" AS "EXPLOSIVE", "MassMurder"."POISON" AS "POISON", "MassMurder"."STRANGLE" AS "STRANGLE", "MassMurder"."DROWN" AS "DROWN", "MassMurder"."ARSON" AS "ARSON", "MassMurder"."HANG" AS "HANG", "MassMurder"."OTHER" AS "OTHER", "MassMurder"."FIREARM (unidentified)" AS "FIREARM (unidentified)", "MassMurder"."shotgun" AS "shotgun", "MassMurder"."rifle" AS "rifle", "MassMurder"."pistol" AS "pistol", "MassMurder"."machine gun" AS "machine gun", "MassMurder"."cause" AS "cause", "MassMurder"."category" AS "category" FROM "MassMurder" "MassMurder"

Shouldn't there be a WHERE clause? So I added "where "MassMurder"."year" = 1911" to the SQL and saving it worked. When I wanted to add another WHERE clause to test for boolean values:

Code: Select all

 where "MassMurder"."AX" = On OR "MassMurder"."HATCHET" = On

I tried TRUE, 1, On. What should I be testing against?

[robleyd]

What type are the fields you want to select?

[UnklDonald418]

For each FROM you can have a single WHERE clause.
Assuming "MassMurder"."year" is a numeric type field plus "MassMurder"."AX" and "MassMurder"."HATCHET" are Boolean fields, try

Code: Select all

WHERE "MassMurder"."year" = 1911 
AND  ("MassMurder"."AX" = TRUE OR "MassMurder"."HATCHET" = TRUE)

If "MassMurder"."year" is text then use '1911'

Edit: If "MassMurder"."date" is a Date type field in the table "MassMurder", try this where clause Code: Select all WHERE YEAR("MassMurder"."date") = 1911 AND  ("MassMurder"."AX" = TRUE OR "MassMurder"."HATCHET" = TRUE)

[UnklDonald418]

It just occurred to me, with all those potential search options you should look at this example
[Example #3] Filter/Search with Forms (leveraging SubForms)
Download the sample database "LIKE_user_input_filter.odb"