All and combo box

[primagic]

I have the following sql for the record source of an unbound combox

SELECT ManagerID, BDName FROM tblManagers UNION SELECT 0 As ManagerID, "*All Managers*" As BDName FROM tblManagers ORDER BY BDName;

However when I select all and try to run the report, I get no results.

Here is my query for the report:

SELECT tblProspects.ProspectID, tblProspects.ProspectName, tblProspects.Manager, "*All Managers*" AS Expr1, tblProspects.Segment, tblProspects.ProspectCurrency, tblProspects.Status, tblProspects.Channel, tblProspects.AlliancePartner, tblProspects.Industry, tblProspects.Region, tblProspects.County, tblProspects.Campaign, tblProspects.Turnover, tblProspects.TotalspendavailableERAReview, tblProspects.TotalSpendSigned, tblProspects.SpendSignedforImmediateStart, tblProspects.NumberOfStaff, tblProspects.DateOfFirstMeeting, tblProspects.ForecastDecisionMonth, tblProspects.ProgressedtoClient, Format([ForecastDecisionMonth],"mmm") AS MonthText
FROM tblProspects
WHERE (((tblProspects.Manager)=[Forms]![frmMainMenu]![sfrmWorkspace].[Form]![cboManager]) AND ((tblProspects.ForecastDecisionMonth) Between [Forms]![frmMainMenu]![sfrmWorkspace].[Form]![txtFromDate] And [Forms]![frmMainMenu]![sfrmWorkspace].[Form]![txtToDate]) AND ((tblProspects.ProgressedtoClient)=No)) OR ((("*All Managers*")=[Forms]![frmMainMenu]![sfrmWorkspace].[Form]![cboManager]) AND ((tblProspects.ForecastDecisionMonth) Between [Forms]![frmMainMenu]![sfrmWorkspace].[Form]![txtFromDate] And [Forms]![frmMainMenu]![sfrmWorkspace].[Form]![txtToDate]) AND ((tblProspects.ProgressedtoClient)=No));

If I select any other option other than ALL I get results

 

[PHV]

What about this where clause ?

WHERE (tblProspects.Manager=[Forms]![frmMainMenu]![sfrmWorkspace].[Form]![cboManager] OR [Forms]![frmMainMenu]![sfrmWorkspace].[Form]![cboManager]=0)
 AND tblProspects.ForecastDecisionMonth Between [Forms]![frmMainMenu]![sfrmWorkspace].[Form]![txtFromDate] And [Forms]![frmMainMenu]![sfrmWorkspace].[Form]![txtToDate]
 AND tblProspects.ProgressedtoClient=No

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886