Filtering out data for combobox

[wlanssens]

Hi,

I'm having a hard time getting the following to work, hopely you guys can help me out..

I'll start with explaining what my goal is.. I have a table with workorders, it has a column "date planned" so I can give all the work orders a date when to be executed.


On the other hand I also have a table with the ID of every technician and the dates when their vacation starts and ends, so 3 columns, 1 text, 2 dates.

To link the 2 I use a table "schedule" where I have 4 columns, "ID", "WO", "TechID". WO refers to the workorder nummer that can be found in the schedule table.

My end goal is to have a form with a subform "schedule" where I can see all the workorders in dataview, when I select a workorder in the table I want to fill up a combobox with all the technicians available, so that means that all the ones on vaction on the planned date of that WO are not included in the combobox..

I tryed making a select query, but I have no clue how to make a "select ... where (date) is not between ... and ..."

thanks!
Lanssens

[June7]

This will probably be a bit tricky. A nested subquery might be needed, something like:

SELECT TechID, TechName FROM Technicians WHERE TechID Not IN (SELECT TechID FROM Vacation WHERE Forms!formname.WODate BETWEEN [VacStart] AND [VacEnd]);

[wlanssens]

June7, thanks for the reply!
So I make 1 query, where I select the techID's from Vacation between VacStart and VacEnd.. Does VacStart and VacEnd stand for the column names in table vacation?
and then I make another query and insert the first query I made?

Sorry I'm a bit of a novice..

thanks!

[June7]

Yes, use your actual field names.

You can go to SQL View of query builder try copy/paste with appropriate edits of the suggested query then switch to Design View to examine that structure then see if it runs in Datasheet View.

I find that the NOT operator will work but it is really slow.

[wlanssens]

Thanks June7! Works great!