Query anomoly in Access-HOW TO FIX!!! 1

[netrusher]

Below is the code I have for a query in my database. The
top one is how it starts out and the bottom one is how it
ends up. My understanding is this is a known issue in
ACCESS. My problem is how to fix this so I do not have to
keep going in and removing the brackets everytime I have
to change the query or to to design view. Most of the time
I get the following error and cannot even open the query
in design view to fix it. I have to do it over.

I have highlighted in red the part of the SQL Code that changes.

Invalid bracketing of name 'Select distinct [WorkUnit'.

GOOD QUERY

SELECT 'Total Work Units' AS FaultCategory, Count([WorkUnit]) AS [WU Totals]
FROM (Select Distinct [WorkUnit]
         FROM WorkUnitsFaultsMainTBL
            WHERE [Process Re-Directs] = True AND BuildID IN ("E010","C809","F001","C810","F187","A910","M173","M174","L177","B875") AND
            PossibleCause NOT IN ("Out of Stock") AND
                [TodaysDate] BETWEEN [Forms]![Queries_ReportsFRM]![StartDateTxt] AND
                                        [Forms]![Queries_ReportsFRM]![EndDateTxt]) AS vTbl;

CHANGES TO THIS-BAD QUERY

SELECT 'Total Work Units' AS FaultCategory, Count([WorkUnit]) AS [WU Totals]
FROM  [COLOR=red][[/color]Select Distinct [WorkUnit]
         FROM WorkUnitsFaultsMainTBL
            WHERE [Process Re-Directs] = True AND BuildID IN ("E010","C809","F001","C810","F187","A910","M173","M174","L177","B875") AND
            PossibleCause NOT IN ("Out of Stock") AND
                [TodaysDate] BETWEEN [Forms]![Queries_ReportsFRM]![StartDateTxt] AND
                                        [Forms]![Queries_ReportsFRM]![EndDateTxt[COLOR=red]]].  [/color]  AS vTbl;

 

[PHV]

Create a query named, say, qryDistinctWorkUnit:

SELECT DISTINCT WorkUnit
FROM WorkUnitsFaultsMainTBL
WHERE [Process Re-Directs] = True AND PossibleCause <> "Out of Stock"
AND BuildID IN ("E010","C809","F001","C810","F187","A910","M173","M174","L177","B875")
AND TodaysDate BETWEEN [Forms]![Queries_ReportsFRM]![StartDateTxt] And [Forms]![Queries_ReportsFRM]![EndDateTxt]

And now your query:

SELECT 'Total Work Units' AS FaultCategory, Count("*") AS [WU Totals]
FROM qryDistinctWorkUnit

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[netrusher]

Thanks PH!