Ask Your Question
0

filter items between two dates

asked 2016-09-01 18:26:42 +0200

koolninja gravatar image

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

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2016-09-01 20:56:31 +0200

Ratslinger gravatar image

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.

edit flag offensive delete link more

Comments

Thanks its works..

koolninja gravatar imagekoolninja ( 2016-09-02 03:09:40 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-09-01 18:26:42 +0200

Seen: 1,230 times

Last updated: Sep 01 '16