Report does not take query as filter into account 1

[AlexandreAmant]

Hi !

I am building an accounting DB and I use a form where the user can choose which data he wants to display.
The form calls the report via an openreport command containing the name of a query in the filter field.

The query works fine and filters my data as I want, but the report does not seem to take the query into account.

There are some fields that I use in my query that I don't need in my report, so there are more fields in my query than in my report, but I only show the ones that the report needs.

Any idea ?

Thanks !

@lex

 

[fredk]

Why don't you just base the report off the query?

Fred

 

[randysmid]

Hi,
Can you look at the report, and see what the recordsource is? You can find it by clicking in the upper left corner of the report, just to the left of the horizontal scale. It is a small rectangular box. After you click on it (it will have a black square in the middle of it), you can right click and select Properties. Click on the Data tab to see the recordsource. Is it the query, or perhaps the name of your table? Before you change it to the query, you need to open the query and make sure all the fields are there that you need on the report.

Am I confusing you? Here is why you might be confused: if the report is based on a table, and you have fields appearing on the report that are not in the query, then you will get all sorts of errors when you run the report. Here's an example: let's say you have a field in the table called LastName, and it appears on the report. BUT, if you query does not include it, and your change the recordsource to the query, then the report will crash and burn with errors because it could not find that field inside the field list. In fact, you can review the field list by clicking on View (top menu bar), then click Field List.

The field list is CRITICAL for another reason. To make your filter work, you are passing some criteria to the report. If the field name in your filter is NOT exactly the same as the field listed in the Field List, then the filter will NOT work.

By the way, sometimes you will want to use a value (e.g., credit/debit status) from the query, but you don't want it to appear on the report. You will still need to add it, especially if it is used in calculations. The secret is to add it to the report, but set the visible property to No.
HTH,
Randy Smith
California Teachers Association

 

[AlexandreAmant]

OK, thanks guys, it seems to work already better.

Now, the debit/credit field is still giving me a problem in the query: I have a combo and the user can choose between debit, credit and both.
I try to put this the query criteria for debitcredit:

Switch([Forms]![F_AccountBalance]![ComboDebitCredit] = "Debit", "Debit", [Forms]![F_AccountBalance]![ComboDebitCredit] = "Credit", "Credit", [Forms]![F_AccountBalance]![ComboDebitCredit] = "Both", "Debit" & """ Or """ & "Credit&quot

But it does not work when "Both" is selected and I can't see what's wrong.

I've tried also to put the null value or a zero-lenght string but everytime the query returns nothing.

Any idea ?

Thanks !

@lex