Query Field "Criteria"

  • Thread starter Thread starter Clyde Thorne
  • Start date Start date
C

Clyde Thorne

I have a database of building permits that I maintain. When it comes time
to pull out the year 2003 permits, making a Query, I have one column that
has the housing code criteria and another column that has the year 2003 for
criteria. Yet, when I run the Query, all of the years show up. Any ideas
how to prevent this?
 
All Kevin wanted to see was the SQL for your query.

Open the query in Design mode, then select Design View from the View menu.
That will show the SQL associated with your query. Highlight it all, copy it
to the clipboard (Edit | Copy, or Ctrl+C), then paste it your followup post.

One thought, though: did you put the housing code criteria and 2003 both on
the same criteria line, or are they on separate lines? Putting them on
separate lines means one or the other: putting them on the same line means
one and the other.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Clyde Thorne said:
I tried posting the database but it's too large. What should I try next?
clyde
Click to expand...
 
I apologize. I'm not a veteran using Access 2002. This should be what you
requested. I have the criteria on different columns. One column ask for
permits with the code SF, and the other column ask for the year 2003. I'm
getting all of the "SF's" but I'm also getting it for all of the years
instead of only 2003.

clyde

SELECT none_PARCEL.ParcelNumber, none_PARCEL.PropName,
none_PARCEL.PropAddress1, none_PARCEL.PropAddress2,
none_PARCEL.PropAddress3, none_PARCEL.PropNaCity, none_PARCEL.PropNaState,
none_PARCEL.PropNaZipCode, none_LEGAL.LegalDesc1, none_LEGAL.LegalDesc2,
none_LEGAL.LegalDesc3, none_PARCEL.PropStreetNumber,
none_PARCEL.PropStreetName, none_PARCEL.PropStreetMd,
none_PARCEL.PropStreetDr, none_PERMIT.R18PermitType,
none_PERMIT.R18IssueDateYy INTO [Two Year]
FROM (none_PARCEL INNER JOIN none_LEGAL ON none_PARCEL.ParcelNumber =
none_LEGAL.ParcelNumber) INNER JOIN none_PERMIT ON none_PARCEL.ParcelNumber
= none_PERMIT.ParcelNumber
WHERE (((none_PERMIT.R18PermitType)="TH") AND
((none_PERMIT.R18IssueDateYy)=2003)) OR (((none_PERMIT.R18PermitType)="CN"))
OR (((none_PERMIT.R18PermitType)="SF"))
ORDER BY none_PERMIT.R18IssueDateYy DESC;
 
Okay, judging from your query, you've got 3 lines of criteria: one with TH
under R18PermitType, one with CN under R18PermitType and one with SF under
R18PermitType, but I'm guessing you've only got 2003 once under
R18IssueDateYy (on the same line as TH under R18PermitType). Put 2003 under
R18IssueDateYy on each of the other criteria row.

Alternatively, go back into the SQL view, and change your SQL from

WHERE (((none_PERMIT.R18PermitType)="TH") AND
((none_PERMIT.R18IssueDateYy)=2003)) OR (((none_PERMIT.R18PermitType)="CN"))
OR (((none_PERMIT.R18PermitType)="SF"))
ORDER BY none_PERMIT.R18IssueDateYy DESC

to

WHERE (((none_PERMIT.R18PermitType)="TH") AND
((none_PERMIT.R18IssueDateYy)=2003)) OR (((none_PERMIT.R18PermitType)="CN")
AND
((none_PERMIT.R18IssueDateYy)=2003))
OR (((none_PERMIT.R18PermitType)="SF") AND
((none_PERMIT.R18IssueDateYy)=2003))
ORDER BY none_PERMIT.R18IssueDateYy DESC

or

WHERE none_PERMIT.R18PermitType In ("TH", "CN", "SF") AND
none_PERMIT.R18IssueDateYy=2003
ORDER BY none_PERMIT.R18IssueDateYy DESC



--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Clyde Thorne said:
I apologize. I'm not a veteran using Access 2002. This should be what you
requested. I have the criteria on different columns. One column ask for
permits with the code SF, and the other column ask for the year 2003. I'm
getting all of the "SF's" but I'm also getting it for all of the years
instead of only 2003.

clyde

SELECT none_PARCEL.ParcelNumber, none_PARCEL.PropName,
none_PARCEL.PropAddress1, none_PARCEL.PropAddress2,
none_PARCEL.PropAddress3, none_PARCEL.PropNaCity, none_PARCEL.PropNaState,
none_PARCEL.PropNaZipCode, none_LEGAL.LegalDesc1, none_LEGAL.LegalDesc2,
none_LEGAL.LegalDesc3, none_PARCEL.PropStreetNumber,
none_PARCEL.PropStreetName, none_PARCEL.PropStreetMd,
none_PARCEL.PropStreetDr, none_PERMIT.R18PermitType,
none_PERMIT.R18IssueDateYy INTO [Two Year]
FROM (none_PARCEL INNER JOIN none_LEGAL ON none_PARCEL.ParcelNumber =
none_LEGAL.ParcelNumber) INNER JOIN none_PERMIT ON none_PARCEL.ParcelNumber
= none_PERMIT.ParcelNumber
WHERE (((none_PERMIT.R18PermitType)="TH") AND
((none_PERMIT.R18IssueDateYy)=2003)) OR (((none_PERMIT.R18PermitType)="CN"))
OR (((none_PERMIT.R18PermitType)="SF"))
ORDER BY none_PERMIT.R18IssueDateYy DESC;

