Incorrect Query Result in Access 2007

[CarlRostron]

No idea why but I am running a query and I am getting the wrong results:

SELECT TOffice.OfficeName, TRevisionType.TypeName, TProjectManager.PlannerForename AS PMForename, TProjectManager.PlannerSurname AS PMSurname, TRevisionDetail.NoPhases, TRevisionDetail.Phase1Deploy, TRevisionDetail.Phase1DeployReforecast, TOffice.RegionArea
FROM TRevisionType INNER JOIN ((TProjectManager INNER JOIN (TOffice INNER JOIN TRevision ON TOffice.OfficeID = TRevision.OfficeID) ON (TProjectManager.ProjectManagerID = TRevision.ManagedBy) AND (TProjectManager.ProjectManagerID = TOffice.ManagedBy)) INNER JOIN TRevisionDetail ON TRevision.RevisionID = TRevisionDetail.RevisionID) ON TRevisionType.TypeID = TRevisionDetail.RevisionType
WHERE (((TRevisionDetail.Phase1DeployReforecast)>Format(#11/15/2011#,"dd/mm/yyyy") And (TRevisionDetail.Phase1DeployReforecast)<=Format(#11/22/2011#,"dd/mm/yyyy")) AND ((TOffice.RegionArea)="n" Or (TOffice.RegionArea)="N")) OR (((TRevisionDetail.Phase1DeployReforecast)>Format(#11/15/2011#,"dd/mm/yyyy") And (TRevisionDetail.Phase1DeployReforecast)<=Format(#11/22/2011#,"dd/mm/yyyy")))
ORDER BY TOffice.OfficeName;

I am filtering out on lots of things but specifically where RegionArea is either "N" or "n" but you can see in the attcahed screenshots that the SQL query is giving me back results which have N in them

Please help.

 

[vbaInet]

I would advise that you remove all your criteria and put them in one by one. You have an OR statemement there that doesn't include the N or n criteria.

 

[MSAccessRookie]

No idea why but I am running a query and I am getting the wrong results:

SELECT TOffice.OfficeName, TRevisionType.TypeName, TProjectManager.PlannerForename AS PMForename, TProjectManager.PlannerSurname AS PMSurname, TRevisionDetail.NoPhases, TRevisionDetail.Phase1Deploy, TRevisionDetail.Phase1DeployReforecast, TOffice.RegionArea
FROM TRevisionType INNER JOIN ((TProjectManager INNER JOIN (TOffice INNER JOIN TRevision ON TOffice.OfficeID = TRevision.OfficeID) ON (TProjectManager.ProjectManagerID = TRevision.ManagedBy) AND (TProjectManager.ProjectManagerID = TOffice.ManagedBy)) INNER JOIN TRevisionDetail ON TRevision.RevisionID = TRevisionDetail.RevisionID) ON TRevisionType.TypeID = TRevisionDetail.RevisionType
WHERE (((TRevisionDetail.Phase1DeployReforecast)>Format(#11/15/2011#,"dd/mm/yyyy") And (TRevisionDetail.Phase1DeployReforecast)<=Format(#11/22/2011#,"dd/mm/yyyy")) AND ((TOffice.RegionArea)="n" Or (TOffice.RegionArea)="N")) OR (((TRevisionDetail.Phase1DeployReforecast)>Format(#11/15/2011#,"dd/mm/yyyy") And (TRevisionDetail.Phase1DeployReforecast)<=Format(#11/22/2011#,"dd/mm/yyyy")))
ORDER BY TOffice.OfficeName;

I am filtering out on lots of things but specifically where RegionArea is either "N" or "n" but you can see in the attcahed screenshots that the SQL query is giving me back results which have N in them

Please help.

I reformatted the WHERE Clause of your Query, and determined that it seems to be doing exactly what you are asking it to do. The GREEN Code will get only records that have "n" or "N", while the RED Code (which is the exact same code as the first part of the GREEN code) will get all records. Is there some kind of error here? As written, the GREEN code would not be required at all.

[FONT=Times New Roman][SIZE=3][B]WHERE [/B][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=seagreen][B]([/B][/COLOR][/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman][B][COLOR=seagreen]  ([/COLOR][/B][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman][B][COLOR=seagreen]    (TRevisionDetail.Phase1DeployReforecast)>Format(#11/15/2011#,"dd/mm/yyyy") And [/COLOR][/B][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman][B][COLOR=seagreen]    (TRevisionDetail.Phase1DeployReforecast)<=Format(#11/22/2011#,"dd/mm/yyyy")[/COLOR][/B][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman][B][COLOR=seagreen]  ) AND [/COLOR][/B][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman][B][COLOR=seagreen]  ([/COLOR][/B][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman][B][COLOR=seagreen]    (TOffice.RegionArea)="n" Or (TOffice.RegionArea)="N"[/COLOR][/B][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman][B][COLOR=seagreen]  )[/COLOR][/B][/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3][B][COLOR=seagreen])[/COLOR] [COLOR=red]OR [/COLOR][/B][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=red][B]([/B][/COLOR][/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman][B][COLOR=red]  ([/COLOR][/B][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman][B][COLOR=red]    (TRevisionDetail.Phase1DeployReforecast)>Format(#11/15/2011#,"dd/mm/yyyy") And [/COLOR][/B][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman][B][COLOR=red]    (TRevisionDetail.Phase1DeployReforecast)<=Format(#11/22/2011#,"dd/mm/yyyy")[/COLOR][/B][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman][B][COLOR=red]  )[/COLOR][/B][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman][B][COLOR=red])[/COLOR][/B][/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3][B]ORDER BY TOffice.OfficeName;[/B][/SIZE][/FONT]

 

[CarlRostron]

Thanks folks - you have made me relook at this again now and I have sorted it. Thanks for taking the time to look at it for me.