IF QUERY TYPE

[netrusher]

I have a table with many fields and several are yes/no fields. This table is populated by a form. I am wanting to create a query that will provide data depending on whether or not some of these Yes/No fields are true. I will try and give an example below.

BUILD ID FIELDS:

E010=Yes/No C809=Yes/No F001=Yes/No C810=Yes/No etc. etc.

SOE FIELDS:

Process=Yes/No Events Added=Yes/No New SOE=Yes/No etc. etc.

The query I want to create is:

If Any of the Build ID Fields are True and any of the SOE Fields are True then I want to see certain other fields in the table. I would like not have to list all of the Yes/No Fields in the query and just use an IF statement if possible. I am also open to other suggestions such as VBA Code and/or SQL. I would also like to use a time range and I do have time Fields in the Table.

Thanks for any answers.

 

[PHV]

SQL code:
SELECT ...
FROM ...
WHERE (E010 OR C809 OR F001 OR C810 OR ...)
AND (Process OR [Events Added] OR [New SOE] OR ...)
AND ([time field] Between #09:00:00# And #17:59:59#)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lynchg]

I think the best way to handle this would be to build your SQL statement on the fly using VBA code. I don't know how many combinations of yes/no are involved, but you would be able to write code to handle all of your combinations. Either with nested If's or with a case statement, whichever seems to make the most sense.

If you don't know how to do that you could continue asking questions on this post or search for code for previous posts. I am sure there is a lot of info out there. Search for info on querydefs.

 

[dhookom]

NetRusher,
It looks like you have a somewhat un-normalized table structure. Creating queries on this type of data can be very difficult and long.

I could be wrong with this observation.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]