Parameter with "OR" not working

[ssignore]

Hello MS Online Community.
I have created a very simply query on which one of my key forms is based.
However, the WHERE segment of the SQL is not working. The SQL has been
copied below.
When the prompt 'Please enter Analyst Name' appears after I hit 'Run', I
will enter an Analyst Name, but the query will still return all records
instead of just those of the specified Analyst.
Do I need different wording?
Many thanks for the help!
Simone

SELECT [01_TblInitial].Status, [01_TblInitial].Unit,
[01_TblInitial].[Analyst Name], [01_TblInitial].Task, [01_TblInitial].[As-Is
Process Flow], [01_TblInitial].[As-Is Narrative], [01_TblInitial].[Next
Steps/Comments], [01_TblInitial].[Completion Date]
FROM 01_TblInitial
WHERE ((([01_TblInitial].[Analyst Name])=[Please enter Analyst Name] Or
([01_TblInitial].[Analyst Name]) Is Not Null))
ORDER BY IIf([01_TblInitial]![Status]="In
Progress",1,IIf([01_TblInitial]![Status]="Approved",2,IIf([01_TblInitial]![Status]="Backburner",3,IIf([01_TblInitial]![Status]="Completed",4,IIf([01_TblInitial]![Status]="Not
Started",5,IIf([01_TblInitial]![Status]="On Hold",6,"")))))),
[01_TblInitial].Unit;

 

[Software-Matters]

..Hi, I think the problem is the "OR". Try taking out "Or
([01_TblInitial].[Analyst Name]) Is Not Null" or replacing it with "AND"
--
<a
href="http://www.software-matters.co.uk/bespoke-database-design.html">Bespoke
Access Database Development</a>
<p>Software Matters</br>
Straightforward solutions that work</p>

 

[Tom van Stiphout]

On Thu, 23 Jul 2009 06:17:01 -0700, ssignore

Your query has this WHERE-clause:
WHERE ((([01_TblInitial].[Analyst Name])=[Please enter Analyst Name]
Or ([01_TblInitial].[Analyst Name]) Is Not Null))
which to Access means: give me the records where the analyst is the
one given by the user, or the analystname is not blank.
The result will be all non-blank analystnames. Take the OR part out if
you only want the given analyst.

-Tom.
Microsoft Access MVP

 

[John Spencer]

Probably you want the following criteria to return one specific analyst if you
enter anything in the prompt and all records if you leave the prompt blank.
In that case, the criteria would be:

WHERE ([01_TblInitial].[Analyst Name]=[Please enter Analyst Name]
Or [Please enter Analyst Name] Is Not Null)

*** IF *** your Analyst Name field ALWAYS contains a value (it is never null)
you can use the following criteria - this won't return records where Analyst
Name is null, but it will return all records where Analyst name has a value -
even if the value is a zero-length string.

WHERE [01_TblInitial].[Analyst Name] LIKE Nz([Please enter Analyst Name],"*")

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

On Thu, 23 Jul 2009 06:17:01 -0700, ssignore

Your query has this WHERE-clause:
WHERE ((([01_TblInitial].[Analyst Name])=[Please enter Analyst Name]
Or ([01_TblInitial].[Analyst Name]) Is Not Null))
which to Access means: give me the records where the analyst is the
one given by the user, or the analystname is not blank.
The result will be all non-blank analystnames. Take the OR part out if
you only want the given analyst.

-Tom.
Microsoft Access MVP

Hello MS Online Community.
I have created a very simply query on which one of my key forms is based.
However, the WHERE segment of the SQL is not working. The SQL has been
copied below.
When the prompt 'Please enter Analyst Name' appears after I hit 'Run', I
will enter an Analyst Name, but the query will still return all records
instead of just those of the specified Analyst.
Do I need different wording?
Many thanks for the help!
Simone

SELECT [01_TblInitial].Status, [01_TblInitial].Unit,
[01_TblInitial].[Analyst Name], [01_TblInitial].Task, [01_TblInitial].[As-Is
Process Flow], [01_TblInitial].[As-Is Narrative], [01_TblInitial].[Next
Steps/Comments], [01_TblInitial].[Completion Date]
FROM 01_TblInitial
WHERE ((([01_TblInitial].[Analyst Name])=[Please enter Analyst Name] Or
([01_TblInitial].[Analyst Name]) Is Not Null))
ORDER BY IIf([01_TblInitial]![Status]="In
Progress",1,IIf([01_TblInitial]![Status]="Approved",2,IIf([01_TblInitial]![Status]="Backburner",3,IIf([01_TblInitial]![Status]="Completed",4,IIf([01_TblInitial]![Status]="Not
Started",5,IIf([01_TblInitial]![Status]="On Hold",6,"")))))),
[01_TblInitial].Unit;