error but cannot view query

[qwertyjjj]

I am trying to view a query in design view but everytime I right click on it (or any method) it brings up the:
syntax error in from clause.

I then have to say OK and that's it. Cannot view it.
Interestingly enough, the query works fine when called from my VBA code.
Know anyway round this?

 

[RuralGuy]

Can you just open it and then switch to SQL view? Have you done a Compact and Repair on the db?

 

[qwertyjjj]

nope, won't open at all.
It is only 4 queries that have some paramaters in them such as
Forms!blahbla.Form.combo_box1

They saved fine, they work fine when run in code, but they can't be opened anymore to change the code.

 

[RuralGuy]

Have you tried a /decompile? http://www.granite.ab.ca/access/decompile.htm
How about just a Compact and Repair?

 

[Guus2005]

In the immediate window type:

?currentdb.querydefs("yourquerynamehere").sql

voila

 

[qwertyjjj]

In the immediate window type:

?currentdb.querydefs("yourquerynamehere").sql

voila

in a VBA form/button or something?

 

[Guus2005]

type ctrl-g for the immediate window

HTH

 

[qwertyjjj]

Thanks.
Gives me:
SELECT TOP 10 [Name 1], [Profit ctr], ROUND((Sum([1To90]) + Sum([Not Due]) + Sum([91To120])) / 1000,0) AS Months0To3, ROUND((Sum([121To150])+Sum([151To180])) /1000,0) AS Months4To5, ROUND((Sum([181To365])) /1000,0) AS Months6To12, ROUND((Sum([MoreThan366])) /1000,0) AS [Months12+], ROUND(Sum(Total) / 1000,0) AS TotalDebt, ROUND(Sum([Bad Debt]) / 1000,0) AS BadDebt, Companies.Notes, Companies.ResponsibleFor
FROM (zarageddebt_CURR INNER JOIN [SELECT [Cost Centre] FROM Structure WHERE [Cost Centre] = Forms![TabsReport]![BusinessUnits1].Form.combo_CostCentreLevel GROUP BY [Cost Centre]
]. AS S ON S.[Cost Centre] = zarageddebt_CURR.[Profit ctr]) LEFT JOIN Companies ON Companies.CompanyName = zarageddebt_CURR.[Name 1]
GROUP BY [Name 1], [Profit ctr], Companies.Notes, Companies.ResponsibleFor
ORDER BY Sum([Bad Debt]) DESC , Sum(Total) DESC;

I have noticed this in my queries in that when saved, Access is changing the brackets to ].
When I want to edit them, I have to change them back to )

 

[Guus2005]

Thanks.
Gives me:
SELECT TOP 10 [Name 1], [Profit ctr], ROUND((Sum([1To90]) + Sum([Not Due]) + Sum([91To120])) / 1000,0) AS Months0To3, ROUND((Sum([121To150])+Sum([151To180])) /1000,0) AS Months4To5, ROUND((Sum([181To365])) /1000,0) AS Months6To12, ROUND((Sum([MoreThan366])) /1000,0) AS [Months12+], ROUND(Sum(Total) / 1000,0) AS TotalDebt, ROUND(Sum([Bad Debt]) / 1000,0) AS BadDebt, Companies.Notes, Companies.ResponsibleFor
FROM (zarageddebt_CURR INNER JOIN [SELECT [Cost Centre] FROM Structure WHERE [Cost Centre] = Forms![TabsReport]![BusinessUnits1].Form.combo_CostCentreLevel GROUP BY [Cost Centre]
]. AS S ON S.[Cost Centre] = zarageddebt_CURR.[Profit ctr]) LEFT JOIN Companies ON Companies.CompanyName = zarageddebt_CURR.[Name 1]
GROUP BY [Name 1], [Profit ctr], Companies.Notes, Companies.ResponsibleFor
ORDER BY Sum([Bad Debt]) DESC , Sum(Total) DESC;

I have noticed this in my queries in that when saved, Access is changing the brackets to ].
When I want to edit them, I have to change them back to )

Seems you have a rogue dot (Red) and i'm not sure but you have to remove your AS(Green). Then change the [] into () around your subquery.

tip:Use a subquery to simplify your query.

HTH

 

[qwertyjjj]

I think the AS is fine because it's an alias but this is what I mean...
when I save the query the ]. is not there because I type it properly and use a single bracket.
When I next view it, Access has changed it back so althought this query works fine when called from code, it will not open it because it thinks there is an error.

SELECT TOP 10 [Name 1],
[Profit ctr],
ROUND((Sum([1To90]) + Sum([Not Due]) + Sum([91To120])) / 1000,0) AS Months0To3,
ROUND((Sum([121To150])+Sum([151To180])) /1000,0) AS Months4To5,
ROUND((Sum([181To365])) /1000,0) AS Months6To12,
ROUND((Sum([MoreThan366])) /1000,0) AS [Months12+],
ROUND(Sum(Total) / 1000,0) AS TotalDebt, ROUND(Sum([Bad Debt]) / 1000,0) AS BadDebt,
Companies.Notes,
Companies.ResponsibleFor
FROM
(
zarageddebt_CURR INNER JOIN
(
SELECT [Cost Centre] FROM Structure WHERE [Cost Centre] = Forms![TabsReport]![BusinessUnits1].Form.combo_CostCentreLevel GROUP BY [Cost Centre]
)
AS S ON S.[Cost Centre] = zarageddebt_CURR.[Profit ctr]
)
LEFT JOIN Companies ON Companies.CompanyName = zarageddebt_CURR.[Name 1]
GROUP BY [Name 1], [Profit ctr], Companies.Notes, Companies.ResponsibleFor
ORDER BY Sum([Bad Debt]) DESC , Sum(Total) DESC;

 

[Guus2005]

Dont need an AS as an alias for a subquery only for fields.

select s.* from (select X.* from X) s

Perhaps the plussign in [Month12+] is a problem?
Tried to save the subquery yet?
Run the subquery to see if there are any anomalies.

Have a nice weekend!