Row Source SELECT statement with Expr

[wally]

I am fairly new the using Access and seem to be stuck on something. Here is the code I am working with:

SELECT T_Keepers.KeeperID AS Expr1, T_Keepers.FirstName & " " & [LastName], T_Keepers.DepartmentID AS Expr2 FROM T_Keepers WHERE (((T_Keepers.DepartmentID)=Forms!F_Log!Departments)) ORDER BY T_Keepers.LastName;

I have a form called F_Log where information is pulled down from drop down menus. The way this is setup is after the user selects a Department it after updates a different combo box that has the above code in the Row Source so it shows only the Keepers (It's for a zoo so keepers are the employees) that are in the department selected ORDERED by their last name. I am trying to further create criteria where it only shows keepers in a selected department if they are a current employee. I have two yes/no check box on the T_Keepers table. There is a box for yes and a box for no. I have having a heck of a time figuring out how to add it to this statement.

Please help!

 

[sparks80]

Hi Wally,

Firstly I would be inclined to use a single check box that would indicate "yes" if it was ticked, and "no" if it was not.

You will need a yes/no field in the table T_keepers to store if the employee is current or not, in the example below called ysnCurrentEmployee. You will also need the checkbox on the form, here called chkCurrentEmployee.

SELECT T_Keepers.KeeperID AS Expr1, T_Keepers.FirstName & " " & [LastName], T_Keepers.DepartmentID AS Expr2 FROM T_Keepers WHERE (((T_Keepers.DepartmentID)=Forms!F_Log!Departments) AND ((ysnCurrentEmployee)=Forms!F_Log!chkCurrentEmployee)) ORDER BY T_Keepers.LastName;

 

[wally]

Thanks for the reply!

Is there a way to pull that information from another form or table so I don't need to have a check box on the F_Log form to make it work? I am hoping to make this form as simple as possible; all the user is entering is an animal training session with notes and some other things. I don't want them to have to enter if they are a current employee each time they want to log a training session ya know?

 

[sparks80]

Hi,

Yes, of course you can. The checkbox will allow you to display current or non-current staff. If you include the ysnCurrentEmployee field in the T_Keepers table you can just check that this is true, and you can then display current employees only (without the need for the combobox on the form).

Try this:

SELECT T_Keepers.KeeperID AS Expr1, T_Keepers.FirstName & " " & [LastName], T_Keepers.DepartmentID AS Expr2 FROM T_Keepers WHERE (((T_Keepers.DepartmentID)=Forms!F_Log!Departments) AND ((T_Keepers.ysnCurrentEmployee)=True)) ORDER BY T_Keepers.LastName;

 

[wally]

Worked perfectly! Thanks!