How to filter against records...

  • Thread starter Thread starter Mike Whalen
  • Start date Start date
M

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...
 
WHERE NOT([CheckboxField] = True AND ([EmailField] <> "" AND [EmailField] Is
Not Null))
 
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]
 
Put a space between Not and the open parenthesis. Not is not a function
(pardon the pun).

Kelvin

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

Expr1: WHERE Not([INACTIVE]=True And ((e-mail address removed)
Click to expand...
 
Mike -

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

--
Ken Snell
<MS ACCESS MVP>

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

Kelvin

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

Expr1: WHERE Not([INACTIVE]=True And ((e-mail address removed)
Click to expand...
Click to expand...
 
Ken Snell said:
Mike -

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

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 said:
Post the entire SQL statement that is not working.
Click to expand...

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

Mike Whalen said:
"Ken Snell" <[email protected]> wrote in message
Post the entire SQL statement that is not working.
Click to expand...

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]
Click to expand...
 
Back
Top