By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,460 Members | 2,215 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,460 IT Pros & Developers. It's quick & easy.

Parsing SQL

PhilOfWalton
Expert 100+
P: 1,430
I think this one is impossible, but I throw open the challenge

Here is a small section of SQL

Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCTROW Address.*, Towns.Town, Counties.County, Country.Country, IIf([Address1]>"",[Address1]) & IIf([Address2]>"",", " & [Address2]) & IIf([Address3]>"",", " & [Address3]) & IIf([Town]>"",", " & [Town]) & IIf([County]>"",IIf([Town]<>[County],", " & [County])) & IIf([PostCode]>"",", " & Format(Nz([PostCode],"!@@@@ @@@"),Nz([Country]))) & IIf([Country]>"" And [Country]<>[County],", " & [Country]) AS MemAdd
  2.  
I am trying to find all the field names, and successfully find (Ignoring the Address.* which I can expand) Town, County, Country & MemAdd.

What I want to do is find the expression that is used for MemAdd (the convoluted IIf clause)

Before I start parsing the SQL I try to eliminate some of the likely problems using

Expand|Select|Wrap|Line Numbers
  1.     TempStrSQL = Replace(StrSQL, Chr$(10) & Chr$(13), " ")      ' Remove line feeds
  2.     TempStrSQL = Replace(TempStrSQL, Chr$(13), " ")             ' Remove CR
  3.     TempStrSQL = Replace(TempStrSQL, "!", ".")                  ' Possible expanded version
  4.      ' Need to check for commas in strings
  5.     TempStrSQL = Replace(TempStrSQL, " & " & Chr$(34) & ", " & Chr$(34) & " & ", " & " & Chr$(34) & "~ " & Chr$(34) & " & ")
  6.     TempStrSQL = Replace(TempStrSQL, " & " & Chr$(34) & "," & Chr$(34) & " & ", " & " & Chr$(34) & "~" & Chr$(34) & " & ")
  7.  
Where StrSQL is the "Input SQL"

The main fields I find by looking for a comma space combination,
but with all the commas in the IIf clause - no chance.

The end idea is to be able to take any SQL statement and get different sections of it like
The Field List
The FROM Tables section
The WHERE Section
The ORDER BY Section
and as the above question implies, if the Field is a calculated field, the calculation.

