Retrieve SQL code with dynamic filters (1 Viewer)

[LGlg0123]

Hi All,
unfortunately an Office 365 update no longer allows me to export Reports to Excel from the Ribbon.
Waiting for a next update that fixes the problem (I work for a large Company and I have to wait quite a while before the update will be installed in our workstations) I'm trying to solve this issue writing some code that export the reports to Excel directly inside my App.

Do.cmd Output to does not work.
However I already have an idea on how to fix it. The only problem is that I need the SQL that builds the Reports, once I have that I can store the query in a string sqlStr and the rest of the code I have already prepared should do it all.

The queries that build the reports have dynamic filters that users can choose from a Form. How do i know what users will choose? What I need is to have the SQL code of the query after the report is run. Is there a way to do this?
Thanks for your help.

 

[arnelgp]

you can add code to the Report's Open event to build the Recordsource of the form:

Private sub Report_Open(Cancel As Integer)
Dim sql as string
sql= "select * from yourTable where [NumericFieldName] = " & Forms!FormName!txtNumeric & " And " & _
"[TextFieldName] = '" & Forms!FormName!txtString & "'"
Me.Recordsource = sql
End Sub

 

[Uncle Gizmo]

The queries that build the reports have dynamic filters that users can choose from a Form. How do i know what users will choose? What I need is to have the SQL code of the query after the report is run. Is there a way to do this?

You mention queries. Queries can be built as text strings.

This means you can use VBA to construct a query as a text string, in any anyway you desire, for example, with parameters taken from the form.

I built a Search Form using this technique and I blogged about the process on my website here:-

Building Search Criteria - Nifty Access

Want to build yourself a Search Form? Want to build yourself a Search Form? Then You are in the right place! This is no Ordinary Search Form, this Search Form incorporates some Object Orientated programming techniques. The process I take you through is exactly the same process as I took when I...

www.niftyaccess.com

 

[LGlg0123]

you can add code to the Report's Open event to build the Recordsource of the form:

Private sub Report_Open(Cancel As Integer)
Dim sql as string
sql= "select * from yourTable where [NumericFieldName] = " & Forms!FormName!txtNumeric & " And " & _
"[TextFieldName] = '" & Forms!FormName!txtString & "'"
Me.Recordsource = sql
End Sub

Thanks arnelgp!

This works but only if I know exactly what filter the users will choose.
For example if I add 2 filters in the SQL code text I know the users will choose: Forms!CmbCities and Form!CmbStatus everything goes well...if I add a third Filter (the one that users will not choose) Forms!CmbTasks..the query will show no results because Forms!CmbTasks will return null/blank value...
The same is when users will choose Cities and Tasks and not Status. No results.
If I switch from AND to OR...the query will show all the records of the table.
How can i get out of this loop?
Thanks

 

[arnelgp]

if CmbTasks is Null/Blank, what result would you like to show? show only Null/Blank on that field
or show All records?

if you want to show All records when CmbTasks is Null/Blank:


sql= "select * from yourTable where " & _
"[city] = IIf(IsNull(Forms!yourForm!cmbCities), [city], Forms!yourForm!cmbCities) And " & _
"[status ] = IIf(IsNull(Forms!yourForm!cmbStatus), [status], Forms!yourForm!cmbStatus) And " & _
"[tasks] = IIf(IsNull(Forms!yourForm!cmbTasks), [tasks], Forms!yourForm!cmbStatus)"

 

[Gasman]

Thanks arnelgp!

This works but only if I know exactly what filter the users will choose.
For example if I add 2 filters in the SQL code text I know the users will choose: Forms!CmbCities and Form!CmbStatus everything goes well...if I add a third Filter (the one that users will not choose) Forms!CmbTasks..the query will show no results because Forms!CmbTasks will return null/blank value...
The same is when users will choose Cities and Tasks and not Status. No results.
If I switch from AND to OR...the query will show all the records of the table.
How can i get out of this loop?
Thanks

How are these filters set? hardcoded in the query with forms references or built dynamically with VBA?

 

[LGlg0123]

if CmbTasks is Null/Blank, what result would you like to show? show only Null/Blank on that field
or show All records?

if you want to show All records when CmbTasks is Null/Blank:


sql= "select * from yourTable where " & _
"[city] = IIf(IsNull(Forms!yourForm!cmbCities), [city], Forms!yourForm!cmbCities) And " & _
"[status ] = IIf(IsNull(Forms!yourForm!cmbStatus), [status], Forms!yourForm!cmbStatus) And " & _
"[tasks] = IIf(IsNull(Forms!yourForm!cmbTasks), [tasks], Forms!yourForm!cmbStatus)"

I tried with your code in your last reply. Nothing has changed. If I select City and status and NO Tasks, the query shows No Resuts.
If I select all the 3 filters, the query returns all the results correctly.

 

[arnelgp]

here i simply create a Query and use it as Recordsource of the Report.

 

[LGlg0123]

How are these filters set? hardcoded in the query with forms references or built dynamically with VBA?

The filters are in a Filter Form (some combos and texts). After selecting filters, when the user run the Report, VBA takes the results from filters, then store everything in a strFilter string and then pass it as Parameter in a Docmd OpenReport.

Reports works well.

 

[arnelgp]

What I need is to have the SQL code of the query after the report is run. Is there a way to do this?

see the Query in the demo i provided.

 

[LGlg0123]

here i simply create a Query and use it as Recordsource of the Report.

Good News! Passing the combo boxes at the IIF/Len test...now it works!
Thanks a lot for your help.

 

[Gasman]

The filters are in a Filter Form (some combos and texts). After selecting filters, when the user run the Report, VBA takes the results from filters, then store everything in a strFilter string and then pass it as Parameter in a Docmd OpenReport.

Reports works well.

So you have everything you need right there?
The sql of the query and the criteria at that time?