Obtaining SQL from a Query (1 Viewer)

[Matt Greatorex]

I have a number of reports that have as their datasources either a saved query (say, 'Query1') or an actual SQL statement (say, 'SELECT IDNumber FROM Table1 ORDER BY IDNumber;'). I want to create an Excel spreadsheet based on each report, but having applied one or more additional filters to the data.

I'm currently trying to achieve this as follows:
1) Open the report
2) Find the report's data source
3) Split the data source into the part before where the filter would be inserted (e.g. 'SELECT IDNUmber FROM Table1') and the part after where the filter would be inserted (e.g. 'ORDER BY IDNumber').
4) Insert the filter (e.g. 'WHERE IDNumber > 1000)
5) Use this as the basis for the spreadsheet.

This works fine for those reports having SQL as theie data source, but doesn't work for those having a named query. In the latter cases, I end up trying to split out the word 'Query1' (which fails, for obvious reasons).

Is there some way, using VBA, that I can find out the actual SQL from a query, given its name?

As always, thanks in advance for any answers.

 

[Bodisathva]

Dim qdf as QueryDef
dim strSQL as String

Set qdf = CurrentDb.QueryDefs("YOURQUERYNAME")
strSQL =  qdf.SQL

 

[Matt Greatorex]

Perfect!

I knew there'd be some easy way to accomplish it.

Thanks a lot.