Display SQL statement into a text field on a report 2

[vanuta]

I have a report that is coming from a query called "PO Query". In the query there is multiple criteria:

PODte Between #1/1/99# and #12/31/99# and PoNumber Between 100 and 1200051

I want to display the where clause of the sql statement in a textbox on my report. Is there an easy way to do this????

Any help would be appreciated!

 

[Isadore]

Vanuta: I have seem several examples of printing the SQL of an underlying query...you'll have to truncate it at the WHERE statement...but this is a good question as many reports, graphs, etc... can be highlighted by supplying in a text box the underlying SQL query text....hopefully someone will kick this one over...

 

[demoman]

You can extract the SQL statement from the QuerydDef:

Dim qdf As DAO.QueryDef
Dim dbs as Database
Dim strSQL as string
Set dbs = CurrentDB
Set qdf = dbs.QueryDefs("MyQuery&quot
strSQL = qdf.SQL

Now, you are going to get the entire SQL statement, so you may want to locate WHERE in the string and extract everyting to the right of it.

Good luck

 

[Isadore]

Demonan: A star well deserved. How would you go about writing a "locate" line of code that would find "WHERE" and print everything to the right of it.

 

[demoman]

Using the example above:

Dim strGet As String

strGet = Mid$(strSQL, (InStr(1, Trim$(strSQL), "where", 1) + 6), Len(Trim$(strSQL)) - (InStr(1, Trim$(strSQL), "where", 1) - 7))

 

[Isadore]

Demoman. If you're not netting > 6 figs, I'd look for another job. Thanks.