Passing SQL Where condition to a query

[josephbupe]

Hi,


I would like to know how to pass a WHERE condition behind my search button on a form to a query so that I can open reports based only on the search results from the Where condition.


This is my code:

If Len(strWhere) > 0 Then
    If Nz(DCount("*", "qryEvents", Left(strWhere, Len(strWhere) - 0)), 0) > 0 Then
       strWhere = "WHERE " & Left(strWhere, Len(strWhere) - 0)
       strSQL = "SELECT * FROM qryEvents " & strWhere & ";"

I thought this would work, but it doesnt:

strWhere = "qryEvents_Search"

 

[plog]

You are playing an unnecessary game of telephone: from the form to the query to the report. Cut out the middle man and simply open the report using a filter based on the data on the form:

https://msdn.microsoft.com/en-us/vba/access-vba/articles/docmd-openreport-method-access

 

[josephbupe]

You are playing an unnecessary game of telephone: from the form to the query to the report. Cut out the middle man and simply open the report using a filter based on the data on the form:

https://msdn.microsoft.com/en-us/vba/access-vba/articles/docmd-openreport-method-access

I could do that but actually I dont want to ever open a report, I only want to export that report to a rich-text file:

DoCmd.OutputTo acReport, "rptEvents", "RichTextFormat(*.rtf)", "C:\SARPCCO accdb\SARPCCO_Report.rtf"

But first I need to create a query from the Where condition to base the report on, maybe with querydef but I have no idea; I have never used them before.

 

[1268]

What plog daid. And remember if you want to set a variable equal to something you have to tell the progran how to get it. Ie dlookup or recordset.





Sent from my SM-G950U using Tapatalk

 

[plog]

I would open the report with the filter (DoCmd.OpenReport), then output it (DoCmd.OutputTo) and finally close it (DoCmd.CloseReport).

 

[josephbupe]

okay, I think I want to create a a query def based on the Where statement I earlier created. Something like this:

If Len(strWhere) > 0 Then     If Nz(DCount("*", "qryEvents", Left(strWhere, Len(strWhere) - 0)), 0) > 0 Then        strWhere = "WHERE " & Left(strWhere, Len(strWhere) - 0)        strSQL = "SELECT * FROM qryEvents " & strWhere & ";"

Query def:

Dim qdf As QueryDef

strSql = "SELECT * FROM qryEvents WHERE strWhere"

'Delete the query if it already exists
DoCmd.DeleteObject acQuery, "qryEvents_Search"

Set qdf = CurrentDb.CreateQueryDef("qryEvents_Search", strWhere)
'DoCmd.OpenQuery qdf.Name
DoCmd.OpenQuery "qryEvents_Search"

'release memory
qdf.Close
Set qdf = Nothing

However, I dont think the query qryEvents_Search is being created since I cannot see it.


Any ideas please.

 

[MajP]

If you open the filtered report (even hidden) then export it will work. The other technique like you said is to modify the query def. The easiest way to do this is with two queries so you never have to write a long sql string. Query one is the heavy lifting with all your joins and sort order. Assume it is called "qryOne". Then create a second query called "qryOneReport". It is the recordsource of your report. It is simply "Select * from qryOne" or whatever you want. If the sql is simple you could do this with one query def.

Now you can modify this before doing your export.

dim qdf as dao.querydef
dim strSql as string
dim strWhere as string
set qdf = currentdb.querydefs("qryOneReport")
'now your code get your new strsql here
'If Len(strWhere) > 0 Then
'    If Nz(DCount("*", "qryEvents", Left(strWhere, Len(strWhere) - 0)), 0) > 0 Then
'       strWhere = "WHERE " & Left(strWhere, Len(strWhere) - 0)
'       strSQL = "SELECT * FROM qryEvents " & strWhere & ";
'strSQL = "SELECT * FROM qryEvents " & strWhere
'....
'now modify the sql of the query def
qdf.sql = strSql
now open report

 

[MajP]

Disregard my last you beat me to it. I believe you need to use the append method of the querydefs collection to append the query in order to see it.

 

[MajP]

You also may need to refresh the database window in your code
Application.RefreshDatabaseWindow

 

[josephbupe]

Okay, this is working just fine.

Dim strNewSql As String
Dim qdf As QueryDef

       strSql = "SELECT * FROM qryEvents " & strWhere & ";"

'Delete the query if it already exists
DoCmd.DeleteObject acQuery, "qryEvents_Search"

strNewSql = "SELECT * FROM qryEvents " & strWhere
Set qdf = CurrentDb.CreateQueryDef("qryEvents_Search", strNewSql)
'Release memory
qdf.Close
Set qdf = Nothing

Thank you so much.