Using expressions for Query criteria

  • Thread starter Thread starter NoviceAccessUser-Melanie
  • Start date Start date
N

NoviceAccessUser-Melanie

I don't use Access often, so I know the issue I am struggling with is due to
my inexperience. I have several queries based on 3 tables. For the 1st
criteria, I use the selection from a form. I recently added a 2nd criteria
which is "Not "Terminated" or "Is Null". My expected results are all rows
that do not have the Status code Terminated and also include rows that do not
have as assigned status (nulls). With the 1st criteria, it works like a
charm. but when I added the 2nd criteria, it loses the 1st criteria and just
gives me a random rows with different values from the 2nd criteria.

Here are the expressions I am using:
Criteria 1: Like [Forms]![frmSelectFund]![txtFund]

Criteria 2: <>"Terminated"
Is Null
Please let me know what I'm missing. Your help is much appreciated.
 
What Access does is each line in the Query By Example grid (the Query window)
is a different question, so your actual query is ending up with:

Give me the rows where ("Like [Forms]![frmSelectFund]![txtFund]" AND <>
"Terminated) OR (is Null)

To get the query to be:

Where ("Like [Forms]![frmSelectFund]![txtFund]" AND (<> "Terminated OR is
Null)

You need to have the second field test both <>"Terminated" and Is Null on
one line. So your second criteria would read:

<>"Terminated" OR Is Null
 
Yes! That did it. Thanks so much.

BobT said:
What Access does is each line in the Query By Example grid (the Query window)
is a different question, so your actual query is ending up with:

Give me the rows where ("Like [Forms]![frmSelectFund]![txtFund]" AND <>
"Terminated) OR (is Null)

To get the query to be:

Where ("Like [Forms]![frmSelectFund]![txtFund]" AND (<> "Terminated OR is
Null)

You need to have the second field test both <>"Terminated" and Is Null on
one line. So your second criteria would read:

<>"Terminated" OR Is Null



NoviceAccessUser-Melanie said:
I don't use Access often, so I know the issue I am struggling with is due to
my inexperience. I have several queries based on 3 tables. For the 1st
criteria, I use the selection from a form. I recently added a 2nd criteria
which is "Not "Terminated" or "Is Null". My expected results are all rows
that do not have the Status code Terminated and also include rows that do not
have as assigned status (nulls). With the 1st criteria, it works like a
charm. but when I added the 2nd criteria, it loses the 1st criteria and just
gives me a random rows with different values from the 2nd criteria.

Here are the expressions I am using:
Criteria 1: Like [Forms]![frmSelectFund]![txtFund]

Criteria 2: <>"Terminated"
Is Null
Please let me know what I'm missing. Your help is much appreciated.
Click to expand...
Click to expand...
 
Back
Top