I have a field called Tbl_PIP.[Requires Specs] and a field called
Tbl_PIP_Phases.Date. I want to count the items in Tbl_PIP.[Requires Specs]
but only if those items happen to fall within the after 1/1/07 or prior to
todays date.

Example: Hotel ABC has 5 items due on 7/1/07 (These would go to
Tbl_PIP.[Requires Specs]. The hotel also has 5 items due on 12/1/07 (These
would also go to Tbl_PIP.[Requires Specs]. We have approved the 5 items due
on 7/1/07 (Tbl_PIP.[Specs Approved]) but not the 5 from December.

I want the report to look like this.
Items Due Specs Approved
%%
Hotel ABC 5 5
100%%

What I'm getting is...

Items Due Specs Approved
%%
Hotel ABC 10 5
50%%

I know this is kind of confusing but any help would be appreciated!

On Tue, 7 Aug 2007 11:46:02 -0700, Chris discussions.microsoft.com>
wrote:

SELECT Tbl_Hotels.ID, Tbl_Hotels.[Prop Code], Tbl_Hotels.[Property Name],
([CountofSpec Approval Date]/[CountofRequires Specs]) AS [Percent Approved],
IIf(Tbl_Hotels![Contractual Reno]=True,"Contractual Reno","Non-Contractual
Reno") AS [Reno Type], Count(Tbl_PIP.[Requires Specs]) AS [CountOfRequires
Specs], Count(Tbl_PIP.[Spec Approval Date]) AS [CountOfSpec Approval Date]
FROM Tbl_PIP INNER JOIN (Tbl_Hotels INNER JOIN (Tbl_Inspect INNER JOIN
Tbl_PIP_Phases ON Tbl_Inspect.InspectionID = Tbl_PIP_Phases.InspectionID) ON
Tbl_Hotels.ID = Tbl_Inspect.[Hotel ID]) ON (Tbl_PIP.InspectionID =
Tbl_PIP_Phases.InspectionID) AND (Tbl_PIP.InspectionID =
Tbl_Inspect.InspectionID)
WHERE (((Tbl_PIP.[Requires Specs])=True) AND ((Tbl_Inspect.Status)="Reno")
AND ((Tbl_PIP_Phases.Date)>=#1/1/2007#) AND...

On Tue, 7 Aug 2007 11:46:02 -0700, Chris discussions.microsoft.com>
wrote:

The problem is probably with the OR clause retrieving records you aren't
expecting - parenthesis nesting can be tricky! Let's untangle the WHERE clause
here:

John,

That worked really well, the only problem is that I have a hotel that has
data and the Tbl_PIP_Phases.[Prior to Opening]) is true, yet nothing
displays. Any suggestions?

"John W. Vinson" wrote:

On Wed, 8 Aug 2007 09:12:05 -0700, Chris discussions.microsoft.com>
wrote:

The only problem with that is I want the record to display if the
Tbl_PIP_Phases.[Date] is between our criteria OR if Tbl_PIP_Phases.[Prior to
Opening] is True.

"John W. Vinson" wrote:

On Wed, 8 Aug 2007 11:50:01 -0700, Chris discussions.microsoft.com>
wrote:

Sorry... as I say, I don't know the details of your database and you did not
post the logic so I guessed, and guessed wrong. Do the other criteria apply in
either case?

John W. Vinson [MVP]

John,

All the other requirements are true for all cases.

"John W. Vinson" wrote:

On Wed, 8 Aug 2007 14:26:03 -0700, Chris discussions.microsoft.com>
wrote: