[AccessD] SQL ANDs

On 1 Jul 2004 at 14:28, Martin Caro wrote:

> The first AND
> will always have data (ie the date range and the TargetPestPlantID) but if
> one (or more) of the remaining components are left vacant I do not want the
> whole construct not to work.
> 
> SELECT....................etc
> FROM dbo_ViewPWLocations
> 
> WHERE (((dbo_ViewPWLocations.ActivationDate) Between
> [Forms]![frmReports]![RptStartDate] And [Forms]![frmReports]![rptEndDate])
> AND
> ((dbo_ViewPWLocations.TargetPestPlantID)=[Forms]![frmReports]![TempPestID])

> AND ((dbo_ViewPWLocations.PremiseTypeID)=[Forms]![frmReports]![rptPremise])

Try 

AND 
iif(isnull([Forms]![frmReports]![rptPremise]),True,((dbo_ViewPWLocations.Premise
TypeID)=[Forms]![frmReports]![rptPremise])

etc

That way, if PremisesType is selected, you will only get one type, if none is 
selected, you will get all types.

 
-- 
Lexacorp Ltd
http://www.lexacorp.com.pg
Information Technology Consultancy, Software Development,System Support.