Need a favor: please paste this SQL to your db and saved it 1

[Clipper2000]

Hi everyone,

I have a query that is working perfectly except that some how my Access ALWAYS convert the ( into [ and add a period after the ] when I saved this query:

Thus, please do me a big favor by paste the query below to your Access query and save it, then open it again to see if Access has messed up the brackets. You don't need to have the tables setup, just paste the query as is will do.

Thanks in advance and thank you PHV for answer this question earlier.

SELECT B.GLM, A.Description, A.Accpac, A.Amount, A.Category, A.Period, A.Year
FROM [tbl90707(Single)] AS A INNER JOIN (SELECT DISTINCT GLM, Accpac FROM [GLM:ACCPAC]) B ON A.Accpac = B.Accpac
WHERE A.Accpac < "40000" AND A.Category <>"CFI" AND A.Period = 11 and A.Year = 2004;

 

[Golom]

I'm not going to be able to do that because I don't have all the tables and fields in my system that the query refers to. An attempt to save it will error out before the save completes.

However, YES ... it will change the ( ... ) to [ ... ].

Why ... I don't know ... but the modified version does seem to work. Is it giving you a problem other than visual appearance?

 

[Clipper2000]

Hi Golm,

I have try paste the query into blank database and it saved ok. So if you wouldn't mind, please open a blank database and saved it there to see if it will do the same.

Access will not only convert the ( into [ and add a period at the end, it will also add the key word "AS" after.

????

 

[Golom]

Yep ... it does the same thing. The problem seems to be that, if you attempt to save it with this modified format you get "Error in From Clause". You need to restore the parentheses before it can be saved again.

 

[Clipper2000]

Hi,

>>> The problem seems to be that, if you attempt to save it with this modified format you get "Error in From Clause". You need to restore the parentheses before it can be saved again. <<<

My access saved it without giving me the warning "Error in From Clause", it just save it.

Any sugestion how I can overcome this. PHV sugested I flipped the join, but that gave me some extra null values which I don't want. I really would like to make this query work as is (without the warning error)

 

[GingerR]

I did it. saved fine. changed to square brackets. no error message. It's also trying to add ". AS" before the B

SELECT B.GLM, A.Description, A.Accpac, A.Amount, A.Category, A.Period, A.Year
FROM [tbl90707(Single)] AS A INNER JOIN [SELECT DISTINCT GLM, Accpac FROM [GLM:ACCPAC]]. AS B ON A.Accpac = B.Accpac
WHERE A.Accpac < "40000" AND A.Category <>"CFI" AND A.Period = 11 and A.Year = 2004;

 

[Clipper2000]

Thank you GingerR !

So now the question is: is this a bug? and do we have a way to make this query saved properly?

I am willing to add additional brackets around them but that just doesn't seem to do it.

I'm glad you share my view on this, and hoping someone can help me solve the problem.

 

[GingerR]

it's hard for me to figure out cause I don't have your table structure. If you can tell me that, and some sample data, I'll see what I can do. Maybe it's just the way you have it written, I have no idea...

 

[Clipper2000]

Hi,

These codes should create the necessary tables, make sure there is a reference to ADO Ext. 2.7 DLL

Function CreateTable90707()

    Dim cat As ADOX.Catalog
    Dim tblSLData As ADOX.Table
    Dim col1 As ADOX.Column, col2 As ADOX.Column, col3 As ADOX.Column, col4 As ADOX.Column, col5 As ADOX.Column
    Dim col6 As ADOX.Column
    
    Set cat = New ADOX.Catalog
    cat.ActiveConnection = CurrentProject.Connection
    
    ' Delete the table, if it exists.
    On Error Resume Next
    cat.Tables.Delete "tbl90707(single)"
    
    Set tblSLData = New ADOX.Table
    tblSLData.Name = "tbl90707(single)"
    
    Set col1 = New ADOX.Column: col1.Name = "Accpac": col1.Type = adVarWChar: col1.DefinedSize = 5
    Set col2 = New ADOX.Column: col2.Name = "Description": col2.Type = adVarWChar: col2.DefinedSize = 10
    Set col3 = New ADOX.Column: col3.Name = "Amount": col3.Type = adDouble
    Set col4 = New ADOX.Column: col4.Name = "Category": col4.Type = adVarWChar: col4.DefinedSize = 5
    Set col5 = New ADOX.Column: col5.Name = "Period": col5.Type = adVarWChar: col5.DefinedSize = 2
    Set col6 = New ADOX.Column: col6.Name = "Year": col6.Type = adDouble
    
    With tblSLData.Columns
        .Append col1: .Append col2: .Append col3: .Append col4: .Append col5
        .Append col6
    End With
    
    With cat.Tables
        .Append tblSLData
        .Refresh
    End With
    
    Application.RefreshDatabaseWindow
    Set col1 = Nothing: Set col2 = Nothing: Set col3 = Nothing: Set col4 = Nothing: Set col5 = Nothing
    Set col6 = Nothing

    Set cat = Nothing
    Set tblSLData = Nothing
    
    On Error GoTo 0
    
End Function

and for the GLM table:

Function CreateTableGLM()

    Dim cat As ADOX.Catalog
    Dim tblSLData As ADOX.Table
    Dim col1 As ADOX.Column, col2 As ADOX.Column, col3 As ADOX.Column, col4 As ADOX.Column, col5 As ADOX.Column
    Dim col6 As ADOX.Column
    
    Set cat = New ADOX.Catalog
    cat.ActiveConnection = CurrentProject.Connection
    
    ' Delete the table, if it exists.
    On Error Resume Next
    cat.Tables.Delete "GLM:Accpac"
    
    Set tblSLData = New ADOX.Table
    tblSLData.Name = "GLM:Accpac"
    
    Set col1 = New ADOX.Column: col1.Name = "GLM": col1.Type = adDouble
    Set col2 = New ADOX.Column: col2.Name = "Sub": col2.Type = adDouble
    Set col3 = New ADOX.Column: col3.Name = "Prod": col3.Type = adDouble
    Set col4 = New ADOX.Column: col4.Name = "Cus": col4.Type = adVarWChar: col4.DefinedSize = 5
    Set col5 = New ADOX.Column: col5.Name = "Description": col5.Type = adVarWChar: col5.DefinedSize = 10
    Set col6 = New ADOX.Column: col6.Name = "Accpac": col6.Type = adVarWChar: col6.DefinedSize = 5
    
    With tblSLData.Columns
        .Append col1: .Append col2: .Append col3: .Append col4: .Append col5
        .Append col6
    End With
    
    With cat.Tables
        .Append tblSLData
        .Refresh
    End With
    
    Application.RefreshDatabaseWindow
    Set col1 = Nothing: Set col2 = Nothing: Set col3 = Nothing: Set col4 = Nothing: Set col5 = Nothing
    Set col6 = Nothing

    Set cat = Nothing
    Set tblSLData = Nothing
    
    On Error GoTo 0
    
End Function

Thanks.

 

[GingerR]

yes it still changes the brackets, but seems to work. I don't have data. the query runs. I've been playing with it(deconstructing it). Don't know if/how I can help. Is it giving you wrong data?

 

[Clipper2000]

Hi GingerR,

You are absolutely right and seems your copy of Access is exactly same as mine.

Everything runs no problem on first try. The real issue is after I saved the query and try to run it the second time, it fails. The reason why it fails is because now the brackets have changed and Access added "]. AS" at the end when it shouldn't have. This is wild. I think it is a bug.

 

[GingerR]

try picking it apart: make a query in place of the "B" and save it, then use that in your query. I did, just named it "B" which is probably dumb, but I'm just testing after all. I also renamed the tbl90707. BTW, I hate your table names ) I wondered if at first it was having problems with the colon in the "GLM:ACCPAC" table name, but the same bracket thing happened even when I changed the name and omitted the colon. So maybe saving a "B" query isn't as cool, but i think it'll work:

SELECT B.GLM, A.Description, A.Accpac, A.Amount, A.Category, A.Period, A.Year
FROM tbl90707 AS A INNER JOIN B ON A.Accpac=B.Accpac
WHERE A.Accpac<"40000" And A.Category<>"CFI" And A.Period=11 And A.Year=2004;

 

[Clipper2000]

Hi GingerR,

You are right again. I tried picking it apart by having 2 separate queries and it worked. I am sorry for getting you through with all this. My purpose here is curiosity and testing (or play with) the Access query designer, not necessary JET 4.0 engine. I think JET 4.0 is fine, but there seems to be a bug in the Access query designer (the tool used to create the queries). This is evident when I tried pasting the query into a blank database with nothing in it, save it, and the designer still change the brackets and add ". AS" at the end.

Thanks, this is entirely new to me.

 

[GingerR]

Well, I've learned something too. It's very wierd. I've never run across any such thing and I've been doing this for 5+ years. No skin off my back, really. Thanks for the good times )

g

 

[Clipper2000]

This is wild !

Instead of creating the query definition in the query designer, which will not saved properly. The whole thing works if I create the query using DAO like this:

Sub CreateQry()
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    
    '-------------------------------------------
    'set DAO DataBase ...
    '-------------------------------------------
    
    Set db = CurrentDb
    '---------------------------------------------
    
    On Error Resume Next
    db.QueryDefs.Delete ("Test")
    On Error GoTo 0
    
    strSQL = "SELECT B.GLM, A.Description, A.Accpac, A.Amount, A.Category, A.Period, A.Year " & _
            "FROM [tbl90707] AS A INNER JOIN " & _
            "(SELECT DISTINCT GLM, Accpac FROM [GLM:ACCPAC]) B ON A.Accpac = B.Accpac " & _
            "WHERE A.Accpac < '40000' AND A.Category <>'CFI' AND A.Period = 10 and A.Year = 2004;"
   
     db.CreateQueryDef "Test123", strSQL
 
End Sub

Yes, this worked, but when you open the query after the code runs to look at the string, it will still have the ". AS" but it will run anyway. Wild !

 

[MichaelRed]

Some confusion in the structure? Generally, SQL expects SUBQUERIES withoout an "IN" (or "AS") to return a SCALAR value (~~def one col of one row). Returning a SET of values (multiple ROWs and/or multiple columns) expects the use of the IN predicate. It appears that Ms. A. query builder is, perhaps, attempting to 'auto-correct' the syntax, but choosing a "fix" which is not quite what you qant it to?





MichaelRed

 

[Clipper2000]

You are right that the query builder have something to do with it.

The correct syntax is:

SELECT B.GLM, A.Description, A.Accpac, A.Amount, A.Category, A.Period, A.Year
FROM tbl90707 AS A INNER JOIN (SELECT DISTINCT GLM, Accpac FROM [GLM:ACCPAC]) B ON A.Accpac = B.Accpac
WHERE A.Accpac < "40000" AND A.Category <>"CFI" AND A.Period = 11 and A.Year = 2004;

However, Access saved it as this:

SELECT B.GLM, A.Description, A.Accpac, A.Amount, A.Category, A.Period, A.Year
FROM tbl90707 AS A INNER JOIN [SELECT DISTINCT GLM, Accpac FROM [GLM:ACCPAC]]. AS B ON A.Accpac = B.Accpac
WHERE (((A.Accpac)<"40000") AND ((A.Category)<>"CFI") AND ((A.Period)=11) AND ((A.Year)=2004));

It other words, Access added ]. AS which obvious is incorrect, and any subsequent saves will failed. However, if I switch it to the designer view before I saved, then Access will allows me to save. The query will run even though the syntax is incorrect (however, in the desiger view, it was represented correctly).

Thus, it appears that when I code the SQL string, Access built the structure in the query designer in the background, and once built, what is in the designer takes precedence over the SQL string

 

[MichaelRed]

perhaps you will re-read my post. The issue is (seems to be?) that you are NOT using the propper syntax for the subquery. "IN" is expected / required for subqueries which return other than a SCALAR value. Yours DOES NOT return a scalar, so YOUR failure to provide the proper syntax requires an adjustment. Ms. A. has simply made a choice in attempting to correct YOUR syntatical error.





MichaelRed

 

[PHV]

Michael, the FROM (SELECT .....) AS alias syntax is perfectly legal in Ansi SQL.
Everybody knows the lack of ANSI compliance from Ms. A...
I personally consider the FROM [SELECT .....]. AS alias transformation as a BUG of the query designer.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MichaelRed]

