Search
Search

# Selection Formula for group

I am trying to pull records from a table for a report that reports members that have not had a visit since a certain date and that have level 1 AND level 2 not equal to what is inputted by user. My formula looks like this:

(DateValue ({TBL_VISIT_HISTORY.VISIT_DATE_IN}) >= DateValue ({@DateFrom}) and (( isnull({TBL_VISIT_HISTORY.LEVEL2_SEL}) or trim({TBL_VISIT_HISTORY.LEVEL2_SEL}) <> trim({@level2})) and (isnull({TBL_VISIT_HISTORY.LEVEL1_SEL}) or trim({TBL_VISIT_HISTORY.LEVEL1_SEL}) <> trim({@level1}))))

The results I get give me records where level 1 is equal but does not take into account the level 2.

An example would be if I had a record for Joe Smith visiting on 8/26/08 with level 1 as Gym and level 2 as Aeorbics Class and I have another record for Jan Doe visiting on 8/26/08 with level 1 as Gym and level 2 as Swim Lesson and I wanted to run a report for anyone who has not visited since 8/26/08 the Gym for Swimming lessons. I would expect to see Joe Smith on the report but not Jan Doe. What I get is neither one shows on the report because of the Gym. If I change the Gym to something else and keep the Aeorbics class, I then get Jan Doe but not Joe Smith. I want both level 1 and level 2 not one or the other.

##### Former Memberreplied

I think you need to use an 'OR' instead of 'AND' as your operator between level 1 and 2.

They would match if Hx.Level1=@Level1 AND Hx.Level2=@Level2, the logical opposite of this is not

Hx.Level1 notequal @Level1 AND Hx.Level2 notequal @Level2 because that requires both to be true.

The opposite is Hx.Level1 notequal @Level1 OR Hx.Level2 notequal @Level2 which returns true if either is true.

ie... Swim notequal Swim AND Gym notequal Gym = false

Swim notequal Aerobics AND Gym notequal Gym = false

Swim notequal Swim OR Gym notequal Gym = false (John Doe would not appear)

Swim notequal Aerobics OR Gym notequal Gym = true (Jane Doe would appear)

Hope that makes sense!!

Here's what I think you need to do....

(

DateValue ({TBL_VISIT_HISTORY.VISIT_DATE_IN}) >= DateValue ({@DateFrom})

and

(

(isnull({TBL_VISIT_HISTORY.LEVEL2_SEL}) or trim({TBL_VISIT_HISTORY.LEVEL2_SEL}) not equal to trim({@level2}))

OR

(isnull({TBL_VISIT_HISTORY.LEVEL1_SEL}) or trim({TBL_VISIT_HISTORY.LEVEL1_SEL}) not equal to trim({@level1}))

)

)

0 View this answer in context
View more on this topic or