records not seen on the report when previewed

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

hello
I have a form which has a combo box and depending on the combo box
selection my query runs. When I execute the query, it shows all the records
that satisfy the criteria in the table and the query works perfectly. But
after using the same query as a Record Source for a report, and after
previewing the report, it only shows the first 2 or 3 records of the query
result and the rest are just empty.

After looking into the report properties, there seems nothing wrong in it
except that its not showing all the records thrown out by my query. please
suggest me where are things going wrong. any help is appreciated.

thanks in advance

talktobatchu
 
Check to make sure that you don't have filter set on the report's properties
that might limit the records shown.
 
when I checked the report properties, the Filter On property is set to No
itself. My combo box actually have 3 values to choose from, when the third
value is selected to view the report all the records are shown on the report
one by one page wise. But when any of the first two options are choosen in
the Combo box, only 2 to 3 records are shown instead of all the 55 records.

please suggest me of whats going wrong here. thanks for any help.

talktobatchu
 
Ok then, here's the issue. When yo uhave a null value in one of your combo
boxes, the records selected by the report's underlying query are looking for
a null in the fields that the combo box / query reference. To solve this
you need to follow two steps:

1.) In your reports underlying query, change the condition reference in the
combo box to use the Like operator (rather than an equal) e.g.

Like Forms!MyForm!MyCombo1

2.) For the combo boxes you need to add a an "*" option which is the
equivalent of "all values". To do this you can use a union query. So if
your combo right now as an example has a query that say pulls customer ids
and looks something like this:

Select CustomerID, CustomerName from tblCustomers

Then open that in the query grid and switch to SQL view. Change the SQL to
read:

Select "*" as CustomerID, "*" as CustomerName from tblCustomers
Union Select CustomerID, CustomerName from tblCustomers

Update the SQL and then set the default value for the combo to ="*"

That way when your query runs for the report, if you only select a specific
criteria for one combo, then for the other referenced fields in the reports
underlying query it will pull all values and not just values with nulls in
the fields.
 
Back
Top