If anyone has any ideas, here is what I have got so far
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4.     Public Type SQLInfo
  5.         SQLType As Integer          ' SELECT DISTINCTROW, SELECT DISTINT, SELECT, INSERT, DELETE, UNION
  6.         UNIONStart As Long
  7.         FROMStart As Long
  8.         WHEREStart As Long
  9.         ORDERBYStart As Long
  10.         SEMICOLONStart As Long
  11.         TheQuery As String
  12.         FROMTables As String
  13.         WHEREClause As String
  14.         ORDERBYClause As String
  15.         ASClauses() As String
  16.     End Type
  17.  
  18. Public Function AnalyseSQL(StrSQL As String) As SQLInfo
  19. 'Debug.Print AnalyseSQL(Forms!GetInfo!OldQuery)
  20.  
  21.     Dim SQLInf As SQLInfo
  22.     Dim TempStrSQL As String
  23.     Dim Lngi As Long, Lngj As Long, Lngk As Long
  24.     Dim FROMStart As Long
  25.     Dim WHEREStart As Long
  26.     Dim ORDERBYStart As Long
  27.     Dim SEMICOLONStart As Long
  28.     Dim TheQuery As String
  29.     Dim FROMTables As String
  30.     Dim WHEREClause As String
  31.     Dim ORDERBYClause As String
  32.     Dim ASClauses() As String
  33.     Dim i As Integer
  34.  
  35.     TempStrSQL = Replace(StrSQL, Chr$(10) & Chr$(13), " ")      ' Remove line feeds
  36.     TempStrSQL = Replace(TempStrSQL, Chr$(13), " ")             ' Remove CR
  37.     TempStrSQL = Replace(TempStrSQL, "!", ".")                  ' Possible expanded version
  38.      ' Need to check for commas in strings
  39.     TempStrSQL = Replace(TempStrSQL, " & " & Chr$(34) & ", " & Chr$(34) & " & ", " & " & Chr$(34) & "~ " & Chr$(34) & " & ")
  40.     TempStrSQL = Replace(TempStrSQL, " & " & Chr$(34) & "," & Chr$(34) & " & ", " & " & Chr$(34) & "~" & Chr$(34) & " & ")
  41.  
  42.  
  43.     Lngi = InStr(TempStrSQL, "SELECT DISTINCTROW ")
  44.     If Lngi = 1 Then
  45.         SQLInf.SQLType = 1          ' SELECT DISTINCTROW
  46.         GoTo CheckFROM
  47.     End If
  48.  
  49.     Lngi = InStr(TempStrSQL, "SELECT DISTINCT ")
  50.     If Lngi = 1 Then
  51.         SQLInf.SQLType = 2          ' SELECT DISTINCT
  52.         GoTo CheckFROM
  53.     End If
  54.  
  55.     Lngi = InStr(TempStrSQL, "SELECT ")
  56.     If Lngi = 1 Then
  57.         SQLInf.SQLType = 3          ' SELECT
  58.         GoTo CheckFROM
  59.     End If
  60.  
  61.     Lngi = InStr(TempStrSQL, "INSERT ")
  62.     If Lngi = 1 Then
  63.         SQLInf.SQLType = 4          ' INSERT
  64.         GoTo CheckFROM
  65.     End If
  66.  
  67.     Lngi = InStr(TempStrSQL, "DELETE ")
  68.     If Lngi = 1 Then
  69.         SQLInf.SQLType = 5          ' DELETE
  70.         GoTo CheckFROM
  71.     End If
  72.  
  73.     Lngi = InStr(TempStrSQL, "UPDATE ")
  74.     If Lngi = 1 Then
  75.         SQLInf.SQLType = 6          ' UPDATE
  76.         GoTo CheckFROM
  77.     End If
  78.  
  79.     Lngi = InStr(TempStrSQL, "UNION SELECT ")
  80.     If Lngi > 1 Then
  81.         SQLInf.SQLType = 7          ' UNION
  82.         SQLInf.UNIONStart = Lngi
  83.         GoTo CheckFROM
  84.     End If
  85.  
  86. CheckFROM:
  87.     FROMStart = InStr(TempStrSQL, "FROM ")
  88.     If FROMStart > 1 Then
  89.         SQLInf.FROMStart = FROMStart
  90.         TheQuery = Left(TempStrSQL, FROMStart - 2)
  91.         SQLInf.TheQuery = Trim(TheQuery)
  92.     End If
  93.  
  94.     ' Work out the "Where clause
  95. CheckWHERE:
  96.     WHEREStart = InStr(TempStrSQL, "WHERE ")
  97.     If WHEREStart > 0 Then
  98.         ORDERBYStart = InStr(WHEREStart, TempStrSQL, "ORDER BY ")
  99.     Else
  100.         ORDERBYStart = InStr(TempStrSQL, "ORDER BY")
  101.     End If
  102.     If ORDERBYStart > 0 Then
  103.         SEMICOLONStart = InStr(ORDERBYStart, TempStrSQL, ";")
  104.     Else
  105.         SEMICOLONStart = InStr(TempStrSQL, ";")
  106.     End If
  107.  
  108.     ' Work out the where criteria
  109.     If WHEREStart > 0 Then
  110.         If ORDERBYStart > 0 Then
  111.             WHEREClause = Mid$(TempStrSQL, WHEREStart, ORDERBYStart - WHEREStart)
  112.         Else
  113.             If SEMICOLONStart > 0 Then
  114.                 WHEREClause = Mid$(TempStrSQL, WHEREStart, SEMICOLONStart - WHEREStart)
  115.             Else
  116.                 WHEREClause = Mid$(TempStrSQL, WHEREStart, Len(TempStrSQL) - WHEREStart)
  117.             End If
  118.         End If
  119.         SQLInf.WHEREClause = Trim(WHEREClause)
  120.  
  121.         If ORDERBYStart > 0 Then
  122.             If SEMICOLONStart > 0 Then
  123.                 ORDERBYClause = Mid(TempStrSQL, ORDERBYStart, SEMICOLONStart - ORDERBYStart)
  124.             Else
  125.                 ORDERBYClause = Mid(TempStrSQL, ORDERBYStart)
  126.             End If
  127.             SQLInf.ORDERBYClause = Trim(ORDERBYClause)
  128.         End If
  129.  
  130.         If FROMStart > 0 Then
  131.             If WHEREStart > 0 Then
  132.                 FROMTables = Mid(TempStrSQL, FROMStart, WHEREStart - FROMStart)
  133.             Else
  134.                 If ORDERBYStart > 0 Then
  135.                     FROMTables = Mid(TempStrSQL, FROMStart, WHEREStart - ORDERBYStart)
  136.                 Else
  137.                     If SEMICOLONStart > 0 Then
  138.                         FROMTables = Mid(TempStrSQL, FROMStart, WHEREStart - SEMICOLONStart)
  139.                     End If
  140.                 End If
  141.             End If
  142.             SQLInf.FROMTables = Trim(FROMTables)
  143.         End If
  144.     Else                                    ' No WHERE
  145.         If ORDERBYStart > 0 Then
  146.             If SEMICOLONStart > 0 Then
  147.                 ORDERBYClause = Mid(TempStrSQL, ORDERBYStart, SEMICOLONStart - ORDERBYStart)
  148.             Else
  149.                 ORDERBYClause = Mid(TempStrSQL, ORDERBYStart)
  150.             End If
  151.             SQLInf.ORDERBYClause = Trim(ORDERBYClause)
  152.         End If
  153.  
  154.         If FROMStart > 0 Then
  155.             If WHEREStart > 0 Then
  156.                 FROMTables = Mid(TempStrSQL, FROMStart, WHEREStart - FROMStart)
  157.             Else
  158.                 If ORDERBYStart > 0 Then
  159.                     FROMTables = Mid(TempStrSQL, FROMStart, ORDERBYStart - FROMStart)
  160.                 Else
  161.                     If SEMICOLONStart > 0 Then
  162.                         FROMTables = Mid(TempStrSQL, FROMStart, SEMICOLONStart - FROMStart)
  163.                     End If
  164.                 End If
  165.             End If
  166.             SQLInf.FROMTables = Trim(FROMTables)
  167.         End If
  168.     End If
  169.  
  170.     ' Look for AS in the query (from SELECT to FROM)
  171. NextAS:
  172.     Lngi = InStr(Lngi + 1, TheQuery, " AS ")
  173.     If Lngi > 0 Then                                    ' AS found
  174.         Lngj = InStr(Lngi + 5, TheQuery, ", ")          ' Following Comma
  175.         If Lngj = 0 Then                                ' Last field
  176.             Lngj = InStr(Lngi + 5, TheQuery, " FROM ")
  177.         End If
  178.         Lngk = InStrRev(TheQuery, ", ", Lngi + 5)        ' Preceeding Comma
  179.         If Lngk = 0 Then                                ' First field
  180.             If SQLInf.SQLType = 1 Then                  ' SELECT DISTINCTROW
  181.                 Lngk = Len("SELECT DISTINCTROW ")
  182.             ElseIf SQLInf.SQLType = 2 Then              ' SELECT DISTINCT
  183.                 Lngk = Len("SELECT DISTINCT ")
  184.             ElseIf SQLInf.SQLType > 2 And SQLInf.SQLType < 6 Then ' SELECT, INSERT, FELET, UPDATE
  185.                 Lngk = 7
  186.             End If
  187.         End If
  188.        ' Stop
  189.         i = i + 1
  190.         ReDim Preserve ASClauses(2, i)
  191.         ASClauses(1, i) = Trim(Mid(TheQuery, Lngi + 4, Lngj - Lngi - 4))     ' The combined field name
  192.         ASClauses(2, i) = Trim(Mid(TheQuery, 7, Lngi - Lngk))               ' Separate fields including " AS "
  193.         GoTo NextAS
  194.     End If
  195.  
  196.     AnalyseSQL = SQLInf
  197.  
  198. End Function
  199.  
