Selection Formula for group

[Former Member]

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 Member · Accepted solution]

"level 1 AND level 2 not equal to what is inputted by user"

where is this represented in your statement ?

[Former Member · Accepted solution]

I'm sorry, I didn't realize that the not equal didn't come through. The statement 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}))))

[Former Member · Accepted solution]

It doesn't look like it will post as I have it. Let me try it this way:

(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})) and (isnull({TBL_VISIT_HISTORY.LEVEL1_SEL}) or trim({TBL_VISIT_HISTORY.LEVEL1_SEL}) not equal to trim({@level1}))))

[Former Member · Accepted solution]

Lisa,

I know. I've had mine not show up too...I forgot about that.

Ok

(I think you need to state both sides of this 'or' statement within the same parens...)

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

(( isnull({TBL_VISIT_HISTORY.LEVEL2_SEL})

or

(and I think you need to restate the DateValue line again here...)

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

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

and

(isnull({TBL_VISIT_HISTORY.LEVEL1_SEL}) or

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

...sorry, wish I could test this out, 'cause I'd rebuild it a small chunk at a time

to verify my guesses.

Hope this helps some,

The Panda

[Former Member · Accepted solution]

The Panda,

Okay, I have tried what you have suggested and still not getting the results I am looking for. I have scaled down the formula to test the simpliest expression as:

(DateValue ({TBL_VISIT_HISTORY.VISIT_DATE_IN}) >= DateValue ({@DateFrom}) AND trim({@level1}) not equal to trim({TBL_VISIT_HISTORY.LEVEL1_SEL}) AND trim({@level2}) not equal to trim({TBL_VISIT_HISTORY.LEVEL2_SEL}))

I have two records:

Joe Smith date 9/11/08 level 1 Gym level 2 Becky Smith

Jane Doe date 9/11/08 level 1 Gym level 2 Charles Anderson

If I enter the date of 9/11/08 with level 1 at Gym and level 2 as Becky Smith, neither one of them show up. I would expect Jane Doe to show on the report since level 2 is not the same.

Do you have any ideas of what I am doing wrong?

Thanks in advance!

Lisa

[Former Member · Accepted solution]

You're formula is looking for:

notequal Level 1 AND notequal Level 2

In the example you gave Jane Doe is equal Level 1 and notequal Level 2

[Former Member · Accepted solution]

Okay, maybe I'm not explaining it correctly. With my example above, I would expect Jane Doe to show up on the report since level 1 and level 2 are not equal to what is on her record but she doesn't because level 1 is equal. Let me know if I need to rephrase.

[Former Member · Accepted solution]

Lisa,

I don't see where you are testing Level1 not equal to Level2.

Try this out and see if gets you a little closer:

(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 ({@level2})) 
AND 
(isnull({TBL_VISIT_HISTORY.LEVEL1_SEL}) 
OR 
trim({TBL_VISIT_HISTORY.LEVEL1_SEL}) not equal to trim({@level1})))
AND 
{@level1} not equal to {@level2})

Jason

[Former Member · Accepted solution]

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}))

)

)