Hi everyone,
I've got a form (named REPORTPOPUP) with 3 unbound combo boxes (cboClients, cboMonth and cboYear) that get their lists from queries. For example, my "clients" combo box has the following query:
SELECT [CLIENTS].[ID], [CLIENTS].[FNAME], [CLIENTS].[LNAME], [CLIENTS].[ACTIVE] FROM CLIENTS WHERE ((([CLIENTS].[ACTIVE])=[Forms]![REPORTPOPUP]![chkActiveClient]));
This correctly gives me all of my "active/current" clients in my clients table. The Month and Year combo boxes give me month and year information about the selected client.

What I want to do is make my combo box selections on the form, click a button that runs a query based on these selections and then opens a report to display the query results.

I created a query (qryClient_Monthly_Report) with fields from the table. I also created a report (rptClient_Monthly_Report) based on this query. When I click my button on the form it opens the report based on the query.

If I put in no criteria, the query correctly returns the rows of data from the table that match my query fields and it is correctly displayed when my report is opened.

If I "hard-wire" a client name, month and year in the criteria fields, it correctly returns the query result for the name, month and year I put in and, again, correctly displays the data in my report.

Here's where I start to have my problem:
If I link my query criteria to the combo boxes on the form, the report displays no data. For example, in the client criteria I have [Forms]![REPORTPOPUP]![cboClients].
The Month criteria is [Forms]![REPORTPOPUP]![cboMonth].
And the year is [Forms]![REPORTPOPUP]![cboYear]

When I make my combo box selections and click the button and open the report, I have no results listed in the report.

The click event for my button is:
Private Sub btnPreviewReports_Click()
On Error GoTo Err_btnPreviewReports_Click

Dim stDocName As String

stDocName = "rptClient_Monthly_Report"
DoCmd.OpenReport stDocName, acPreview

Exit_btnPreviewReports_Click:
Exit Sub

Err_btnPreviewReports_Click:
MsgBox Err.Description
Resume Exit_btnPreviewReports_Click

End Sub

What am I doing wrong? :confused:

Thanks!

The criteria section of the query is probably not written correctly...can you post it?

Hi Adam, thanks for the reply.

The query that my report is based on has 4 fields in it: client/ID, month, year and servicetype. I want the criteria for the client/id field to come from the combo box (cboClients) on form REPORTPOPUP. So, in the query's design grid I set the criteria for client/id to: [Forms]![REPORTPOPUP]![cboClients].

Likewise, I want the month criteria to come from the month combo box. It's criteria is: [Forms]![REPORTPOPUP]![cboMonth]. And the same for the year combo box: [Forms]![REPORTPOPUP]![cboYear]

I've also tried: "Like [Forms]![REPORTPOPUP]![cboClients]",
"Like [Forms]![REPORTPOPUP]![cboMonth]"
"Like [Forms]![REPORTPOPUP]![cboYear]", but with no results.

This should not be that difficult, but I have been out of Access for about 5 or 6 years.

Thanks! :)

Here's some help on operators:

using AND: All criteria has to be populated, or you will see an empty report
using OR: this is the best way to do it

If this doesn't help, the best way for us to help YOU would be for you to post the entire SQL, like I originally requested. ;)

Hi Adam,
here's the SQL from the query for the report:

SELECT [CLIENTS Monthly Reports].ID, [CLIENTS Monthly Reports].MONTH, [CLIENTS Monthly Reports].YEAR, [CLIENTS Monthly Reports].SERVICETYPE
FROM [CLIENTS Monthly Reports]
WHERE ((([CLIENTS Monthly Reports].ID)=[Forms]![REPORTPOPUP]![cboClients]) AND (([CLIENTS Monthly Reports].MONTH)=[Forms]![REPORTPOPUP]![cboMonth]) AND (([CLIENTS Monthly Reports].YEAR)=[Forms]![REPORTPOPUP]![cboYear]));


As I said earlier, I've been out of Access for 5 or 6 years, so it took me a little while to find the query's SQL.....

Mike :o

This is probably where the problem lies:WHERE ((([CLIENTS Monthly Reports].ID)=[Forms]![REPORTPOPUP]![cboClients]) AND (([CLIENTS Monthly Reports].MONTH)=[Forms]![REPORTPOPUP]![cboMonth]) AND (([CLIENTS Monthly Reports].YEAR)=[Forms]![REPORTPOPUP]![cboYear]));All 3 of those have to be satisfied, otherwise, you're report is going to be blank. They are not being satisfied, otherwise you would see something on your report object.

Maybe try this instead:

WHERE (([CLIENTS Monthly Reports].ID)=[Forms]![REPORTPOPUP]![cboClients] OR [Forms]![REPORTPOPUP]![cboClients] IS NULL) AND ([CLIENTS Monthly Reports].MONTH)=[Forms]![REPORTPOPUP]![cboMonth] OR [Forms]![REPORTPOPUP]![cboMonth] IS NULL) AND ([CLIENTS Monthly Reports].YEAR)=[Forms]![REPORTPOPUP]![cboYear] OR [Forms]![REPORTPOPUP]![cboYear] IS NULL));

Or perhaps this:

WHERE ((([CLIENTS Monthly Reports].ID)=[Forms]![REPORTPOPUP]![cboClients]) OR (([CLIENTS Monthly Reports].MONTH)=[Forms]![REPORTPOPUP]![cboMonth]) OR (([CLIENTS Monthly Reports].YEAR)=[Forms]![REPORTPOPUP]![cboYear]));

Adam,
thanks for sticking with me on this.

I've tried the new suggestions and it still produces a blank report.

Another guess? :(

I have a few more, but I would really like to see it first. That would be the best way to fix it. I personally have a lot of knowledge in Access, but I can never rattle it off the top of my head without working with the actual file and seeing the knowledge apply to something on the screen. If you want to post it, I'm sure we can solve the problem...

Hi Adam,
let me clean out the confidential information from the tables tonight, insert some bogus info and I'll post it tomorrow.

Thanks,

Mike

Adam (and anyone else who cares to try),
here's the test DB.

Enjoy!

Mike

I have no idea what you did...but maybe you could do it this way...