Sorry it's a touch long winded but for some queries it is working, but not all

Phil
Jul 1 '17 #1

✓ answered by ADezii

  1. If I am not mistaken, there is a much easier way to reference the Expression that generates an Aliased Field via one of the System Tables, namely MSysQueries.
  2. Case in point is the Employees Extended Query in the Northwind Sample Database. Here is it's SQL:
    Expand|Select|Wrap|Line Numbers
    1. SELECT IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),IIf(IsNull([First Name]),[Last Name],[Last Name] & ", " & [First Name])) AS [File As], IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),IIf(IsNull([First Name]),[Last Name],[First Name] & " " & [Last Name])) AS [Employee Name], Employees.*
    2. FROM Employees
    3. ORDER BY IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),IIf(IsNull([First Name]),[Last Name],[Last Name] & ", " & [First Name])), IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),IIf(IsNull([First Name]),[Last Name],[First Name] & " " & [Last Name]));
  3. Assuming we want to return the Expression that generated the Aliased [Employee Name] Field:
    Expand|Select|Wrap|Line Numbers
    1. Dim strASFld As String
    2.  
    3. strASFld = "Employee Name"
    4.  
    5. Debug.Print DLookup("[Expression]", "MSySqueries", "[Name1] = '" & strASFld & "'")
  4. The OUTPUT (Expression that generated the AS Field) would be:
    Expand|Select|Wrap|Line Numbers
    1. IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),IIf(IsNull([First Name]),[Last Name],[First Name] & " " & [Last Name]))
  5. Make use of the System Table.

