Criteria to prompt for field and omit certain records

A

ADB_Seeker

I have a query that prompts the user for the install number. I also need it
to omit any records that have "obsolete" in any of the revision fields.
Following is my SQL code.
If I include the Not Like "*obs*" criteria, the install number is not
included in the report.
If I exclude the Not Like "*obs*" criteria, the install number is included
in the report, however all records with a revision of Obsolete also show up.

SELECT [Engineering Drawing Numbers].MasterProject, [Engineering Drawing
Numbers].[INSTALL NUMBER], [Engineering Drawing Numbers].[INSTALL REV],
[Engineering Drawing Numbers].[INSTALL DESCRIPTION], [Engineering Drawing
Numbers].[SUB-ASSEMBLY 1], [Engineering Drawing Numbers].[SUB-ASSEMBLY 1
REV], [Engineering Drawing Numbers].[SUB-ASSEMBLY 1 DESCRIPTION],
[Engineering Drawing Numbers].[SUB ASSEMBLY 2], [Engineering Drawing
Numbers].[SUB-ASSEMBLY 2 REV], [Engineering Drawing Numbers].[SUB-ASSEMBLY 2
DESCRIPTION], [Engineering Drawing Numbers].[DETAIL NUMBER], [Engineering
Drawing Numbers].[DETAIL REV], [Engineering Drawing Numbers].[DETAIL
DESCRIPTION], [Engineering Drawing Numbers].[DETAIL CONFIG], [Engineering
Drawing Numbers].CAT_CODE, [Engineering Drawing Numbers].VENDOR_CODE
FROM [Engineering Drawing Numbers]
WHERE ((([Engineering Drawing Numbers].[INSTALL NUMBER])=[Enter Install
Number])) OR ((([Engineering Drawing Numbers].[INSTALL REV]) Not Like
"*obs*")) OR ((([Engineering Drawing Numbers].[SUB-ASSEMBLY 1 REV]) Not Like
"*obs*")) OR ((([Engineering Drawing Numbers].[SUB-ASSEMBLY 2 REV]) Not Like
"*obs*")) OR ((([Engineering Drawing Numbers].[DETAIL REV]) Not Like
"*obs*"));

Thank you in advance for your help.
Linda
 
C

Chris

You need AND instead of OR in your where clause.

You want all rows where install number equals the prompt. And you want
every one of the revision columns not like "OBS", right? If any revision is
like OBS, you don't want that row, even if the install number matches.
 
A

ADB_Seeker

Hi, Chris
I changed OR to AND. Now when I run the report, I get no information.
Instead, my message box "No Data Found" appears.

Chris said:
You need AND instead of OR in your where clause.

You want all rows where install number equals the prompt. And you want
every one of the revision columns not like "OBS", right? If any revision is
like OBS, you don't want that row, even if the install number matches.



ADB_Seeker said:
I have a query that prompts the user for the install number. I also need it
to omit any records that have "obsolete" in any of the revision fields.
Following is my SQL code.
If I include the Not Like "*obs*" criteria, the install number is not
included in the report.
If I exclude the Not Like "*obs*" criteria, the install number is included
in the report, however all records with a revision of Obsolete also show up.

