Changing where clause for a report based on a saved query

[ddog171]

I have a report based on the following saved Query:

SELECT Crime_Table.Offense, Count(*) AS [Number of Crimes]
FROM Crime_Table
GROUP BY Crime_Table.Offense;

What I want to do is be able to pass in a where clause that is generated from a form.

Is this possible? Would I need to change the recordsource dynamically? if so how do I do this?

Any help would be greatly appreciated.

 

[Pat Hartman]

If your where clause is dynamic, you will need to build the clause in code. You can then use the clause (without the "where") as the Where argument of the OpenReport method.

 

[ddog171]

I have that

I have the dynamic where clause, and I am passing it into the report like this:

DoCmd.OpenReport "Crime Numbers", acViewPreview, , WhereString

(Where string is a string that contains the where clause, without the "WHERE")

The problem is when I try and run the report from the form it asks me for the value in the where clause.

The saved query is as follows:

SELECT Crime_Table.Offense, Count(*) AS [Number of Crimes]
FROM Crime_Table
GROUP BY Crime_Table.Offense;


My Dynamic where clause is as follows:

Subdivision = "Chambers Glen"

When I run the report I get a pop up box asking to input the value for subdivision.

Any help would be appreciated.

 

[Pat Hartman]

Building a string that requires embedded quotes can be a problem.

WhereString = "Subdivision = " & """" & Me.Subdivision & """"

The """" will each embed a single " into the string so it will actually look like what you want. I'm guessing that if you stoped the code at the point of the Open method and looked at the string, it would not contain quotes around the subdivision name.