By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,144 Members | 972 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,144 IT Pros & Developers. It's quick & easy.

NoData event happening when there _is_ data.

P: n/a
Access 97 and SQL Server 2000.

Reports in this system are driven from a parameter form. Parameters
are used to make a WHERE clause. If no parameters are selected,
strWhere is a zero length string. All reports are based on a stored
query. e.g. rptWhatever would have its base query "qselRptWhatever".
The OnOpen procedure changes the report's RecordSource to:

"Select qselRptWhatever.* From qselRptWhatever" & strWhere & ";"

Everything works beautifully except for one report.

It's a marketing system and this report is listing customers who have
not got a NextContactDate set. The base query selects all customers
with no future date set. The parameter is the ConsultantID. So we end
up with a query like

"Select qselRptNoDate.* From qselRptNoDate;"

or

"Select qselRptNoDate.* From qselRptNoDate Where
qselRptNoDate.ResponsibleConsultantID IN (Select ConsultantID From
tblTempConsultantSelected);"

The report works on my development network. Perfectly. When I install
it on the customer's network, the report gives a NoData event if
strWhere is a zero length string. If I run the report using one or
many (or all) consultants as parameters, the report shows all the
data.

If I open the report in design view (no parameters) and open the query
from the RecordSource property in the Properties box, it shows all
records. If I create a new query using exactly the SQL used in the
report, it shows all records. If I then open the report in preview, I
get a NoData event.

I'm stumped. Any ideas?

Regards,
Richard.
--
Regards.
Richard.
Nov 14 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Richard Sherratt wrote:
Access 97 and SQL Server 2000.

Reports in this system are driven from a parameter form. Parameters
are used to make a WHERE clause. If no parameters are selected,
strWhere is a zero length string. All reports are based on a stored
query. e.g. rptWhatever would have its base query "qselRptWhatever".
The OnOpen procedure changes the report's RecordSource to:

"Select qselRptWhatever.* From qselRptWhatever" & strWhere & ";"

Everything works beautifully except for one report.

It's a marketing system and this report is listing customers who have
not got a NextContactDate set. The base query selects all customers
with no future date set. The parameter is the ConsultantID. So we end
up with a query like

"Select qselRptNoDate.* From qselRptNoDate;"

or

"Select qselRptNoDate.* From qselRptNoDate Where
qselRptNoDate.ResponsibleConsultantID IN (Select ConsultantID From
tblTempConsultantSelected);"

The report works on my development network. Perfectly. When I install
it on the customer's network, the report gives a NoData event if
strWhere is a zero length string. If I run the report using one or
many (or all) consultants as parameters, the report shows all the
data.

If I open the report in design view (no parameters) and open the query
from the RecordSource property in the Properties box, it shows all
records. If I create a new query using exactly the SQL used in the
report, it shows all records. If I then open the report in preview, I
get a NoData event.

I'm stumped. Any ideas?

Regards,
Richard.


Most likely you have 2 tables in the query. One has the "main" records
and the other is a one-one or one-to-many. If there is no record in
that table to match the main table, no records will be displayed.
Change your query to Left join, not Inner join between the tables. To
do so, open your query builder and right click on the relationship line.
Nov 14 '05 #2

P: n/a
On Mon, 14 Nov 2005 17:46:21 GMT, Salad <oi*@vinegar.com> wrote:

<snip>
Most likely you have 2 tables in the query. One has the "main" records
and the other is a one-one or one-to-many. If there is no record in
that table to match the main table, no records will be displayed.
Change your query to Left join, not Inner join between the tables. To
do so, open your query builder and right click on the relationship line.


Thank you. There are no joins in the query. It's

"Select qselRptNoDate.* From qselRptNoDate;"

There are joins in qselRptNoDate, but, as I said in my post, if I open
exactly the same query from the database window, I get all the data
shown. If I open exactly the same query from the query builder in the
report's design view, I get all the data. If I exit the query builder
and hit the preview button, I get NoData.
--
Regards.
Richard.
Nov 14 '05 #3

P: n/a
Richard Sherratt wrote:
On Mon, 14 Nov 2005 17:46:21 GMT, Salad <oi*@vinegar.com> wrote:

<snip>
Most likely you have 2 tables in the query. One has the "main" records
and the other is a one-one or one-to-many. If there is no record in
that table to match the main table, no records will be displayed.
Change your query to Left join, not Inner join between the tables. To
do so, open your query builder and right click on the relationship line.


Thank you. There are no joins in the query. It's

"Select qselRptNoDate.* From qselRptNoDate;"

There are joins in qselRptNoDate, but, as I said in my post, if I open
exactly the same query from the database window, I get all the data
shown. If I open exactly the same query from the query builder in the
report's design view, I get all the data. If I exit the query builder
and hit the preview button, I get NoData.
--
Regards.
Richard.


The last time someone had a problem like this some wise poster
suggested checking the Filter property of the report. A97 can save a
filter in the Filter Property and might cause the records not to show
up for no apparent reason even though they show up in the query.

James A. Fortune

Nov 14 '05 #4

P: n/a
On 14 Nov 2005 12:09:16 -0800, ji********@compumarc.com wrote:
Richard Sherratt wrote:
On Mon, 14 Nov 2005 17:46:21 GMT, Salad <oi*@vinegar.com> wrote:

<snip>
>Most likely you have 2 tables in the query. One has the "main" records
>and the other is a one-one or one-to-many. If there is no record in
>that table to match the main table, no records will be displayed.
>Change your query to Left join, not Inner join between the tables. To
>do so, open your query builder and right click on the relationship line.
Thank you. There are no joins in the query. It's

"Select qselRptNoDate.* From qselRptNoDate;"

There are joins in qselRptNoDate, but, as I said in my post, if I open
exactly the same query from the database window, I get all the data
shown. If I open exactly the same query from the query builder in the
report's design view, I get all the data. If I exit the query builder
and hit the preview button, I get NoData.

The last time someone had a problem like this some wise poster
suggested checking the Filter property of the report. A97 can save a
filter in the Filter Property and might cause the records not to show
up for no apparent reason even though they show up in the query.


Thanks Jim. It wasn't that either.

A funny thing happened today. I pointed the front end at the
production SQL Server this morning and the report worked. Pointed it
back at the client's development server and it still worked. Hmmm.
Might be something to do with the ODBC drivers/interface between A97
and SQL 2K/the phase of the moon.

The parameter form has a multi-select list box of Consultants. If none
are selected, the ConsultantID parameter is not used. The BuildWhere
function builds a table of selected ConsultantIDs. If the problem
comes back again, I could always change the BuildWhere to use all
consultants if none are selected. <Shrug>
--
Regards.
Richard.
Nov 15 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.