SQL changes in design view

[PJFry]

Below is a statement that works correctly; however, when I open it to make a change, part of the statement is automatically changed and causes an error in the query.

SELECT [CurCount]/[PrCount] AS 7RollRate, T1.CurMonth, 7 AS Bucket, (SELECT MAX(BusinessDt) FROM WVR) AS BusinessDt
FROM (SELECT Sum(WVR.Count) AS CurCount, Month([BusinessDt]) AS CurMonth, WVR.Owner AS CurOwner
FROM WVR
WHERE (((WVR.BusinessDt)=(SELECT MAX(BusinessDt) FROM WVR)) AND ((WVR.Bucket)=7))
GROUP BY Month([BusinessDt]), WVR.Owner
HAVING (((WVR.Owner)="Collections"))) AS T1, (SELECT Sum(WVR.Count) AS PrCount, Month([BusinessDt]) AS [PrMonth], WVR.Owner AS PrOwner
FROM WVR
WHERE (((WVR.BusinessDt)=DateAdd("m",-1,(SELECT MAX(BusinessDt) FROM WVR))) AND ((WVR.Bucket)=6))
GROUP BY Month([BusinessDt]), WVR.Owner
HAVING (((WVR.Owner)="Collections"))) AS T2;

This is what the sql looks like after I open it in design view, but before I correct it (blue and red text):

SELECT [CurCount]/[PrCount] AS 7RollRate, T1.CurMonth, 7 AS Bucket, (SELECT MAX(BusinessDt) FROM WVR) AS BusinessDt
FROM [COLOR=blue][SELECT Sum(WVR.Count) AS CurCount, Month([BusinessDt]) AS CurMonth, WVR.Owner AS CurOwner
FROM WVR
WHERE (((WVR.BusinessDt)=(SELECT MAX(BusinessDt) FROM WVR)) AND ((WVR.Bucket)=7))
GROUP BY Month([BusinessDt]), WVR.Owner
HAVING (((WVR.Owner)="Collections"))]. AS T1,[/color] [COLOR=red] [SELECT Sum(WVR.Count) AS PrCount, Month([BusinessDt]) AS [PrMonth], WVR.Owner AS PrOwner
FROM WVR
WHERE (((WVR.BusinessDt)=DateAdd("m",-1,(SELECT MAX(BusinessDt) FROM WVR))) AND ((WVR.Bucket)=6))
GROUP BY Month([BusinessDt]), WVR.Owner
HAVING (((WVR.Owner)="Collections"))]. AS T2;[/color]

The '(' for the select statements change to a '['. I have never run into this before. Any thoughts?

 

[AlexCuse]

I have posted this same question before, and never had an answer. I actually knew what your question was before I opened the thread. I think that access just wants you to do stuff like this in multiple queries?

Anyway, if you replace the brackets with parentheses, and remove teh period, you will be able to edit it. I don't know why it happens, but it certainly is a nuisance eh!

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[AlexCuse]

ps- awesome handle!

Ignorance of certain subjects is a great part of wisdom

 

[PJFry]

Nuts. Well, at least I am not the only one...

The good part is that I am mostly done with changes.

Glad you like the handle! Not many people get it.

 

[AlexCuse]

My theory is that access' 'query saving engine' or whatever you would call it, is not as capable as the jet engine is, and it just can't parse the query when it is saved...

This actually just made me think of something. Access is deployed to my computer from a remote server, and because of that I am missing the sqlparse.dll (I think this is the name). I believe this dll is part of office. If you don't have this dll present, it could be that causing the issue maybe? (note - I just went back to where I found out I was missing this DLL, and mentioned that I thought it could be causing these issues then (two weeks ago!)). So if you too are missing this DLL, we might be on to something. Let me know what your search turns up.

Thanks,

Alex


PS - It was a very under-viewed show, IMO. Every bit as good as the Simpsons. Its' a shame it wasn't on longer...

Ignorance of certain subjects is a great part of wisdom