Query anomoly in Access-HOW TO FIX!!!

[aldeb]

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 CODE

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;

BAD CODE

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;

 

[KenHigg]

So is this in a vba module or are trying to put in the sql view of a stored query?

 

[aldeb]

this is part of sql query

 

[ByteMyzer]

The following VBA code should fix any queries in your database that use this form of subquery:

[COLOR="Navy"]Sub[/COLOR] RefreshQueryDefs()

    [COLOR="navy"]Dim[/COLOR] qd [COLOR="navy"]As[/COLOR] QueryDef
    [COLOR="navy"]Dim[/COLOR] col [COLOR="navy"]As[/COLOR] Collection
    [COLOR="navy"]Dim[/COLOR] sSQL [COLOR="navy"]As String
    Dim[/COLOR] X [COLOR="navy"]As Long

    Set[/COLOR] col = [COLOR="navy"]New[/COLOR] Collection

    [COLOR="navy"]For[/COLOR] X = 0 [COLOR="navy"]To[/COLOR] CurrentDb.QueryDefs.Count - 1
        [COLOR="navy"]If[/COLOR] InStr(1, CurrentDb.QueryDefs(X).SQL, "[SELECT") > 0 [COLOR="navy"]Then[/COLOR]
            col.Add CurrentDb.QueryDefs(X).Name
        [COLOR="navy"]End If
    Next[/COLOR] X

    For X = 1 To col.Count
        sSQL = Replace(Replace(CurrentDb.QueryDefs(col(X)).SQL, _
            "[SELECT", "(SELECT"), "]. AS", ") AS")
        CurrentDb.QueryDefs.Delete col(X)
        [COLOR="navy"]Set[/COLOR] qd = CurrentDb.CreateQueryDef
        qd.Name = col(X)
        qd.SQL = sSQL
        CurrentDb.QueryDefs.Append qd
        [COLOR="navy"]Set[/COLOR] qd = [COLOR="navy"]Nothing
    Next[/COLOR] X

[COLOR="navy"]End Sub[/COLOR]

 

[aldeb]

Where do I insert this code and will it help with mulitple queries using subqueries?

 

[ByteMyzer]

The code is written to fix ALL of the queries in your database with this scenario. Since, by your question, I gather that you are new to VBA (Visual Basic for Applications), follow the steps below:

1) In your database, click on the Modules tab.

2) Click on the [New] button to create a new Module. This will open up the Visual Basic workspace for the Module.

3) Copy the code that I posted and paste it into the Module workspace.

4) Click on the Disk icon to save the Module. When prompted for the Module Name, type mdlRefreshQueryDefs and click the [OK] button.

5) Close the Microsoft Visual Basic window.

6) Click on the Macros tab.

7) Click on the [New] button to create a new Macro.

8) In the Action select RunCode.

9) In the Function Name parameter at the bottom of the screen, type RefreshQueryDefs()

10) Click on the Disk icon to save the Macro. When prompted for the Macro Name, type RefreshQueryDefs and click the [OK] button.


Now, any time you wish to execute this fix, simply double-click on the Macro to run the code.