SELECT [Engineering Drawing Numbers].MasterProject, [Engineering Drawing
Numbers].[INSTALL NUMBER], [Engineering Drawing Numbers].[INSTALL REV],
[Engineering Drawing Numbers].[INSTALL DESCRIPTION], [Engineering Drawing
Numbers].[SUB-ASSEMBLY 1], [Engineering Drawing Numbers].[SUB-ASSEMBLY 1
REV], [Engineering Drawing Numbers].[SUB-ASSEMBLY 1 DESCRIPTION],
[Engineering Drawing Numbers].[SUB ASSEMBLY 2], [Engineering Drawing
Numbers].[SUB-ASSEMBLY 2 REV], [Engineering Drawing Numbers].[SUB-ASSEMBLY 2
DESCRIPTION], [Engineering Drawing Numbers].[DETAIL NUMBER], [Engineering
Drawing Numbers].[DETAIL REV], [Engineering Drawing Numbers].[DETAIL
DESCRIPTION], [Engineering Drawing Numbers].[DETAIL CONFIG], [Engineering
Drawing Numbers].CAT_CODE, [Engineering Drawing Numbers].VENDOR_CODE
FROM [Engineering Drawing Numbers]
WHERE ((([Engineering Drawing Numbers].[INSTALL NUMBER])=[Enter Install
Number])) OR ((([Engineering Drawing Numbers].[INSTALL REV]) Not Like
"*obs*")) OR ((([Engineering Drawing Numbers].[SUB-ASSEMBLY 1 REV]) Not Like
"*obs*")) OR ((([Engineering Drawing Numbers].[SUB-ASSEMBLY 2 REV]) Not Like
"*obs*")) OR ((([Engineering Drawing Numbers].[DETAIL REV]) Not Like
"*obs*"));

Thank you in advance for your help.
Linda
Click to expand...
Click to expand...
 
T

Tom van Stiphout

On Thu, 14 Jan 2010 05:36:01 -0800, ADB_Seeker

