Query question

[T_Tronix]

I am trying to create a form to find when an Employee is available or not. I have a table called projects where I have a start_date and an end_date and an Employee designated to that project. Now I want to find based on that which employee is available or not to work on a new project.

Something like where the user will input a date and after the query runs it will show a list of all the employees available for that date.

[June7]

Sounds like a Find Unmatched query.

Find all employees from Employees table that don't have a record in Projects between the indicated dates.

Try the Find Unmatched query wizard.

[T_Tronix]

Yes but then how do I add into that query something like "WHERE txtDate (date from search criteria text box) is not in_between Start_Date and End_Date from Projects table.

[T_Tronix]

OK lets say I have the following:

Project1 - Employee1 - 01/Nov/2013 till 05/Nov/2013
Project2 - Employee2 - 02/Nov/2013 till 09/Nov/2013
Employee3 and Employee4 are not delegated to any projects yet

I want a query to show me who is available on 03/Nov/2013

Query result should be Employee3 and 4

[June7]

I've never had to make much use of Find Unmatched so just did some testing. Throwing in date parameters on the Find Unmatched doesn't work.

I had to create a separate query on the Projects table with the date parameters then use that query in the Find Unmatched instead of the table.

It can be done as an all-in-one nested subquery.

SELECT Employees.EmployeeID, Query1.EmployeeID
FROM Employees LEFT JOIN (SELECT EmployeeID FROM Projects WHERE ...) AS Query1 ON Employees.EmployeeID = Query1.EmployeeID WHERE Query1.EmployeeID Is Null;

Use query builder to get the Projects query right then copy/paste into the Find Unmatched query.

[T_Tronix]

I managed to get it working using the following query:

PARAMETERS SDate DateTime, EDate DateTime;
SELECT Employees.First_Name, Employees.Last_Name
FROM Employees LEFT JOIN Projects ON Employees.[ACF2_ID] = Projects.[Primary_ACF2ID]
WHERE (((Projects.Primary_ACF2ID) Is Null)) Or ((([SDate]) Not Between Projects.Start_Date And Projects.End_Date) and ([EDate] Not Between Projects.Start_Date And Projects.End_Date));

Now I have another question:

I have 2 fields in my form, one is the User ID and one is the User Name, can I have Access automatically update the User ID field based on the User Name chosen from the combo-box? If so, how would I go about this?

My Access is rusted, did not use it for like 6-7 years now

[T_Tronix]

OK figured it out, when the combobox had a change I wrote do the following:

Private Sub cmbPrimary_Change()Me!txtPrimary_ACF2ID = Me!cmbPrimary.Column(0)
End Sub

Column(0) = ACF2_ID connected to the combobox choice.

[T_Tronix]

Now I want to find out if I can color the combobox fields (highlight or change the color of the font of some of the choices) based on the Query I've made earlier...

Basically I want to identify which of the employee names that are in the list were triggered by the query.

[June7]

Nice! It was the OR operator I did not think of.

Bing: Access VBA color listbox item

http://www.access-programmers.co.uk/...d.php?t=226478

http://social.msdn.microsoft.com/For...r?forum=isvvba