Share this Question
Share on Google+
13 Replies


ADezii
Expert 5K+
P: 8,619
  1. I may be way off base here, but how about creating a Recordset based on the SQL Statement and then return the Members (Field Names) of the Fields Collection. The following SQL Statement is from the Northwind Sample Database, and the actual Query is named Invoice Data. This Statement is valid. The following Code will return all Fields from the Recordset Object which is based on the SQL Statement (strSQL).
  2. Code Definition:
    Expand|Select|Wrap|Line Numbers
    1. Dim strSQL As String
    2. Dim MyDB As DAO.Database
    3. Dim rst As DAO.Recordset
    4. Dim fld As DAO.Field
    5. Dim intCtr As Integer
    6.  
    7. strSQL = "SELECT Orders.[Order ID], Orders.[Ship Name], Orders.[Ship Address], Orders.[Ship City], Orders.[Ship State/Province], " & _
    8.          "Orders.[Ship ZIP/Postal Code], Orders.[Ship Country/Region], Orders.[Customer ID], Customers.Company AS [Customer Name], " & _
    9.          "Customers.Address, Customers.City, Customers.[State/Province], Customers.[ZIP/Postal Code], Customers.[Country/Region], " & _
    10.          "[Employees Extended].[Employee Name] AS Salesperson, Orders.[Order Date], Orders.[Shipped Date], Shippers.Company AS " & _
    11.          "[Shipper Name], [Order Details].[Product ID], Products.ID AS [Product ID], [Order Details].[Unit Price], " & _
    12.          "[Order Details].Quantity, [Order Details].Discount, CCur(Nz([Unit Price]*[Quantity]*(1-[Discount]),0)/100)*100 AS " & _
    13.          "ExtendedPrice, Orders.[Shipping Fee], Products.[Product Name] " & _
    14.          "FROM (Shippers RIGHT JOIN (Customers RIGHT JOIN (Orders LEFT JOIN [Employees Extended] ON Orders.[Employee ID] = " & _
    15.          "[Employees Extended].ID) ON Customers.ID = Orders.[Customer ID]) ON Shippers.ID = Orders.[Shipper ID]) LEFT JOIN " & _
    16.          "([Order Details] LEFT JOIN Products ON [Order Details].[Product ID] = Products.ID) ON Orders.[Order ID] = " & _
    17.          "[Order Details].[Order ID];"
    18.  
    19. Set MyDB = CurrentDb
    20. Set rst = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
    21.  
    22. Debug.Print "Counter", "Fields Used in Query"
    23. Debug.Print String(50, "-")
    24.  
    25. For Each fld In rst.Fields
    26.   intCtr = intCtr + 1
    27.     Debug.Print Format$(intCtr, "000"), fld.Name
    28. Next
    29.  
    30. Debug.Print String(50, "-")
    31.  
    32. rst.Close
    33. Set rst = Nothing
    34.  
  3. OUTPUT:
    Expand|Select|Wrap|Line Numbers
    1. Counter       Fields Used in Query
    2. --------------------------------------------------
    3. 001           Order ID
    4. 002           Ship Name
    5. 003           Ship Address
    6. 004           Ship City
    7. 005           Ship State/Province
    8. 006           Ship ZIP/Postal Code
    9. 007           Ship Country/Region
    10. 008           Customer ID
    11. 009           Customer Name
    12. 010           Address
    13. 011           City
    14. 012           State/Province
    15. 013           ZIP/Postal Code
    16. 014           Country/Region
    17. 015           Salesperson
    18. 016           Order Date
    19. 017           Shipped Date
    20. 018           Shipper Name
    21. 019           Order Details.Product ID
    22. 020           Product ID
    23. 021           Unit Price
    24. 022           Quantity
    25. 023           Discount
    26. 024           ExtendedPrice
    27. 025           Shipping Fee
    28. 026           Product Name
    29. --------------------------------------------------
    30.  