I did not mean (or think I said) that the alaias predicatte ("AS") was not 'legal'. However it is my (quite limited knowledge/experience) that subqueries which return more than a SINGLE (e.g. SCALAR) value require a predicate ([In | AS | 'some relational operator'] Further, subqueries (non "Scalar") generally appear in the from/where clause, not as a field list in the 'top level' select statement.

My SQL Reference does not even list "alaias" ("AS") as a keyword, while devoting some ten pages to the construct, placement and predicates of subqueries.

I'm sure that ther is MUCH more for me to know/learn (inluding the propper use & construction of SQL) and hopefully I will have the opportunity to both learn and practice the positive portions of these learning experiences.

I 'llok at' the addition of hte AS clause as the completion of the ALAIAS clause of the subquery itself:

SELECT B.GLM, A.Description, A.Accpac, A.Amount, A.Category, A.Period, A.Year
FROM [tbl90707(Single)] AS A 

INNER JOIN 

(SELECT DISTINCT GLM, Accpac FROM [GLM:ACCPAC])
[b][COLOR=red] "as" B[/color][/b]
ON A.Accpac = B.Accpac
WHERE A.Accpac < "40000" AND A.Category <>"CFI" AND A.Period = 11 and A.Year = 2004;

See the insertion of the "stuff above and compare to the base select statement above.

Perhaps you would care to enlilghten me re the road I have'n traveled (correctly) in arriving at this juncture?





MichaelRed