How to filter against records...

[Mike Whalen]

Hi,

I am trying to help out someone who wants to create a custom query.

Right now, he has two querys against a 2000+ database of contacts. The
first query shows everything.

The second query shows everything except those contacts that are
labelled "Inactive." The Inactive field is a check box. If the field
is checked, those entries do not show up in the second queries'
results.

I am trying to create a third query that does NOT show a record:

1. If the inactive box is checked AND
2. The e-mail field contains ANYTHING

(The E-mail field is simply a text field.)

Can anyone explain to me how to do this query?

Cheers,

Mike...

 

[Ken Snell]

WHERE NOT([CheckboxField] = True AND ([EmailField] <> "" AND [EmailField] Is
Not Null))

 

[Mike Whalen]

This is what I have in the Field section right now:

Expr1: WHERE Not([INACTIVE]=True And (<>"" And [EMAIL] Is Not Null))

This produces a syntax error and then highlights "Not("

Am I putting this in the right place?

Cheers,

Mike Whalen



[QUOTE="Ken Snell"]
WHERE NOT([CheckboxField] = True AND ([EmailField] <> "" AND [EmailField] Is
Not Null))


--
Ken Snell
<MS ACCESS MVP>

[QUOTE="Mike Whalen"]
Hi,

I am trying to help out someone who wants to create a custom query.

Right now, he has two querys against a 2000+ database of contacts. The
first query shows everything.

The second query shows everything except those contacts that are
labelled "Inactive." The Inactive field is a check box. If the field
is checked, those entries do not show up in the second queries'
results.

I am trying to create a third query that does NOT show a record:

1. If the inactive box is checked AND
2. The e-mail field contains ANYTHING

(The E-mail field is simply a text field.)

Can anyone explain to me how to do this query?

Cheers,

Mike...[/QUOTE][/QUOTE]

 

[Kelvin]

Put a space between Not and the open parenthesis. Not is not a function
(pardon the pun).

Kelvin

This is what I have in the Field section right now:

Expr1: WHERE Not([INACTIVE]=True And ((e-mail address removed)

 

[Ken Snell]

Mike -

Kelvin is correct. That was a typo in my post. Sorry!

--
Ken Snell
<MS ACCESS MVP>

Put a space between Not and the open parenthesis. Not is not a function
(pardon the pun).

Kelvin

This is what I have in the Field section right now:

Expr1: WHERE Not([INACTIVE]=True And ((e-mail address removed)

 

[Mike Whalen]

Mike -

Kelvin is correct. That was a typo in my post. Sorry!

It's no problem, but I am certain I tried it with a space. At that
point the "NOT (" item is highlighted after a notification of a syntax
error.

I'm missing something simple...

m

 

[Ken Snell]

Post the entire SQL statement that is not working.

 

[Mike Whalen]

Post the entire SQL statement that is not working.

I suppose I don't understand what you want.

This is all I have:

Expr1: WHERE Not([INACTIVE]=True And (<>"" And [EMAIL] Is Not Null))

or

Expr1: WHERE Not ([INACTIVE]=True And ([EMAIL]<>"" And [EMAIL] Is Not Null))

What else do you need?

m

 

[Ken Snell]

From what you've posted, I think I see the problem. But, to be sure, do
this:

Open your query in design view. In the top left corner of the toolbar is an
icon for switching to other views. Pull down the arrow and select SQL. The
window will change to show a long statement. Copy the entire statement and
paste it into a message. That will let me see exactly what you've set up.

I believe the problem is that you've put the criterion statements into
calculated fields.

--
Ken Snell
<MS ACCESS MVP>

"Ken Snell" <kthissnellis9@notcomcast.realnet> wrote in message

Post the entire SQL statement that is not working.

I suppose I don't understand what you want.

This is all I have:

Expr1: WHERE Not([INACTIVE]=True And (<>"" And [EMAIL] Is Not Null))

or

Expr1: WHERE Not ([INACTIVE]=True And ([EMAIL]<>"" And [EMAIL] Is Not Null))

What else do you need?

m[/QUOTE]