Jul 1 '17 #2

ADezii
Expert 5K+
P: 8,619
I failed to add that this would only be a part of the puzzle, namely the Field List.
Jul 2 '17 #3

PhilOfWalton
Expert 100+
P: 1,430
Thanks for your input. I already load the fields into a table, but I think I might gave a go at using instr to find the field name in the SQL, see if it is immediately preceded by " AS " and if so use InstrRev to find the previous field name. If that succeeds, assume everything in between is the expression (calculation or concatination of fields etc.) that is source of the AS field.

So in the above North Wind example, find [Customer Name] in the table, scan for it in the SQL, establish there is an " AS " in front if it, find the preceding field ([Customer ID]) in the table, scan backward for that in the SQL and assume that everything between (Customers.Company) is the source of Customer Name.

Does that look logical?

But that's for tomorrow.

Thanks again

Phil
Jul 2 '17 #4

ADezii
Expert 5K+
P: 8,619
  1. Initially, I took a rather convoluted approach that processes SQL Statements, in this case the one displayed below, and produces an Output Table with the results.
  2. SQL:
    Expand|Select|Wrap|Line Numbers
    1. strSQL = "SELECT Employees.[Last Name], Employees.[First Name], Employees.[E-mail Address], Employees.Company, " & _
    2.          "Employees.[Job Title] FROM Employees WHERE (((Employees.[Job Title]) Like 'Sales*')) " & _
    3.          "ORDER BY Employees.[Last Name], Employees.[First Name];"
    4.  
  3. Results Table:
    Expand|Select|Wrap|Line Numbers
    1. ID    ObjType         ObjName              Context
    2. 291    Field          Last Name            Display
    3. 292    Table          Employees            FROM Clause
    4. 293    Table/Field    Employees.Last Name  PRIMARY ORDER BY Clause
    5. 294    Field          First Name           Display
    6. 295    Table          Employees            FROM Clause
    7. 296    Table/Field    Employees.First Name SECONDARY ORDER BY Clause
    8. 297    Field          E-mail Address       Display
    9. 298    Table          Employees            FROM Clause
    10. 299    Field          Company              Display
    11. 300    Table          Employees            FROM Clause
    12. 301    Field          Job Title            Display
    13. 302    Table          Employees            FROM Clause
    14. 303    Table/Field    Employees.Job Title  WHERE Clause
  4. Sorry, did not have the time to properly format the Results.
Jul 2 '17 #5

NeoPa
Expert Mod 15k+
P: 31,398
Hi Phil.

If I may offer some advice, without necessarily leading the way, I'd suggest you take a different approach and parse the SQL itself using your own routine. We all have a basic understanding of what keywords are available and what punctuation does what and where. That would be more complicated in a way, but would work from the ground-up, as it were. Rather than trying to recognise items generally, which can easily be tripped up by unexpected SQL.

Clearly, your code would have to understand various modes and would behave differently when it came across a parenthesis or bracket when inside a string than it would outside of one. String literals are the killer here it seems to me. When you start using VBA code, and even SQL code, to build up SQL strings then it's not uncommon to have valid SQL structures within your string literals.

Don't get me wrong. I'm not invested in your taking the approach I suggest. However, I thought I'd throw it out there for your consideration.

Best of luck however you choose to proceed from here.
Jul 2 '17 #6

jforbes
Expert 100+
P: 1,107
This looked like fun, so I created a function that might work for you. The idea is to pass it a list of SQL fields and it will return a Pipe Delimited string of just the fields that contain AS, while taking into account the nesting that happens with Parenthesis.

