help writing query 1

[JOEYB99]

Can someone please help me out with some SQL?

Here is the current SQL for a simple query,

SELECT tbl_EmployeeList.[Active Employee], tbl_EmployeeList.Trade, tbl_EmployeeList.Employee, tbl_EmployeeList.[Bruce Power Number], tbl_EmployeeList.[Aecon Number]
FROM tbl_EmployeeList
GROUP BY tbl_EmployeeList.[Active Employee], tbl_EmployeeList.Trade, tbl_EmployeeList.Employee, tbl_EmployeeList.[Bruce Power Number], tbl_EmployeeList.[Aecon Number]
HAVING (((tbl_EmployeeList.[Active Employee])="active"))
ORDER BY tbl_EmployeeList.Trade;

I would like to add the parameter prompt for a field called EPSCA, which has a value of either "Y" or "N".

The output would be different if the user wants this field included as criteria, otherwise there is no change if this prompt excludes it, i.e. EPSCA = "N".

 

[lameid]

You could use something like....

Where [ESPCA] Like NZ([Enter ESPCA Y/N or blank for all],"") & "*"

That assumes the field is text.

Typically optional filters are added in the opening of the form / report via code as opposed to the query.

 

[JOEYB99]

Thanks lameid.

Couple of questions though.

1. where exactly do I insert this Where statement in my SQL?

2. What is meant by NZ?

 

[randy700]

The WHERE clause belongs after the FROM clause.
The best explanation for the NZ function is in the help files.


Randy