Not equal to question

[jneirinckx]

Good afternoon,
I have a form where I enter criteria that a query pulls from and then runs a report. The user can pick from a combo box which "codes" to filter on including an "*" for all codes which all works fine. I have been asked to have an option to include all codes except the one they choose . ie <> "a".

I am struggling on how to accomplish this through a combo box selection.

Appreciate any ides you might have.

Thanks
Jerry

 

[PNGBill]

If your combo box was a value list and the selected data used in a Select Case vba statement to run the approriate sql.

Use the AfterUpdate event to read the form control (combobox), run appropriate sql and open what ever (subform, report...)

 

[Pat Hartman]

You don't need to run the query prior to running the report. The query needs to be the RecordSource for the report. There are several ways to solve your problem. The easiest is to leave the report's RecordSource query without any selection criteria at all. Then, using the DoCmd.OpenReport method, provide a "where" argument with the criteria from the form.

If me.SelectEqual = True Then
     strWhere = "SomeField = " & Me.cboSelection
Else
    strWhere = "SomeField <> " & Me.cboSelection
End If
docmd.OpenReport "YourReportName",acViewPreview,,strWhere

 

[jneirinckx]

Thank you for your replys.
I do need to use the query as there are also other fields and calculated fields used in the report.
I try to make it easy for the user to enter the criteria through the form which works well. But again struggling on how I could have the user pick a code option to excluded it from the report. If it was just in the query i would put in the criteria <> CA . So trying to accomplish that from the form.

Thanks again.
Jerry

 

[Pat Hartman]

I didn't say that you didn't need a query, I said that you don't need to run it separately before you run the report. If the query is the RecordSource of the report, the report will run the query itself.
But again struggling on how I could have the user pick a code option to excluded it from the report. If it was just in the query i would put in the criteria <> CA . So trying to accomplish that from the form.
I gave you a code sample that shows how to open the report using either positive or negative criteria.

 

[jneirinckx]

Thanks again. Sorry I miss understood.

So where would I put your code ?

J

 

[Pat Hartman]

The code (which you'll need to change to conform with your control names, etc.) goes in the click event of the button you are using to open the report.