Douglas J. Steele said:
All Kevin wanted to see was the SQL for your query.

Open the query in Design mode, then select Design View from the View menu.
That will show the SQL associated with your query. Highlight it all,
Click to expand...
copy
it
to the clipboard (Edit | Copy, or Ctrl+C), then paste it your followup post.

One thought, though: did you put the housing code criteria and 2003 both on
the same criteria line, or are they on separate lines? Putting them on
separate lines means one or the other: putting them on the same line means
one and the other.
Click to expand...
Click to expand...
 
THANK YOU VERY MUCH. I'll definitely be keeping this in my files for a long
time. Thanks again. clyde


Douglas J. Steele said:
Okay, judging from your query, you've got 3 lines of criteria: one with TH
under R18PermitType, one with CN under R18PermitType and one with SF under
R18PermitType, but I'm guessing you've only got 2003 once under
R18IssueDateYy (on the same line as TH under R18PermitType). Put 2003 under
R18IssueDateYy on each of the other criteria row.

Alternatively, go back into the SQL view, and change your SQL from

WHERE (((none_PERMIT.R18PermitType)="TH") AND
((none_PERMIT.R18IssueDateYy)=2003)) OR (((none_PERMIT.R18PermitType)="CN"))
OR (((none_PERMIT.R18PermitType)="SF"))
ORDER BY none_PERMIT.R18IssueDateYy DESC

to

WHERE (((none_PERMIT.R18PermitType)="TH") AND
((none_PERMIT.R18IssueDateYy)=2003)) OR (((none_PERMIT.R18PermitType)="CN")
AND
((none_PERMIT.R18IssueDateYy)=2003))
OR (((none_PERMIT.R18PermitType)="SF") AND
((none_PERMIT.R18IssueDateYy)=2003))
ORDER BY none_PERMIT.R18IssueDateYy DESC

or

WHERE none_PERMIT.R18PermitType In ("TH", "CN", "SF") AND
none_PERMIT.R18IssueDateYy=2003
ORDER BY none_PERMIT.R18IssueDateYy DESC



--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Clyde Thorne said:
I apologize. I'm not a veteran using Access 2002. This should be what you
requested. I have the criteria on different columns. One column ask for
permits with the code SF, and the other column ask for the year 2003. I'm
getting all of the "SF's" but I'm also getting it for all of the years
instead of only 2003.

clyde

SELECT none_PARCEL.ParcelNumber, none_PARCEL.PropName,
none_PARCEL.PropAddress1, none_PARCEL.PropAddress2,
none_PARCEL.PropAddress3, none_PARCEL.PropNaCity, none_PARCEL.PropNaState,
none_PARCEL.PropNaZipCode, none_LEGAL.LegalDesc1, none_LEGAL.LegalDesc2,
none_LEGAL.LegalDesc3, none_PARCEL.PropStreetNumber,
none_PARCEL.PropStreetName, none_PARCEL.PropStreetMd,
none_PARCEL.PropStreetDr, none_PERMIT.R18PermitType,
none_PERMIT.R18IssueDateYy INTO [Two Year]
FROM (none_PARCEL INNER JOIN none_LEGAL ON none_PARCEL.ParcelNumber =
none_LEGAL.ParcelNumber) INNER JOIN none_PERMIT ON none_PARCEL.ParcelNumber
= none_PERMIT.ParcelNumber
WHERE (((none_PERMIT.R18PermitType)="TH") AND
((none_PERMIT.R18IssueDateYy)=2003)) OR (((none_PERMIT.R18PermitType)="CN"))
OR (((none_PERMIT.R18PermitType)="SF"))
ORDER BY none_PERMIT.R18IssueDateYy DESC;

Douglas J. Steele said:
All Kevin wanted to see was the SQL for your query.

Open the query in Design mode, then select Design View from the View menu.
That will show the SQL associated with your query. Highlight it all,
Click to expand...
copy
it
to the clipboard (Edit | Copy, or Ctrl+C), then paste it your followup post.

One thought, though: did you put the housing code criteria and 2003
Click to expand...
both
on
the same criteria line, or are they on separate lines? Putting them on
separate lines means one or the other: putting them on the same line means
one and the other.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



I tried posting the database but it's too large. What should I try next?
clyde


Can you post the SQL?

--
Kevin3NF

Sick of all that junk filling up your mailbox?
http://spamarrest.com/affl?2967001



