More Query Fun!!!

[kmwhitt]

I have a query with the following fields:

Category >> Date >> Description >> Amount

There are only 4 possible choices for Category which I defined using a lookup. These categories are "Improvements", "Maintenance", "Operating Cost" and "Startup Cost".

I have a form with 2 checkboxes.

If the first checkbox is False, all records will be returned except those that have the category "Operating Cost" AND are dated prior to 7-7-04. Else if it is True, those records with "Operating Cost" AND dated prior to 7-7-04 are returned along with all the other records.

If the second checkbox is False, all records will be returned except those that have the category "Operating Cost" AND are dated after 7-7-04. Else if it is True, all records are returned including those that are "Operating Cost" AND dated after 7-7-04.

Can someone advise me on how to setup criteria within the query that will give me these results within the form?

Thanks,

Kevin

 

[Kraj]

I may be wrong so don't take this as gospel, but I don't believe you can use an IF in SQL (and therefore queries.) My suggestion would be to create two queries, one for each scenario. Then use code or a marco to use the IFs from the checkboxes to chose the correct query.

 

[KenHigg]

Did you ever gett this figured out?

 

[Keith27]

I think you can use an IIF expression in the criteria of your query. Jon K recently discussed this in great detail in this thread:-
http://www.access-programmers.co.uk/forums/showthread.php?t=69417

You have a more complicated scenario and may need to nest some IIF's in the expression.


Another way is to dynamically build the query in code based on the check boxes.