Here is the code:
Expand|Select|Wrap|Line Numbers
  1. Public Function getDelimitedASFields(ByRef sSQL As String) As String
  2.  
  3.     Dim iCount As Integer
  4.     Dim iStart As Integer
  5.     Dim INestCount As Integer
  6.     Dim iFound As Integer
  7.     Dim iAsCount As Integer
  8.     Dim iSQLLen As Integer
  9.     Dim iLastFound As Integer
  10.     Dim sResult As String
  11.  
  12.     iSQLLen = Len(sSQL)
  13.  
  14.     ' Loop through the Fields and grab the fields with he AS Keyword and place them in a Pipe delimited String
  15.     For iCount = 1 To iSQLLen
  16.         Select Case Mid(sSQL, iCount, 1)
  17.  
  18.             ' Commas signify a new field unless nested, like in a function
  19.             Case ","
  20.                 If INestCount = 0 Then
  21.                     If iAsCount = 4 Then
  22.                         iFound = iFound + 1
  23.                     Else
  24.                         iStart = iCount
  25.                     End If
  26.                 End If
  27.  
  28.  
  29.             ' flag when in a nested function so that commas are ignored
  30.             Case "("
  31.                 INestCount = INestCount + 1
  32.                 If iAsCount <> 4 Then iAsCount = 0
  33.             Case ")"
  34.                 INestCount = INestCount - 1
  35.                 If iAsCount <> 4 Then iAsCount = 0
  36.  
  37.  
  38.             ' look for the "AS" Keyword
  39.             Case " "
  40.                 If iAsCount = 0 Or iAsCount = 3 Then iAsCount = iAsCount + 1
  41.             Case "A"
  42.                 If iAsCount = 1 Then iAsCount = iAsCount + 1
  43.             Case "S"
  44.                 If iAsCount = 2 Then iAsCount = iAsCount + 1
  45.  
  46.  
  47.             ' Reset the "AS" Keyword counter as needed
  48.             Case Else
  49.                 If iAsCount <> 4 Then iAsCount = 0
  50.         End Select
  51.  
  52.  
  53.  
  54.         ' See if an "AS" was found, and we found a non-nested comma, replace the comma with a pipe
  55.         If iAsCount = 4 And (iLastFound <> iFound Or iCount = iSQLLen) Then
  56.             sResult = sResult & Trim(Mid(sSQL, (iStart + 1), (iCount - iStart) + (iLastFound <> iFound))) & "|"
  57.             Debug.Print sResult
  58.             iAsCount = 0
  59.             iStart = iCount
  60.             iLastFound = iFound
  61.         End If
  62.     Next iCount
  63.  
  64.     If Len(sResult) > 0 Then sResult = Left(sResult, Len(sResult) - 1)
  65.  
  66.  
  67.     ' Return the results
  68.     getDelimitedASFields = sResult
  69.  
  70. End Function
Using this as a parameter:"iif(true, 1, 2) AS v, a as b, Employees.[Last Name], Employees.[First Name], Employees.[E-mail Address], Employees.Company,Employees.[Job Title] , b as c, c, d as e, Address.*, Towns.Town, Counties.County, Country.Country, Iif([Address1]>"",[Address1]) & Iif([Address2]>"",", " & [Address2]) & Iif([Address3]>"",", " & [Address3]) & Iif([Town]>"",", " & [Town]) & Iif([County]>"",Iif([Town]<>[County],", " & [County])) & Iif([PostCode]>"",", " & Format(Nz([PostCode],"!@@@@ @@@"),Nz([Country]))) & Iif([Country]>"" And [Country]<>[County],", " & [Country]) AS MemAdd, Employees.[Last Name], Employees.[First Name], Employees.[E-mail Address], Employees.Company,Employees.[Job Title]"

The function returns: "iif(true, 1, 2) AS v|a as b|b as c|d as e|Iif([Address1]>"",[Address1]) & Iif([Address2]>"",", " & [Address2]) & Iif([Address3]>"",", " & [Address3]) & Iif([Town]>"",", " & [Town]) & Iif([County]>"",Iif([Town]<>[County],", " & [County])) & Iif([PostCode]>"",", " & Format(Nz([PostCode],"!@@@@ @@@"),Nz([Country]))) & Iif([Country]>"" And [Country]<>[County],", " & [Country]) AS MemAdd"

You can then split on the Pipe.
Jul 3 '17 #7

ADezii
Expert 5K+
P: 8,619
  1. If I am not mistaken, there is a much easier way to reference the Expression that generates an Aliased Field via one of the System Tables, namely MSysQueries.
  2. Case in point is the Employees Extended Query in the Northwind Sample Database. Here is it's SQL:
    Expand|Select|Wrap|Line Numbers
    1. SELECT IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),IIf(IsNull([First Name]),[Last Name],[Last Name] & ", " & [First Name])) AS [File As], IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),IIf(IsNull([First Name]),[Last Name],[First Name] & " " & [Last Name])) AS [Employee Name], Employees.*
    2. FROM Employees
    3. ORDER BY IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),IIf(IsNull([First Name]),[Last Name],[Last Name] & ", " & [First Name])), IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),IIf(IsNull([First Name]),[Last Name],[First Name] & " " & [Last Name]));
  3. Assuming we want to return the Expression that generated the Aliased [Employee Name] Field:
    Expand|Select|Wrap|Line Numbers
    1. Dim strASFld As String
    2.  
    3. strASFld = "Employee Name"
    4.  
    5. Debug.Print DLookup("[Expression]", "MSySqueries", "[Name1] = '" & strASFld & "'")
  4. The OUTPUT (Expression that generated the AS Field) would be:
    Expand|Select|Wrap|Line Numbers
    1. IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),IIf(IsNull([First Name]),[Last Name],[First Name] & " " & [Last Name]))
  5. Make use of the System Table.