It'a a bit hard to read with all the parentheses in the WHERE clause.
I believe that you always want the selected installnumber, and then
omit possible values in other fields. The where clause should be
structured like this:
(I'm using shorthand)
....where f1 = A
and f2 not like 'B'
and f3 not like 'C'
and f4 not like 'D'

I don't see an AND in your where-clause.

-Tom.
Microsoft Access MVP
 
A

ADB_Seeker

Tom,
SQL puts in all the parentheses. I changed the WHERE to the following and it
still brings up no data.

WHERE [Engineering Drawing Numbers].[INSTALL NUMBER]=[Enter Install Number]
AND [Engineering Drawing Numbers].[INSTALL REV] Not Like "*obs*" AND
[Engineering Drawing Numbers].[SUB-ASSEMBLY 1 REV] Not Like "*obs*" AND
[Engineering Drawing Numbers].[SUB-ASSEMBLY 2 REV] Not Like "*obs*" AND
[Engineering Drawing Numbers].[DETAIL REV] Not Like "*obs*";

Tom van Stiphout said:
On Thu, 14 Jan 2010 05:36:01 -0800, ADB_Seeker

It'a a bit hard to read with all the parentheses in the WHERE clause.
I believe that you always want the selected installnumber, and then
omit possible values in other fields. The where clause should be
structured like this:
(I'm using shorthand)
....where f1 = A
and f2 not like 'B'
and f3 not like 'C'
and f4 not like 'D'

I don't see an AND in your where-clause.

-Tom.
Microsoft Access MVP

I have a query that prompts the user for the install number. I also need it
to omit any records that have "obsolete" in any of the revision fields.
Following is my SQL code.
If I include the Not Like "*obs*" criteria, the install number is not
included in the report.
If I exclude the Not Like "*obs*" criteria, the install number is included
in the report, however all records with a revision of Obsolete also show up.

SELECT [Engineering Drawing Numbers].MasterProject, [Engineering Drawing
Numbers].[INSTALL NUMBER], [Engineering Drawing Numbers].[INSTALL REV],
[Engineering Drawing Numbers].[INSTALL DESCRIPTION], [Engineering Drawing
Numbers].[SUB-ASSEMBLY 1], [Engineering Drawing Numbers].[SUB-ASSEMBLY 1
REV], [Engineering Drawing Numbers].[SUB-ASSEMBLY 1 DESCRIPTION],
[Engineering Drawing Numbers].[SUB ASSEMBLY 2], [Engineering Drawing
Numbers].[SUB-ASSEMBLY 2 REV], [Engineering Drawing Numbers].[SUB-ASSEMBLY 2
DESCRIPTION], [Engineering Drawing Numbers].[DETAIL NUMBER], [Engineering
Drawing Numbers].[DETAIL REV], [Engineering Drawing Numbers].[DETAIL
DESCRIPTION], [Engineering Drawing Numbers].[DETAIL CONFIG], [Engineering
Drawing Numbers].CAT_CODE, [Engineering Drawing Numbers].VENDOR_CODE
FROM [Engineering Drawing Numbers]
WHERE ((([Engineering Drawing Numbers].[INSTALL NUMBER])=[Enter Install
Number])) OR ((([Engineering Drawing Numbers].[INSTALL REV]) Not Like
"*obs*")) OR ((([Engineering Drawing Numbers].[SUB-ASSEMBLY 1 REV]) Not Like
"*obs*")) OR ((([Engineering Drawing Numbers].[SUB-ASSEMBLY 2 REV]) Not Like
"*obs*")) OR ((([Engineering Drawing Numbers].[DETAIL REV]) Not Like
"*obs*"));

Thank you in advance for your help.
Linda
Click to expand...
.
Click to expand...
 
C

Chris

Create a simple query, just pulling in the install number and all the
revision columns, and enter an install number. Do you get any rows returned?

ADB_Seeker said:
Hi, Chris
I changed OR to AND. Now when I run the report, I get no information.
Instead, my message box "No Data Found" appears.

Chris said:
You need AND instead of OR in your where clause.

You want all rows where install number equals the prompt. And you want
every one of the revision columns not like "OBS", right? If any revision is
like OBS, you don't want that row, even if the install number matches.



ADB_Seeker said:
I have a query that prompts the user for the install number. I also need it
to omit any records that have "obsolete" in any of the revision fields.
Following is my SQL code.
If I include the Not Like "*obs*" criteria, the install number is not
included in the report.
If I exclude the Not Like "*obs*" criteria, the install number is included
in the report, however all records with a revision of Obsolete also show up.

SELECT [Engineering Drawing Numbers].MasterProject, [Engineering Drawing
Numbers].[INSTALL NUMBER], [Engineering Drawing Numbers].[INSTALL REV],
[Engineering Drawing Numbers].[INSTALL DESCRIPTION], [Engineering Drawing
Numbers].[SUB-ASSEMBLY 1], [Engineering Drawing Numbers].[SUB-ASSEMBLY 1
REV], [Engineering Drawing Numbers].[SUB-ASSEMBLY 1 DESCRIPTION],
[Engineering Drawing Numbers].[SUB ASSEMBLY 2], [Engineering Drawing
Numbers].[SUB-ASSEMBLY 2 REV], [Engineering Drawing Numbers].[SUB-ASSEMBLY 2
DESCRIPTION], [Engineering Drawing Numbers].[DETAIL NUMBER], [Engineering
Drawing Numbers].[DETAIL REV], [Engineering Drawing Numbers].[DETAIL
DESCRIPTION], [Engineering Drawing Numbers].[DETAIL CONFIG], [Engineering
Drawing Numbers].CAT_CODE, [Engineering Drawing Numbers].VENDOR_CODE
FROM [Engineering Drawing Numbers]
WHERE ((([Engineering Drawing Numbers].[INSTALL NUMBER])=[Enter Install
Number])) OR ((([Engineering Drawing Numbers].[INSTALL REV]) Not Like
"*obs*")) OR ((([Engineering Drawing Numbers].[SUB-ASSEMBLY 1 REV]) Not Like
"*obs*")) OR ((([Engineering Drawing Numbers].[SUB-ASSEMBLY 2 REV]) Not Like
"*obs*")) OR ((([Engineering Drawing Numbers].[DETAIL REV]) Not Like
"*obs*"));

Thank you in advance for your help.
Linda
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Is it possible that the Rev fields are might be NULL (Look Totally blank). If
so, try

WHERE [Engineering Drawing Numbers].[INSTALL NUMBER]=[Enter Install Number]
AND [Engineering Drawing Numbers].[INSTALL REV] & "" Not Like "*obs*" AND
[Engineering Drawing Numbers].[SUB-ASSEMBLY 1 REV] & "" Not Like "*obs*" AND
[Engineering Drawing Numbers].[SUB-ASSEMBLY 2 REV] & "" Not Like "*obs*" AND
[Engineering Drawing Numbers].[DETAIL REV] & "" Not Like "*obs*";

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

ADB_Seeker said:
Tom,
SQL puts in all the parentheses. I changed the WHERE to the following and it
still brings up no data.

WHERE [Engineering Drawing Numbers].[INSTALL NUMBER]=[Enter Install Number]
AND [Engineering Drawing Numbers].[INSTALL REV] Not Like "*obs*" AND
[Engineering Drawing Numbers].[SUB-ASSEMBLY 1 REV] Not Like "*obs*" AND
[Engineering Drawing Numbers].[SUB-ASSEMBLY 2 REV] Not Like "*obs*" AND
[Engineering Drawing Numbers].[DETAIL REV] Not Like "*obs*";

Tom van Stiphout said:
On Thu, 14 Jan 2010 05:36:01 -0800, ADB_Seeker

It'a a bit hard to read with all the parentheses in the WHERE clause.
I believe that you always want the selected installnumber, and then
omit possible values in other fields. The where clause should be
structured like this:
(I'm using shorthand)
....where f1 = A
and f2 not like 'B'
and f3 not like 'C'
and f4 not like 'D'

I don't see an AND in your where-clause.

-Tom.
Microsoft Access MVP

I have a query that prompts the user for the install number. I also need it
to omit any records that have "obsolete" in any of the revision fields.
Following is my SQL code.
If I include the Not Like "*obs*" criteria, the install number is not
included in the report.
If I exclude the Not Like "*obs*" criteria, the install number is included
in the report, however all records with a revision of Obsolete also show up.

SELECT [Engineering Drawing Numbers].MasterProject, [Engineering Drawing
Numbers].[INSTALL NUMBER], [Engineering Drawing Numbers].[INSTALL REV],
[Engineering Drawing Numbers].[INSTALL DESCRIPTION], [Engineering Drawing
Numbers].[SUB-ASSEMBLY 1], [Engineering Drawing Numbers].[SUB-ASSEMBLY 1
REV], [Engineering Drawing Numbers].[SUB-ASSEMBLY 1 DESCRIPTION],
[Engineering Drawing Numbers].[SUB ASSEMBLY 2], [Engineering Drawing
Numbers].[SUB-ASSEMBLY 2 REV], [Engineering Drawing Numbers].[SUB-ASSEMBLY 2
DESCRIPTION], [Engineering Drawing Numbers].[DETAIL NUMBER], [Engineering
Drawing Numbers].[DETAIL REV], [Engineering Drawing Numbers].[DETAIL
DESCRIPTION], [Engineering Drawing Numbers].[DETAIL CONFIG], [Engineering
Drawing Numbers].CAT_CODE, [Engineering Drawing Numbers].VENDOR_CODE
FROM [Engineering Drawing Numbers]
WHERE ((([Engineering Drawing Numbers].[INSTALL NUMBER])=[Enter Install
Number])) OR ((([Engineering Drawing Numbers].[INSTALL REV]) Not Like
"*obs*")) OR ((([Engineering Drawing Numbers].[SUB-ASSEMBLY 1 REV]) Not Like
"*obs*")) OR ((([Engineering Drawing Numbers].[SUB-ASSEMBLY 2 REV]) Not Like
"*obs*")) OR ((([Engineering Drawing Numbers].[DETAIL REV]) Not Like
"*obs*"));

Thank you in advance for your help.
Linda
Click to expand...
.
Click to expand...
Click to expand...
 
A

ADB_Seeker

That was it! Thanks. It works perfectly now.

John Spencer said:
Is it possible that the Rev fields are might be NULL (Look Totally blank). If
so, try

WHERE [Engineering Drawing Numbers].[INSTALL NUMBER]=[Enter Install Number]
AND [Engineering Drawing Numbers].[INSTALL REV] & "" Not Like "*obs*" AND
[Engineering Drawing Numbers].[SUB-ASSEMBLY 1 REV] & "" Not Like "*obs*" AND
[Engineering Drawing Numbers].[SUB-ASSEMBLY 2 REV] & "" Not Like "*obs*" AND
[Engineering Drawing Numbers].[DETAIL REV] & "" Not Like "*obs*";

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

ADB_Seeker said:
Tom,
SQL puts in all the parentheses. I changed the WHERE to the following and it
still brings up no data.

WHERE [Engineering Drawing Numbers].[INSTALL NUMBER]=[Enter Install Number]
AND [Engineering Drawing Numbers].[INSTALL REV] Not Like "*obs*" AND
[Engineering Drawing Numbers].[SUB-ASSEMBLY 1 REV] Not Like "*obs*" AND
[Engineering Drawing Numbers].[SUB-ASSEMBLY 2 REV] Not Like "*obs*" AND
[Engineering Drawing Numbers].[DETAIL REV] Not Like "*obs*";

Tom van Stiphout said:
On Thu, 14 Jan 2010 05:36:01 -0800, ADB_Seeker

It'a a bit hard to read with all the parentheses in the WHERE clause.
I believe that you always want the selected installnumber, and then
omit possible values in other fields. The where clause should be
structured like this:
(I'm using shorthand)
....where f1 = A
and f2 not like 'B'
and f3 not like 'C'
and f4 not like 'D'

I don't see an AND in your where-clause.

-Tom.
Microsoft Access MVP


I have a query that prompts the user for the install number. I also need it
to omit any records that have "obsolete" in any of the revision fields.
Following is my SQL code.
If I include the Not Like "*obs*" criteria, the install number is not
included in the report.
If I exclude the Not Like "*obs*" criteria, the install number is included
in the report, however all records with a revision of Obsolete also show up.

SELECT [Engineering Drawing Numbers].MasterProject, [Engineering Drawing
Numbers].[INSTALL NUMBER], [Engineering Drawing Numbers].[INSTALL REV],
[Engineering Drawing Numbers].[INSTALL DESCRIPTION], [Engineering Drawing
Numbers].[SUB-ASSEMBLY 1], [Engineering Drawing Numbers].[SUB-ASSEMBLY 1
REV], [Engineering Drawing Numbers].[SUB-ASSEMBLY 1 DESCRIPTION],
[Engineering Drawing Numbers].[SUB ASSEMBLY 2], [Engineering Drawing
Numbers].[SUB-ASSEMBLY 2 REV], [Engineering Drawing Numbers].[SUB-ASSEMBLY 2
DESCRIPTION], [Engineering Drawing Numbers].[DETAIL NUMBER], [Engineering
Drawing Numbers].[DETAIL REV], [Engineering Drawing Numbers].[DETAIL
DESCRIPTION], [Engineering Drawing Numbers].[DETAIL CONFIG], [Engineering
Drawing Numbers].CAT_CODE, [Engineering Drawing Numbers].VENDOR_CODE
FROM [Engineering Drawing Numbers]
WHERE ((([Engineering Drawing Numbers].[INSTALL NUMBER])=[Enter Install
Number])) OR ((([Engineering Drawing Numbers].[INSTALL REV]) Not Like
"*obs*")) OR ((([Engineering Drawing Numbers].[SUB-ASSEMBLY 1 REV]) Not Like
"*obs*")) OR ((([Engineering Drawing Numbers].[SUB-ASSEMBLY 2 REV]) Not Like
"*obs*")) OR ((([Engineering Drawing Numbers].[DETAIL REV]) Not Like
"*obs*"));

Thank you in advance for your help.
Linda
.
Click to expand...
Click to expand...
.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Unique ID's for Document Control 3
Concatenating the same field from multiple records 16
Queiry to return highest value or number in a field 6
Need to Change An Output Report... 3
Convert text to number automatically 1
Count Matching records 2
To preserve records. 2
Using an Access form to Mass Update a table 7

Top