Complicated Like Expression

[Ed Ardzinski]

I have a scheduler application, and with it a "task board" for my crew.
Recently I added a bunch of "Daily" tasks to our tracking and they clutter up
the task board. So I'd like to allow the users to view 1) all tasks, 2) just
the daily tasks, 3) all but the daily tasks. This is controlled via a field
called "Occurrence"...

I created an option frame on my form, and was able to get my first 2 options
to work, but the 3rd (all but daily) stumps me. Here's the expression I've
been trying to use:

Like IIf([fraType]=2,7,IIf([fraType]=3,([tblTasks].[Occurrence])<>7,"*"))

Originally I had the following:

Like IIf([fraType]=2,7,IIf([fraType]=3,([tblTasks].[Occurrence])=1 Or
[tblTasks].[Occurrence])=2 Or [tblTasks].[Occurrence])=3 Or
[tblTasks].[Occurrence])=4 Or [tblTasks].[Occurrence])=5 Or
[tblTasks].[Occurrence])=6,"*"))

In the above it was the parser that put inthe explicit field reference - I
origianlly only typed in "1 or 2 or..."

I know I can do the following - a drop down list of Occurance types and
restrict the selected type, or try to alter the SQL for the list boxes with
some in-line code. The first option doesn't get me the funtionality I want,
the second seems like I'd be making things too complex. I suppose I could
also limit my optins to "All Tasks" and "Exclude Daily Tasks"...but I'd like
to be able to look at the daily tasks by themselves.

Appreciate any ideas. Thanks!

 

[kingston via AccessMonster.com]

If I understand correctly:

1) [Occurrence]=*
2) [Occurrence]=7
3) [Occurrence]<>7

I'm not sure what [fraType] does but I don't think you need it to do this.
If this is correct, you'll want:

IIF(allTasks, [Occurrence]=*, IIF(DailyTasks, [Occurrence]=7, [Occurrence]<>7)
)

Here I've made 3) your default, but you can change this to any of the other
two. Otherwise, you'll need another embedded IIF statement with a different
default criteria.

I have a scheduler application, and with it a "task board" for my crew.
Recently I added a bunch of "Daily" tasks to our tracking and they clutter up
the task board. So I'd like to allow the users to view 1) all tasks, 2) just
the daily tasks, 3) all but the daily tasks. This is controlled via a field
called "Occurrence"...

I created an option frame on my form, and was able to get my first 2 options
to work, but the 3rd (all but daily) stumps me. Here's the expression I've
been trying to use:

Like IIf([fraType]=2,7,IIf([fraType]=3,([tblTasks].[Occurrence])<>7,"*"))

Originally I had the following:

Like IIf([fraType]=2,7,IIf([fraType]=3,([tblTasks].[Occurrence])=1 Or
[tblTasks].[Occurrence])=2 Or [tblTasks].[Occurrence])=3 Or
[tblTasks].[Occurrence])=4 Or [tblTasks].[Occurrence])=5 Or
[tblTasks].[Occurrence])=6,"*"))

In the above it was the parser that put inthe explicit field reference - I
origianlly only typed in "1 or 2 or..."

I know I can do the following - a drop down list of Occurance types and
restrict the selected type, or try to alter the SQL for the list boxes with
some in-line code. The first option doesn't get me the funtionality I want,
the second seems like I'd be making things too complex. I suppose I could
also limit my optins to "All Tasks" and "Exclude Daily Tasks"...but I'd like
to be able to look at the daily tasks by themselves.

Appreciate any ideas. Thanks!

 

[KARL DEWEY]

Try this --
IIf([fraType]=2,7,IIf([fraType]=3,([tblTasks].[Occurrence])<>7,>0))

 

[Ed Ardzinski]

Thanks for the ideas - neither worked perfectly, but the comments did get my
mind thinking clearer ont he iseue and what I was trying to do.

I resorted to a 2 pronged approach - part query design (I used an IN clause
for the criteria for the field in the 3 SQL statements), and part VBA inline
SQL (I created a sub routine to replace the IN clauses in the before update
of my option frame).

I had to create an integer variable to keep track of the previous selection
of the frame (there's probably a way to do that easier...), but the process
works and wasn't too much of a bearto implement.

Performance stinks, but it only takes a couple seconds to do the 3 requeries
(they are fairly complex SQL statements all in all), and it's not like anyone
will be wanting to see lgihtning repaint!

Thanks again!