i have created two table Issue and for filter Issue_filter.
“Issue” table have “Date” column and Issue_filter table have “Date” column, “Date2” column for selection for two dates.
but in query i am unsuccsesful to write correct queries for selection for between two dates and for other relate detail i am attaching ODB file for this…Search Form.odb
First, your refresh button belongs on the subform not on the main form.
Your dates aren’t working because they conflict. The current statement reads that if both dates are NULL select all - that’s fine. If date = 08/13/16 and date2 is NULL records with 08/13/16 will be selected - that’s OK. If date is NULL and date2 is 08/13/16 then records with 08/13/16 will be selected - that’s OK but just a duplicate of the previous selection. Now if date = 08/13/16 and date2 = 08/14/16 no records will be selected because it is looking for records with a date = 08/13/16 AND 08/14/16 which will never happen because a field will never be equal to two different values.
This statement will work but there are conditions:
SELECT "Issue".* FROM "Issue", "Issue_filter" WHERE ( ( ( UPPER ( "Issue"."CustomerName" ) LIKE '%' || UPPER ( "Issue_filter"."CustomerName" ) || '%' ) OR ( "Issue_filter"."CustomerName" IS NULL ) ) AND ( ( UPPER ( "Issue"."ProductName" ) LIKE '%' || UPPER ( "Issue_filter"."ProductName" ) || '%' ) OR ( "Issue_filter"."ProductName" IS NULL ) ) AND ( ( UPPER ( "Issue"."CategoryName" ) LIKE '%' || UPPER ( "Issue_filter"."CategoryName" ) || '%' ) OR ( "Issue_filter"."CategoryName" IS NULL ) ) AND ( ( ( "Issue"."IssueNo" ) LIKE '%' || ( "Issue_filter"."IssueNo" ) || '%' ) OR ( "Issue_filter"."IssueNo" IS NULL ) ) AND ((( "Issue_filter"."Date" IS NULL ) AND ( "Issue_filter"."Date2" IS NULL )) OR (( "Issue"."Date" >= "Issue_filter"."Date") AND ( "Issue"."Date" <= "Issue_filter"."Date2"))) )
To select ANY date, both date and date2 must be blank. To select a specific date, both date AND date2 MUST be EQUAL. To select a date range, date MUST be LESS than date2. This type of filtering is not usual.
Thanks its works…