Selection Criteria Syntax help

[nickfatool]

I'm going crazy here, struggling with selection syntax.

FName   MName     LName
----------------------------
Fred    James     Flinstone
Ted     Bertie    Beetle
Irene             Payne
Greg    Philpott  Brady
Ron               Birdseye

I want to exclude all records whose middle names = "James" or "Bertie". That should leave me with 3 of the 5 records..

I tried

 not ({query.MName} in ["James", "Bertie"]

"Greg Philpott Brady" was the only record returned, what happened to "Irene Payne" and "Ron Birdseye"?

In Access I could use "OR query.MName is Null" to include those names where "MName" was never initialized.
Any clues would be helpful, if you feel like elaborating, please go ahead.

Oh, and add some personality by including your middle name if you like also.


Cheers,

Robert "MacIntyre" Dexter.

 

[dgillz]

You were on the right track in your thinking. Try this:

not ({query.MName} in ["James", "Bertie"]) or
IsNull({query.Mname})
Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[nickfatool]

Bewdy! Thanks.

 

[Ngolem]

not ({query.MName} in ["James", "Bertie"]) or
IsNull({query.Mname})

should be

(IsNull({query.Mname}) or not ({query.MName} in ["James", "Bertie"]) )

Always test for a null in the first part of a formula or you will have problems Jim

JimBroadbent@Hotmail.com

 

[dgillz]

Good one Jim, I missed it and should have known better. Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com