I am very new here and pretty much new to Access so be gentle..

I recently inherited an access database that was original written 7 or 8 years ago by someone no longer with our company. Over the years several people have messed with the tables, queries ect without documenting the changes. I am trying to make heads or tails of what the thing is doing and clean it up.

We have 10 queries that all are doing the same thing except for the filtering for the location or department. These queries all work on the same table, and are used to provide the data for a report which is run by user request.

SELECT findshortdated.N_UIN,
findshortdated.description,
findshortdated.slot,
findshortdated.N_TBOH,
findshortdated.UPC_Bk5,
findshortdated.[Sell By Days],
findshortdated.[Shelf Life],
findshortdated.[WH],
findshortdated.[Expiration Date],
([findshortdated]![Expiration Date]-[findshortdated]![sell by days])-([Findshortdated]![Safety Margin]+ [Findshortdated]![Shipping Allowance]) AS Expr1

FROM findshortdated, Parameter

WHERE (((([findshortdated]![Expiration Date]-[findshortdated]![sell by days])-([Findshortdated]![Safety Margin]+[Findshortdated]![Shipping Allowance]))<Date()+1)
AND ((findshortdated.[Always Check])=No)
AND ((Left([Findshortdated]![WH] & [Findshortdated]![slot],2))="4Z"))

ORDER BY findshortdated.slot;

The only thing that changes in these queries is the last "AND" portion of the WHERE clause. For 7 of the queries only the "4Z" changes to reflect the different WH requested (4T thru 4Z). The 8th where clause is more complex in that it adds 2 ORs to create a report that combines 3 of the 7 WH possibilities (I would like to do more of these)

The 9th and 10th Queries are slightly different
ie "AND ((Left([Findshortdated]![WH] & [Findshortdated]![slot],3))="4TD")" and "AND ((Left([Findshortdated]![WH] & [Findshortdated]![slot],3))="4TF")". They split a large WH into 2 based on the 1st character in the Slot field.

We are currently running these queries via the object view. I would like to create a menu to run them instead. Maybe its just me, but it seems that 10 queries to do essentially the same thing is a little silly.

I created a table called WH_Dept with 3 fields [Department Code],[Description] and [Pass to Queries],populated with the 10 different "And possibilities". A form with a combo box called "Select a Dept" to serve as a menu with its
Row Source set as "SELECT DISTINCTROW WH_Dept.[Pass to Queries], WH_Dept.[Department Code], WH_Dept.Description FROM WH_Dept ORDER BY WH_Dept.[Department Code]; " and the Bound Column set to 1.

I thought that I could go into the query and replace the where clause in the WH field with [Forms]![WH_Dept]![Select_a_Dept]in design view. It does not work. I know the output from the Combobox is correct as I have it displaying on the form when it is selected. So why does it not work? Is there a better way?


Thanks, in advance for any help or suggestions

Joe