Crieria based on Form

[r0nniem]

Hi,

I have a form with different job types to filter for queries and reports.

If Job Type = ALL is selected, I want the query to filter Job Type <>"GUARD", or;
if Job Type = Guard is selected, to filter on job type = Guard.

Not sure how to do this in the query criteria ... the Iif statement isn't working. I have also tried to have a 2nd column for the combo box Job Type on the form with:

JOB TYPE CRITERIA
ALL '<>"GUARD"' (I also tried 'Not In ("GUARD")'

and updating a txtJobType on the form with this and tried to filter on this in the query's with no sucess.

If you could help me out that would be appreciated ...

Thanks Ronnie

SELECT tmpZoneUtilisation.WPDate, tmpZoneUtilisation.Call_Ref_No, tmpZoneUtilisation.JobType
FROM tmpZoneUtilisation
WHERE (((tmpZoneUtilisation.WPDate) Between [forms]![frmUtil]![begtransdate] And [forms]![frmUtil]![endtransdate]) AND ((tmpZoneUtilisation.JobType)=IIf([forms]![frmUtil]![cmbJobType]="ALL",'<>"GUARD"',"GUARD")));

 

[dhookom]

Is the query the Record Source of a report or form?


You can't use the "<>" inside the IIf().

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

How are ya r0nniem . . .

Perhaps this:

[blue]((tmpZoneUtilisation.JobType) [purple][b]LIKE[/b][/purple] IIf([forms]![frmUtil]![cmbJobType]="ALL",[purple][b]'[!GUARD]*'[/b][/purple],'GUARD')));[/blue]

[blue]Your Thoughts? . . .[/blue]


See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]