Reports stopped working 1

[cjelec]

Hi,

For some very strange reason one of the reports in a database I created a while ago have stopped working. It doesn't show any results at all, there are no errors. Nothing has changed with the database since (except entries being added).

The report contains the query string and it doesn't link to a query object/document...

Its a very simple report, just displays items from a table.

I have double checked the query string, even have recreated the report (manually and using the wizard).

I have tried different default printers, as someone suggested that it could error on some printers.

I have tried the database (made copy and put) on different PCs using different OS's and versions of Access (XP, Vista, Office XP, Office 2003) without any success.

There are other reports in the db and they do work.

Can some one please help me?

Thanks for your help

 

[JerryKlmns]

Create a query with the same query string and execute it. Does it return any records? If it should but doesn't then it is your query to check!

 

[cjelec]

I had to leave this problem for a while to solve another. Back on it now.

I think I have found out why its not working, The report uses a query to link three tables together using INNER JOIN. It does work when all three tables have rows to link, but if one doesn't none of the tables returns anything.

I have a main linking table and two tables linked to it:

MainTable
ID (PK)
Table1_ID (Linked to Table1.ID)
Table2_ID (Linked to Table2.ID)

Table1
ID (PK)

Table2
ID (PK)

Access Wizard created INNER JOIN:

SELECT ... FROM Table2 INNER JOIN (Table1 INNER JOIN MainTable ON Table1.Id = MainTable.Table1_ID) ON Table2.Id = MainTable.Table2_ID

Output of columns:

MainTable.Id
MainTable.Table1_ID
MainTable.Table2_ID
Table1.ID
Table2.ID

What I need is for it to get all the MainTable rows with the Table1 and Table2 sub rows. If there isn't a row (say) on Table1 linking to the MainTable, I need it to still return the MainTable row linked to Table2...

Does anyone have any suggestions on how to do this?

Also, I hope this makes sense?

And thanks JerryKlmns for you post before, I did try and it helped me diagnose this problem.

Thanks

 

[KenReay]

Hi

Open your query in design view.

Right Click on the Join Line(s) one at a time, from the pop up menu which is displayed choose the option to show all rows from MainTable

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[cjelec]

KenReay,

Thanks so much, have had this problem for a long time and it turns out to be something simple

Thanks again