Hello!

I have an issue I've looked at 50 different ways from Sunday and am stuck on the resolution. I am tracking metrics for a project, stored on SharePoint, which I dump into Excel and use Access 2007 for my reports.

The report has multiple date fields, i.e.: R4RPlan_Date, R4RActual_Date, BusPlan_Date, BusActual_Date, etc... on table RICEFW_Dates.

What I am trying to do is set a parameter to query all the fields for a specific date, i.e. <[Report Date], without having to run multiple parameter queries. The end result will be a count of the number of dates in each field meeting the criteria.

The problem comes in when teams complete their criteria before it was planned, i.e., R4RPlan_Date = 3/23/12, R4RActual_Date = 2/6/12, <[Report Date] = 2/22/12.

If I add <[Report Date] as a criteria for the R4RPlan_Date, I will get all the plan dates I'm looking for, but I will not get all of the actuals since the plan date is after the specified [Report Date], but the actual was completed w/in the frame of the parameter. If I put the [Report Date] parameter in the R4RActual_Date field, I get the correct number of actuals, but since I am querying the field with lesser amount of data, I do not get all of the planned dates which meet the [Report Date] parameter. So if the plan date is greater than the parameter date, I want it to be counted IF the actual date is not null, otherwise it should follow the parameter criteria (<[Report Date]). This needs to completed for every date field in the query. Each date has a planned and actual.

The other caveat is there is a revised date which will supersede the plan date. So if R4R_Revised_Date > R4RPlan_Date, then R4R_Revised_Date should be used for the parameter criteria [Report Date], otherwise the R4RPlan_Date should be used for the parameter criteria.

I apologize if this is confusing, but any help would be appreciated. I would be more than happy to clarify any of the points I've made.

Here is the SQL I am working with:

PARAMETERS [Report Date] DateTime;
SELECT RICEFW_Dates.Owning_Team, RICEFW_Dates.Del_Type, Count(RICEFW_Dates.R4RPlan_Date) AS CountOfR4RPlan_Date, Count(RICEFW_Dates.R4RActual_Date) AS CountOfR4RActual_Date
FROM RICEFW_Dates
WHERE (((RICEFW_Dates.Scope)<>"Removed") AND ((RICEFW_Dates.Owning_Team) Like "*" & [Team]) AND ((RICEFW_Dates.R4RPlan_Date)<[Report Date]))
GROUP BY RICEFW_Dates.Owning_Team, RICEFW_Dates.Del_Type;


Thanks.

I have tried that. Because it is a parameter query, I tried R4RPlan_Date < [Report Date] OR R4RActual_Date < [Report Date]. It didn't work, it just went off the R4RPlan_Date. I need it to look at the fields individually, without me having to write separate queries for each date field or it asking the user to reenter the date multiple times.

Make sense?