Jul 3 '17 #8

PhilOfWalton
Expert 100+
P: 1,430
Thanks both for replying. Using MsysQueries looks the way to go.

I will do further playing in the next few days and report back.

Interestingly, and probably logically you can't create a query on MsysQueries. Suppose it would be a circular reference.

Thanks again,

Phil
Jul 3 '17 #9

ADezii
Expert 5K+
P: 8,619
Not to put a Monkey Wrench into the process, but you can have Multiple Aliased Field Names all named exactly the same, such as: [Employee Name]. What would be different would be the [ObjectId] Field in MSysQueries. You can check this [ObjectId] against the [Id] Field in MSysObjects. The [Name] Field in MSysObjects for the [Id] is the Name of the Query.
Jul 3 '17 #10

ADezii
Expert 5K+
P: 8,619
Interestingly, and probably logically you can't create a query on MsysQueries.
I'm pretty sure that you can.
  1. The following Query
    Expand|Select|Wrap|Line Numbers
    1. SELECT MSysQueries.Attribute, MSysObjects.Name AS [Query Name], MSysQueries.Name1 AS [AS Field], MSysQueries.Expression, MSysQueries.ObjectId
    2. FROM MSysObjects INNER JOIN MSysQueries ON MSysObjects.Id = MSysQueries.ObjectId
    3. WHERE (((MSysQueries.Name1)="Employee Name"));
  2. will produce the following OUTPUT
    Expand|Select|Wrap|Line Numbers
    1. Attribute     Query Name            AS Field         Expression                                          ObjectId (after Expression)
    2. 6             Employees Extended    Employee Name    "IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),IIf(IsNull([First Name]),[Last Name],[First Name] & "" "" & [Last Name]))"    -2147483636
    3.  
Jul 4 '17 #11

PhilOfWalton
Expert 100+
P: 1,430
How odd

I did it before, even more simply

Expand|Select|Wrap|Line Numbers
  1. SELECT MSysQueries.*
  2. FROM MSysQueries;
  3.  
and got a blank sheet. Now running perfectly.

Anyway, a lot to redesign using MSysQueries so I will get back in a few days.

Thanks

Phil
Jul 4 '17 #12

ADezii
Expert 5K+
P: 8,619
Good Luck, Phil. I'm sure that you are aware but you can Link MSysQueries with MSysObjects (as illustrated in Post# 11) in the following manner:
Expand|Select|Wrap|Line Numbers
  1. MSysObjects.Id = MSysQueries.ObjectId
with [ObjectId], [Attribute], and [Order] in MSysQueries comprise a Multi-Field Primary Key.
Jul 4 '17 #13

