0 to select all records any other value to show matching records

[ProgramError]

I have a form with a combobox where the user can select a department code.
I am trying to show only those records with a matching department code or all of them if the department code selected is 0.
I have tried the following criteria in the query wizard

iif([forms]![selectfrm].[Deptcode] <>0,[forms]![selectfrm].[Deptcode])

I chave been able to show all records by emitting the criteria and show matching records by putting in the criteria.

Any help would be appreciated.

Ian Mayor (UK)
Program Error
9 times out of 10 I know what I'm talking about. This must be my tenth reply.

 

[SkipVought]

Hi,

When the user selects ZERO, the IIF statement returns FALSE. Is that the value that you want returned to your criteria?

I think not. You will actually have to REMOVE that WHERE statement in your SQL WHERE Clause.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[PWise]

try
where deptcode=iif([forms]![selectfrm].[Deptcode] =0,deptcode,[forms]![selectfrm].[Deptcode])

 

[PHV]

Criteria:
=[forms]![selectfrm]![Deptcode] OR [forms]![selectfrm]![Deptcode]=0


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