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.

If you could send me a stripped down version of yoru database with just some dummy data, I can take a look at it. You can upload it here or just go to my website and e-mail me through there or you can even upload the database through the "Request Information" section. It would be easier to see what's going on that way.