QBF connundrum

[Guest]

Hi I have a relatively straightforward training database which has a few
tables for courses, employees, and bookings.

I want to be able to search on 'employee' and 'course' criteria. So i
created a form with 2 text boxes for employee name and course name and a
'search ' button. I also created a macro and a query (based on the bookings
table - employee, course, start date

The query criteria for employee is this - Forms![Reports_Form]![Employee] Or
Forms![Reports_Form]![Employee] Is Null

The query criteria for course is - Forms![Reports_Form]![Course] Or
Forms![Reports_Form]![Course] Is Null

Does this look kinda OK? If any of you guys have any similar examples I
could look at it would be much appreciated!

Thanks,

Richard.

 

[Allen Browne]

I doubt that will work correctly.

By typing the 2 options with OR in the Criteria row under your field, you
are asking Access to equate those 2 values to the field. The Null won't work
that way.

Switch the query to design view.
Locate the WHERE clause.
Change it to something like this:

WHERE ((Forms![Reports_Form]![Employee] Is Null) OR
(Employee = Forms![Reports_Form]![Employee]))
AND ((Forms![Reports_Form]![Course] Is Null) OR
(Course = Forms![Reports_Form]![Course]))

The bracketing does matter when you mix AND and OR like that.

A much more powerful, flexible, and efficient approach to query-by-form is
to build the Filter dynamically, so it uses only the boxes where the user
actually entered something. You can do this with a form (using the Filter)
or with OpenReport (using the WhereCondition.) For an example of how to
build that, see:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

 

[Guest]

Thanks Allen I'll give that a try later!

I doubt that will work correctly.

By typing the 2 options with OR in the Criteria row under your field, you
are asking Access to equate those 2 values to the field. The Null won't work
that way.

Switch the query to design view.
Locate the WHERE clause.
Change it to something like this:

WHERE ((Forms![Reports_Form]![Employee] Is Null) OR
(Employee = Forms![Reports_Form]![Employee]))
AND ((Forms![Reports_Form]![Course] Is Null) OR
(Course = Forms![Reports_Form]![Course]))

The bracketing does matter when you mix AND and OR like that.

A much more powerful, flexible, and efficient approach to query-by-form is
to build the Filter dynamically, so it uses only the boxes where the user
actually entered something. You can do this with a form (using the Filter)
or with OpenReport (using the WhereCondition.) For an example of how to
build that, see:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi I have a relatively straightforward training database which has a few
tables for courses, employees, and bookings.

I want to be able to search on 'employee' and 'course' criteria. So i
created a form with 2 text boxes for employee name and course name and a
'search ' button. I also created a macro and a query (based on the
bookings
table - employee, course, start date

The query criteria for employee is this - Forms![Reports_Form]![Employee]
Or
Forms![Reports_Form]![Employee] Is Null

The query criteria for course is - Forms![Reports_Form]![Course] Or
Forms![Reports_Form]![Course] Is Null

Does this look kinda OK? If any of you guys have any similar examples I
could look at it would be much appreciated!

Thanks,

Richard.