show all records in qery critera

June 8, 2009 at 04:51:10
Specs: Windows 2000
I tried and have researched this puzzle but I give in. I need some professional help..

I wish to run a simple MS Access query for a list of records with an "Active" and "Closed" field. I am not the best VB guy in the world (still reading the "Dummy" book).

I tried to use IIf([Enter Status]="Active","Active",Null)or IIf([Enter Status]="Closed","Closed",Null) or IIf([Enter Status]="All",Like "[a-Z*]",Null) in the select query window. I tried the "not like" etc functions, but none seem to work.

I wish to show all records if the entry "All" is applied. It's probably really simple, but its making me bald... please aid. Thanks, Mark

See More: show all records in qery critera

Report •

June 8, 2009 at 06:22:17
If the field can contain STRING1 or STRING2 but either way you want the record, leave that criteria out of your query.

Report •

June 8, 2009 at 07:00:05
Sorry David, I do not understand.

I wish to be able to enter data using the [ ] in the query critera:
"Active" to show active records
"Closed" to show closed records
"All" to show all the records

The active and closed IIF formula's work, but if ALL is entered the IIF true statement seems to be ignored by MS Access

Report •

June 8, 2009 at 11:19:34
This is not MS Access specific.

select field from table where criteria = 'SOMETHING'

If you don't want to filter based on the contents of this field, either do not include it in the where clause ( best answer ) or set the where clause to look for any matching value such as

where field in ( 'STRING1', 'STRING2', 'STRING3' );

Report •

Related Solutions

June 8, 2009 at 23:42:02
This too, is not access specific, but a common method to return all is to make the field compared to itself.
Something like this in SQL

where Status = case @status when 'Active' then 'Active' when 'Closed' then 'Closed' when 'All' then Status else Null end

Which will

Report •

Ask Question