ADezii
Expert 5K+
P: 8,619
  1. I did a little research on the MSysQueries System Table and came up with what I feel is invaluable information, especially given your Post# 9. I consolidated as much info as I could and placed it in an Access Table. To verify much of this information, I created the following Query linking the MSysObjects and MSysQueries Tables and filtering for Queries only (Type=5) and Non-Temporary and Queries not involved in Row or Record Sources (Not Like ~sq_*). I found it extremely interesting and I hope you will also especially since you will use this Table in your Current Project. If you want the actual Table, I will be happy to Attach a DB containing it.
    Expand|Select|Wrap|Line Numbers
    1. SELECT MSysQueries.ObjectId, MSysObjects.Type, MSysObjects.Name, MSysQueries.Attribute, MSysQueries.Expression, MSysQueries.Flag, MSysQueries.Name1, MSysQueries.Name2
    2. FROM MSysObjects INNER JOIN MSysQueries ON MSysObjects.Id = MSysQueries.ObjectId
    3. WHERE (((MSysObjects.Type)=5) AND ((MSysObjects.Name) Not Like "~sq*"));
  2. tblMSysObjects:
    Expand|Select|Wrap|Line Numbers
    1. --------------- qryMSysObjects---------------
    2.  
    3. Attribute    Flag Value    COMMENT2
    4. 0        Indicates the Beginning of the Query Definition
    5. 255        Indicates the End of the Query Definition
    6. 1        Attribute 1 indicates the Type of Query
    7. 1    1    SELECT Query
    8. 1    2    SELECT INTO or MAKE TABLE Query
    9. 1    3    INSERT QUERY; [Name1] will have the Name of the Table to insert into
    10. 1    4    UPDATE Query
    11. 1    5    DELETE Query
    12. 1    6    CROSSTAB Query (TRANSFORM)
    13. 1    7    CREATE TABLE Query
    14. 1    8    DATE/TIME Parameter
    15. 1    9    UNION Query; Text of SQL in [Expression]
    16. 2        Rows with Attribute 2 (can be multiple) are each of the Formal Parameters of the Query as
    17.         defined in the Query Parameters Dialog Box. The Flag Column indicates the Data Type
    18.         (i.e. '10' for dbText) and the [Name1] Column indicates the Name of the Parameter. If there
    19.         are no Rows with Attribute 2, then the Query does not have 'Formal' Parameters.
    20. 3        Attribute 3 indicates the presence of UNION or DISTINCT Keywords.
    21. 3    0    Nothing special.
    22. 3    1    UNION ALL
    23. 3    2    SELECT DISTINCT
    24. 3    3    UNION
    25. 3    8    SELECT DINTINCTROW
    26. 3    9    Queries on Master Fields and Child Fields
    27. 3    12    WITH OWNER AACCESS OPTION
    28. 3    16    SELECT TOP; the number as in TOP 10 will be in the [Name1] Field
    29. 3    24    SELECT TOP
    30. 3    48    SELECT TOP [Name1] PERCENT; PERCENT IN [Name1]
    31. 4        Attribute 4 indicates if the Query comes from an External Database. [Name1] will contain
    32.         the Source if Attribute 4 exists.
    33. 5        Attribute 5 (could be multiple) indicates each Table found in a Query. If the Query is a
    34.         UNION Query, the [Expression] Field has a split on the UNION Keyword and the [Name2]
    35.         Field has a System-generated Table Alias. For all other Tables in a Query, [Name1] is the
    36.         Name of the Table and [Name2] is the Alias, if there is one.
    37. 6        Rows with Attribute 6 (could be multiple) indicated each single Field or Expression in the Query. If there is no
    38.         Attribute 6 for the Query, the behavior assumed is that all Fields are included. The [Expression]
    39.         Field contains each Field Expression or name, and [Name1] contains the Field Alias, if there is
    40.         one.
    41. 6    0    Value of the Field or Expression.
    42. 6    1    The Field is a Column Heading in a CROSSTAB Query.
    43. 6    2    The Field is a Row Heading in a CROSSTAB Query.
    44. 7        Rows with Attribute 7 (could be multiple) indicate each single Join 'ON' Expression. The
    45.         [Expression] Field contains the actual Join Expression.
    46. 7    1    [Name1] INNER JOIN [Name2] on [Expression]
    47. 7    2    [Name1] LEFT JOIN [Name2] on [Expression]
    48. 7    3    [Name1] RIGHT JOIN [Name2] on [Expression]
    49. 8        The Row with Attribute 8 contains the whole WHERE Claus in the [Expression] Field. If there
    50.         is no WHERE Clause, Attribute 8 is omitted from the Query.
    51. 9        The Rows with Attribute 9 (could be multiple) indicate each single GROUP BY Expression in the
    52.         GROUP BY Clause of the Query. The [Expression] Field contains each GROUP BY Expression.
    53. 9    0    Value of the Field or Expression.
    54. 9    1    The Field is a Column Heading in a CROSSTAB Query.
    55. 9    2    The Field is a Row Heading in a CROSSTAB Query.
    56. 10        HAVING [Expression]
    57. 11        The Rows with Attribute 11 (could be multiple) indicate each single ORDER BY Expression in
    58.         the ORDER BY Clause of the Query. The [Expression] Field contains each ORDER BY
    59.         Expression. [Name1] has a 'D' or 'd' to indicated that the Sort is done in Descending Order.
    60.  
    61.  
  3. The [Attribute] and [Flag] Fields in MSysQueries taken together clearly appear to define virtually all of a Query's Properties.
P.S. - Please forgive the sloppy formatting, ran out of time and had to go!
Jul 4 '17 #14

Post your reply

Sign in to post your reply or Sign up for a free account.