I have a database of building permits that I maintain. When it comes
time
to pull out the year 2003 permits, making a Query, I have one column
that
has the housing code criteria and another column that has the year
2003
for
criteria. Yet, when I run the Query, all of the years show up. Any
ideas
how to prevent this?
Click to expand...
Click to expand...
Click to expand...
 
Thanks for picking me up there Mr. Steele...this is first chance I've had to
get back to this post...:-)

--
Kevin3NF

Sick of all that junk filling up your mailbox?
http://spamarrest.com/affl?2967001



Douglas J. Steele said:
Okay, judging from your query, you've got 3 lines of criteria: one with TH
under R18PermitType, one with CN under R18PermitType and one with SF under
R18PermitType, but I'm guessing you've only got 2003 once under
R18IssueDateYy (on the same line as TH under R18PermitType). Put 2003 under
R18IssueDateYy on each of the other criteria row.

Alternatively, go back into the SQL view, and change your SQL from

WHERE (((none_PERMIT.R18PermitType)="TH") AND
((none_PERMIT.R18IssueDateYy)=2003)) OR (((none_PERMIT.R18PermitType)="CN"))
OR (((none_PERMIT.R18PermitType)="SF"))
ORDER BY none_PERMIT.R18IssueDateYy DESC

to

WHERE (((none_PERMIT.R18PermitType)="TH") AND
((none_PERMIT.R18IssueDateYy)=2003)) OR (((none_PERMIT.R18PermitType)="CN")
AND
((none_PERMIT.R18IssueDateYy)=2003))
OR (((none_PERMIT.R18PermitType)="SF") AND
((none_PERMIT.R18IssueDateYy)=2003))
ORDER BY none_PERMIT.R18IssueDateYy DESC

or

WHERE none_PERMIT.R18PermitType In ("TH", "CN", "SF") AND
none_PERMIT.R18IssueDateYy=2003
ORDER BY none_PERMIT.R18IssueDateYy DESC



--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Clyde Thorne said:
I apologize. I'm not a veteran using Access 2002. This should be what you
requested. I have the criteria on different columns. One column ask for
permits with the code SF, and the other column ask for the year 2003. I'm
getting all of the "SF's" but I'm also getting it for all of the years
instead of only 2003.

clyde

SELECT none_PARCEL.ParcelNumber, none_PARCEL.PropName,
none_PARCEL.PropAddress1, none_PARCEL.PropAddress2,
none_PARCEL.PropAddress3, none_PARCEL.PropNaCity, none_PARCEL.PropNaState,
none_PARCEL.PropNaZipCode, none_LEGAL.LegalDesc1, none_LEGAL.LegalDesc2,
none_LEGAL.LegalDesc3, none_PARCEL.PropStreetNumber,
none_PARCEL.PropStreetName, none_PARCEL.PropStreetMd,
none_PARCEL.PropStreetDr, none_PERMIT.R18PermitType,
none_PERMIT.R18IssueDateYy INTO [Two Year]
FROM (none_PARCEL INNER JOIN none_LEGAL ON none_PARCEL.ParcelNumber =
none_LEGAL.ParcelNumber) INNER JOIN none_PERMIT ON none_PARCEL.ParcelNumber
= none_PERMIT.ParcelNumber
WHERE (((none_PERMIT.R18PermitType)="TH") AND
((none_PERMIT.R18IssueDateYy)=2003)) OR (((none_PERMIT.R18PermitType)="CN"))
OR (((none_PERMIT.R18PermitType)="SF"))
ORDER BY none_PERMIT.R18IssueDateYy DESC;

Douglas J. Steele said:
All Kevin wanted to see was the SQL for your query.

Open the query in Design mode, then select Design View from the View menu.
That will show the SQL associated with your query. Highlight it all,
Click to expand...
copy
it
to the clipboard (Edit | Copy, or Ctrl+C), then paste it your followup post.

One thought, though: did you put the housing code criteria and 2003
Click to expand...
both
on
the same criteria line, or are they on separate lines? Putting them on
separate lines means one or the other: putting them on the same line means
one and the other.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



I tried posting the database but it's too large. What should I try next?
clyde


Can you post the SQL?

--
Kevin3NF

Sick of all that junk filling up your mailbox?
http://spamarrest.com/affl?2967001



I have a database of building permits that I maintain. When it comes
time
to pull out the year 2003 permits, making a Query, I have one column
that
has the housing code criteria and another column that has the year
2003
for
criteria. Yet, when I run the Query, all of the years show up. Any
ideas
how to prevent this?
Click to expand...
Click to expand...
Click to expand...
 
Clyde Thorne said:
I have a database of building permits that I maintain. When it comes time
to pull out the year 2003 permits, making a Query, I have one column that
has the housing code criteria and another column that has the year 2003 for
criteria. Yet, when I run the Query, all of the years show up. Any ideas
how to prevent this?
Click to expand...
 
Back
Top