Filtering in query design view

[bxgirl]

Inherited this process

I have a form with 1 text field.

The button on the form runs a macro that does the following

- Opens a query
- Closes the query
- Transfers (exports) the data to excel

The query runs filtering on what is entered into the text field.

I have added a combo box to the form for more filtering.

The combo box has these values:

A
B
C

The table has this structure with a date entered into them:

A B
----------------------
1/1/07 null
null 1/1/07
null null
1/1/07 1/1/07


I would like to, based on what was selected in the combo box, do the following

1. if the value in the combo box is A then run query WHERE A is not null
2. if the value in the combo box is B then run query WHERE B is not null
3. if the value in the combo box is C then run query WHERE A and B is null

Can I do this keeping the existing macro, and modify the query in design view to accomodate these combination
where clauses?

 

[Pampers]

Not sure, but can you not just use an 'if then else'-statement in the query, like: tblYourTable.A: IIF([Combobox] = A, A, Null); tblYourTable.B: IIF([Combox] = B,Null). (And you could nest one in-then-else to check if the selected value is not null.)

Pampers
Keeping it simple can be complicated