TOP 10 selection box for a report using message box 1

[HellsWoo]

Hello there,

I have been working on 2 reports, one to display the top 'n' Projects and another showing the top 'n' projects and respective orders.

For both reports the 'n' of projects is activated by a message box that pops up when the report is opened. This works fine for the first report but the second one only shows the top 'n' orders NOT projects with respective orders. The code for the second report is:

Private Sub Report_Open(Cancel As Integer)

Dim strSQL As String

Dim strInput

strInput = InputBox("Number of records")


strSQL = "SELECT TOP " & strInput & " tblProject_Details.Client, tblProject_Details.[Project Title], tblSupplier_Orders.Period, tblSupplier_Orders.[Order Type], tblSupplier_Orders.Amount, tblSupplier_ID.[Supplier Name], tblSupplier_Orders.[Project ID], tblSupplier_ID.[Supplier ID], ProjectValue_10.[Value (*000)], ProjectSumofAmount_FULL.[Sum Of Amount] " _
& "FROM tblSupplier_ID INNER JOIN (((tblProject_Details INNER JOIN ProjectValue_10 ON tblProject_Details.[Project ID] = ProjectValue_10.[Project ID]) INNER JOIN ProjectSumofAmount_FULL ON tblProject_Details.[Project ID] = ProjectSumofAmount_FULL.[Project ID]) INNER JOIN tblSupplier_Orders ON tblProject_Details.[Project ID] = tblSupplier_Orders.[Project ID]) ON tblSupplier_ID.[Supplier ID] = tblSupplier_Orders.[Supplier ID];"

Me.RecordSource = strSQL

End Sub


If you have any ideas please let me know.

cheers

Helen

 

[Crowley16]

paste your sql into the query builder, and remove the top 'n' bit.

use the querybuilder/datasheet view to check that you are indeed returning both projects AND respective orders...

--------------------
Procrastinate Now!

 

[HellsWoo]

Hiya

The SQL works correctly without the TOP 'n' bit. Where do I enter the TOP 'n' so that it gives me the TOP 'n' Projects rather than only the TOP 'n' records?

Cheers

Helen