Access Takes my Query and Alters it!

[dso808]

Please help. I have a large query which I develop in Notepad and then paste into the query window for the RecordSource property for a report. However, I've found out that the query gets mangled for some reason. Access seems to ADD and repeat statements in my query. This is what I put in:



SELECT [Service Request].[SR#], [Service Request].[Project Title], [Service Request].[Proj Category], [Service Request].[FHB Status], [Service Request].[Orig Commitment Date], [Service Request].[Revised Commitment Date], [Service Request].[IMG Contact], [Service Request].[Comments], [Service Request].[Comments2],NZ([Service Request].[Est Hrs - PC/LAN Mand],0) + NZ([Service Request].[Est Hrs - PC/LAN Discret],0) As EstHrs,'PC/LAN' As Dept,[Service Request].[Act Hrs - PC/LAN Mand] As ActHrsMand,[Service Request].[Act Hrs - PC/LAN Discret] As ActHrsDis, [Service Request].[Risk]
FROM [Service Request] INNER JOIN [Business Owner] ON [Service Request].[Bus Owner] = [Business Owner].[Dept/Div/Group]
WHERE (([Service Request].[Orig Commitment Date] <> null) OR ([Service Request].[Revised Commitment Date] <> null)) AND ([Service Request].[Copy - PC/LAN]=True) AND ([Service Request].[Proj Category]<>"Annual Production Support") AND ([Service Request].[FHB Status]="Approved" Or [Service Request].[FHB Status]="Implemented" Or [Service Request].[FHB Status]="On-Hold" OR ([FHB Status] = "Completed" AND [Actual Completion Date] >= #7/1/2010#)) AND (isNull([Service Request].[Act Hrs - PC/LAN Mand]) AND isNull([Service Request].[Act Hrs - PC/LAN Discret]))

And this is what I see when I re-open the query window:

SELECT [Service Request].[SR#], [Service Request].[Project Title], [Service Request].[Proj Category], [Service Request].[FHB Status], [Service Request].[Orig Commitment Date], [Service Request].[Revised Commitment Date], [Service Request].[IMG Contact], [Service Request].Comments, [Service Request].Comments2, NZ([Service Request].[Est Hrs - PC/LAN Mand],0)+NZ([Service Request].[Est Hrs - PC/LAN Discret],0) AS EstHrs, 'PC/LAN' AS Dept, [Service Request].[Act Hrs - PC/LAN Mand] AS ActHrsMand, [Service Request].[Act Hrs - PC/LAN Discret] AS ActHrsDis, [Service Request].Risk
FROM [Service Request] INNER JOIN [Business Owner] ON [Service Request].[Bus Owner] = [Business Owner].[Dept/Div/Group]
WHERE ((([Service Request].[Proj Category])<>"Annual Production Support") AND (([Service Request].[FHB Status])="Approved" Or ([Service Request].[FHB Status])="Implemented" Or ([Service Request].[FHB Status])="On-Hold") AND (([Service Request].[Orig Commitment Date]) Is Not Null) AND (([Service Request].[Copy - PC/LAN])=True) AND ((IsNull([Service Request].[Act Hrs - PC/LAN Mand]))<>False) AND ((IsNull([Service Request].[Act Hrs - PC/LAN Discret]))<>False)) OR ((([Service Request].[Proj Category])<>"Annual Production Support") AND (([Service Request].[FHB Status])="Approved" Or ([Service Request].[FHB Status])="Implemented" Or ([Service Request].[FHB Status])="On-Hold") AND (([Service Request].[Revised Commitment Date]) Is Not Null) AND (([Service Request].[Copy - PC/LAN])=True) AND ((IsNull([Service Request].[Act Hrs - PC/LAN Mand]))<>False) AND ((IsNull([Service Request].[Act Hrs - PC/LAN Discret]))<>False)) OR ((([Service Request].[Proj Category])<>"Annual Production Support") AND (([Service Request].[FHB Status])="Completed") AND (([Service Request].[Orig Commitment Date]) Is Not Null) AND (([Service Request].[Copy - PC/LAN])=True) AND ((IsNull([Service Request].[Act Hrs - PC/LAN Mand]))<>False) AND ((IsNull([Service Request].[Act Hrs - PC/LAN Discret]))<>False) AND (([Service Request].[Actual Completion Date])>=#7/1/2010#)) OR ((([Service Request].[Proj Category])<>"Annual Production Support") AND (([Service Request].[FHB Status])="Completed") AND (([Service Request].[Revised Commitment Date]) Is Not Null) AND (([Service Request].[Copy - PC/LAN])=True) AND ((IsNull([Service Request].[Act Hrs - PC/LAN Mand]))<>False) AND ((IsNull([Service Request].[Act Hrs - PC/LAN Discret]))<>False) AND (([Service Request].[Actual Completion Date])>=#7/1/2010#));

What's going on? For example, you can see that the statement '(([Service Request].[Actual Completion Date])>=#7/1/2010#))' is repeated at the end. Is this one of the many limitations in Access? Where a large query gets misinterpreted? Is there a workaround? Please advise, thanks!

[weekend00]

That's the way Access interpret the query in design view. It may run slower but the result won't be different as yours.

For example,
(a or b) and (c or d)
will be changed to
(a and c) or (a and d) or (b and c) or (b and d)

[dso808]

Thanks for the quick reply Weekend! You're right. After breaking everything down in Notepad, I think the Access-altered version of my query should be OK. It's perhaps the data in the record which may explain why it's not showing up in my report. Thanks again and have a great weekend :-)