My SQL needs help

[HockeyFan]

I have a form that has a drop down combo box with the names of 3 people, and an All option. When the user makes a selection and presses the button, they see records from one of the three people, or all of them.
In the underlying query, I have a table where the names of the people come from in the drop down list (managerTbl) and I also have a table that contains the programmers(TblProgrammers).
So what happens, is when you select a manager name from the dropdown box and press the button, you get a list of all the names that report to that person.
When you select all, you get a list of all of the employees from all three managers.

Here's the problem: In each of the tables, I have a field called, 'active'. That way if a person is no longer working here, it is simple to remove them, by unchecking the checkmark in that field. The problem is that when I select a manager name and press the button, the list of programmers is correct which only shows active people, however when you select the 'All' option, it is showing everyone, including the inactive people.

I need to put something in the sql so when the 'All' option is selected, it will also not show the inactive people. here is my code.

SELECT DISTINCTROW tblProgramers.ProgrammerLastName, tblProgramers.ProgrammerFirstName, tblProgramers.PgmrId, tblProgramers.DirectMgr, ManagerTbl.ManagerFirstName, ManagerTbl.ManagerLastName, tblProgramers.Active
FROM ManagerTbl INNER JOIN tblProgramers ON ManagerTbl.PgmrId = tblProgramers.DirectMgr
WHERE (((tblProgramers.DirectMgr)=[Forms]![reportsfrm]![manager]) AND ((tblProgramers.Active)=-1)) OR ((([Forms]![reportsfrm]![manager])='*'));

Note: when I tried putting the, ((tblProgramers.Active)=-1) on the "all" side of the OR statement, this is the error it gave: "Data type mismatch in criteria expression"
Please help.

 

[dhookom]

Try this SQL:

SELECT DISTINCTROW tblProgramers.ProgrammerLastName, 
tblProgramers.ProgrammerFirstName, tblProgramers.PgmrId, 
tblProgramers.DirectMgr, ManagerTbl.ManagerFirstName, 
ManagerTbl.ManagerLastName, tblProgramers.Active
FROM ManagerTbl INNER JOIN tblProgramers ON ManagerTbl.PgmrId = tblProgramers.DirectMgr
WHERE (tblProgramers.DirectMgr=[Forms]![reportsfrm]![manager]
 OR [Forms]![reportsfrm]![manager]='*') AND 
tblProgramers.Active=-1;

Duane
Hook'D on Access
MS Access MVP

 

[HockeyFan]

It says the expression is typed incorrectly, or is too complex to be evaluated.
Graphically, the way I had it put the -1 in the active column on the same line as the "([Forms]![reportsfrm]![manager]).
Your suggestion put a -1 on the line with the ='*' criteria.

It seems this would be correct since both critera needs to say that programmer must be active for it to function as desired, however it's giving the error.

 

[dhookom]

Can you share some data types and if any field might be null?

Duane
Hook'D on Access
MS Access MVP

 

[HockeyFan]

In the programmers table, the 'active' field is yes/no and 'directMgr' field is number.
In the ManagerTbl table, it is the same, 'active' field is yes/no and 'directMgr' field is number.

When I select one of the manager's names from the dropdown list on the form, the query works great. When I change the selection in the dropdown box to the 'All' option and run the query, that's when it gives the following errror.

It says, "data type mismatch in criteria expression error".

 

[HockeyFan]

Also, Duane, you asked if any field might be null.... I didn't initially have any values in the 'directMgr' field in the ManagerTbl, however I just put values in there and it didn't change the result. still the same error, data type mismatch in criteria expression.

 

[dhookom]

I would change the Row Source of [Forms]![reportsfrm]![manager] to return a 0 rather than *. This would provide consistent data types and your query would be:

SELECT DISTINCTROW tblProgramers.ProgrammerLastName, 
tblProgramers.ProgrammerFirstName, tblProgramers.PgmrId, 
tblProgramers.DirectMgr, ManagerTbl.ManagerFirstName, 
ManagerTbl.ManagerLastName, tblProgramers.Active
FROM ManagerTbl INNER JOIN tblProgramers ON ManagerTbl.PgmrId = tblProgramers.DirectMgr
WHERE (tblProgramers.DirectMgr=[Forms]![reportsfrm]![manager]
 OR [Forms]![reportsfrm]![manager]=0) AND 
tblProgramers.Active=-1;

Duane
Hook'D on Access
MS Access MVP

 

[HockeyFan]

this is the combo box row source,
SELECT ManagerTbl.PgmrId, ManagerTbl.ManagerFirstName, ManagerTbl.ManagerLastName FROM ManagerTbl UNION SELECT "*", "<All>", "<Managers>" FROM ManagerTbl
ORDER BY ManagerTbl.ManagerLastName;

it's not returning an *, I just use that as a wildcard.

 

[dhookom]

Did you try change it as I suggested?

SELECT PgmrId, ManagerFirstName, ManagerLastName 
FROM ManagerTbl  
UNION 
SELECT 0, "<All>", "<Managers>" 
FROM ManagerTbl
ORDER BY 3,2;

Duane
Hook'D on Access
MS Access MVP

 

[HockeyFan]

Duane, you're a genius! it works great. I just had a question if you didn't mind... On the form, for the row source of the combo box, I was using the asterisk in my select statement and I thought that is what enabled it giving me all of the records. However you used a 0 and got the same result. I don't understand how that was able to work.

 

[dhookom]

You evaluate the where clause of the query and one of the conditions is possibly true:

 OR [Forms]![reportsfrm]![manager]=0

Duane
Hook'D on Access
MS Access MVP

 

[HockeyFan]

Oh, I see. Well thanks again. I greatly appreciate your help.