Query by form leaving some fileds blank

vandenherik

Registered User.
Local time
Today, 05:19
Joined
Jun 9, 2012
Messages
11
I am buidling a database to track action progress from different departments. I have set up 7 tables with 1 or 2 fields. I have a form where i can input the actions and that is working fine. I have a form where i allow the users to input search criteria, this form feeds a query. This form has 2 text boxes to input dates (the date that the action is logged and the date that the action needs to be closed out) and 5 combo boxes. All of them unbound. The query is build from all 7 tables and uses a primary key. I have populated the filed and table field in the query with the field name and table name (i.e field:actionstatus and table: ACTION MAIN). The criteria field has the format of [forms]![formname]![actionstatus] OR [forms]![formname]![actionstatus] isNull. This I thought would give me the opportunity to leave some of the fileds blank and still get the result. I.e. if i only selected the action status it would give me all the records that had the specific action status which can be open, closed or in progress.
I have no joins between the tables.
If i select just one field on the form that feeds the query i do not get any returns. If i leave all fields blank, all records are returned.
I am working in Access 2003.
Any thought please. If I can solve this issue i can build several applications that will help us manage the asset a lot easier.
Thanks in advance.
Dirk
 
you are on the right track but you cannot just put that in the criteria in design view, if you switch to SQL view you will see that the field is set to equal the whole expression
Fieldname =([forms]![formname]![actionstatus] OR [forms]![formname]![actionstatus] isNull)

Whereas you want
Fieldname=[forms]![formname]![actionstatus] OR [forms]![formname]![actionstatus] isNull

Make the changes in SQL view, watch the ()

Brian
 

Users who are viewing this thread

Back
Top Bottom