Extract portion of SQL from an Access 97 Query? 1

[ordendelfai]

This is a strange request, but I need to find a way to extract the top initial SQL code (text) up to the "FROM" portion of the statement, then extract the FROM SQL until the WHERE statement, and then extract the WHERE SQL text until the ; (cannot include the

For example:

qry_SqlExtract

[COLOR=green]Select Distinct table.field1, 
table.field2[/Color]
[Blue]FROM table[/Blue]
[Purple]Where table.field1 = "C"[/Purple]
;

I am familiar with the MID and LEN functions, but I am not sure how to write code to search a query's SQL and only up to a certain word in the SQL...

If you can help, would really appreciate it ;-)





~Joel
As common courtesy and to help other members who might find your thread helpful in the future, please respond if the advice given resolved your issue.

 

[RoyVidar]

Something like this (quick and dirty, and not tested, of course;-))

[tt]dim qdf as dao.querydef
dim strSql as string
dim lngl as long
set gdf=currentdb.querydef("nameofquery")
strSql=qdf.sql
lngl=instr(strsql,"From ")
debug.print mid$(strsql,1,lngl-1)
debug.pring mid$(strsql,lngl,instr(strsql,"Where ")-1)
lngl=instr(strsql,"Where ")
debug.print mid$(strsql,lngl,len(mid$(strsql,lngl))-3)[/tt]

- does not take into consideration the possibility of more than one from/where clause (subqueries) or no where clause

Roy-Vidar

 

[ordendelfai]

Thanks Roy, this puts me in the right direction. I will put in a lot more logic to handle Union, actin, and other oddball queries. I'll report back after trying it out Thanks ;-)

~Joel
As common courtesy and to help other members who might find your thread helpful in the future, please respond if the advice given resolved your issue.

 

[ordendelfai]

Hi Roy, thanks for the tip again, after testing, I fixed a couple of bugs, and put together the below code to make it all work:

Sub SQL_ExtractTest()
Dim Qdf As dao.QueryDef
Dim StrSql As String
Dim TotalSize As Long
Dim FromPosition As Long
Dim WherePosition As Long
Dim OrderPosition As Long
Dim EndPosition As Long

Set Qdf = CurrentDb.QueryDefs("qry_FetchData")
StrSql = Qdf.SQL

TotalSize = Len(StrSql)
FromPosition = InStr(StrSql, "From ")
WherePosition = InStr(StrSql, "Where ")
OrderPosition = InStr(StrSql, "Order By ")
EndPosition = InStr(StrSql, ";")

MsgBox (Mid$(StrSql, 1, FromPosition - 1)) '

MsgBox (Mid$(StrSql, FromPosition, WherePosition - FromPosition))

MsgBox (Mid$(StrSql, WherePosition, OrderPosition - WherePosition))

MsgBox (Mid$(StrSql, OrderPosition, EndPosition - OrderPosition))

Set Qdf = Nothing

End Sub

~Joel
As common courtesy and to help other members who might find your thread helpful in the future, please respond if the advice given resolved your issue.