There's no such thing as a stupid question, but they're the easiest to answer.


Search Search
Search for:
Tech Support Guy > > >

Solved: Access multiple date query issue


nomad0321's Avatar
nomad0321 nomad0321 is offline
Member with 2 posts.
Join Date: Feb 2012
23-Feb-2012, 01:29 PM #1
Question Solved: Access multiple date query issue

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 show up 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, 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
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;

nomad0321's Avatar
nomad0321 nomad0321 is offline
Member with 2 posts.
Join Date: Feb 2012
23-Feb-2012, 03:39 PM #2
Solved in another forum.
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

access, dates, parameter, query

Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
Thread Tools

You Are Using: Server ID
Trusted Website Back to the Top ↑