IIf CriteriaHelp (1 Viewer)

[systematic]

Hi all,

Sorry, this should be a simple answer. Have searched this forum and the web but have not been able to find a similar example.

Here is my criteria -

IIf([Forms]![frmModelling]![cboProcess]="ALL PROCESSES","",[Forms]![frmModelling]![cboProcess])

If cboProcess = "All Processes" show all records. Otherwise only show those matching cboProcess.

It's just the 'show all records' that is not working - I have a feeling it could be a syntax issue?

Thanks again,

Rob

 

[bob fitz]

Where are you using the criteria?

If cboProcess = "All Processes" show all records. Otherwise only show those matching cboProcess.

Where and how are these records to be shown?
Can you tell us a little more about what you are trying to do?

 

[systematic]

Hi Bob,

The records are shown on frmModelling in a listbox, which I requery after cboProcess is updated.

Cheers,

Rob

 

[bob fitz]

And where are you using the criteria?

 

[systematic]

Within the query criteria - sorry, nothing worse than a poster that doesn't give you the info you need!

It's just started returning the error (on form open) -

MS Access database does not recognize '[Forms]![frmModelling]![cboProcess]' as a valid field name or expression.

Which is strange, because the 'filter' on the listbox still works when I change cboProcess to anything except 'ALL PROCESSES'.

Cheers

Rob

 

[recyan]

Check if below gives some guidelines :

SELECT 
	myTable.TheID, 
	myTable.FieldToBeChecked
FROM 
	myTable
WHERE 
	1=1 
	AND
	IIF
		(
			[Forms]![frmModelling]![cboProcess]='ALL PROCESSES',
			myTable.FieldToBeChecked LIKE "*",
			myTable.FieldToBeChecked=[Forms]![frmModelling]![cboProcess]
		)

Thanks

 

[systematic]

Thanks recyan - I've tried it and got the same result again. Query works when I select any process except 'ALL PROCESSES' from the drop down box.

I have a couple of spin buttons that have also stopped working....I'm thinking perhaps the munchkins have corrupted my DB. I forget how much fun Access can be

Will let you know once I get it sorted!

 

[Brianwarnock]

Try


Where myTable.FieldToBeChecked=[Forms]![frmModelling]![cboProcess] OR [Forms]![frmModelling]![cboProcess]="ALL PROCESSES"


Brian