Post reply

Unable to select data range from a report

  • August 17, 2006 at 3:06 am

    #169398

    Hi All,

    I am trying to open a report with the following code.

    When I open the report I get no data. If I remove the MonthDate criteria in the SQL string all the records return so I figure there must be a problem here but I can't see it.

    Could anyone help?

    thanks

    Steve

    Here is the code:

    Private Sub Report_Open(Cancel As Integer)

    VarID1 = Forms!frmReportsProjectsMenu!Study

    VarID2 = Forms!frmReportsProjectsMenu!cboEmployee

    VarID3 = Forms!frmReportsProjectsMenu!MonthStart

    VarID4 = Forms!frmReportsProjectsMenu!MonthEnd

    strSQL = "Select * FROM vwIndivPTMFcastbyProject" _

    & " WHERE StudyCode = '" & VarID1 & "'" _

    & " AND Employee = '" & VarID2 & "'" _

    & " AND MonthDate BETWEEN #" & VarID3 & "# AND #" & VarID4 & "#"

    Me.RecordSource = strSQL

    End Sub

  • August 17, 2006 at 5:11 am

    #655410

    Can you output debug.print strSQL or msgbox strSQL to verify strSQL?

  • August 17, 2006 at 7:41 am

    #655450

    Hi,

    Yes - no prob with the SQL.

    In fact the whole thing worked when I had the data as a select query in an standalone Access database but I am in the process of upgrading to SQL server and am trying to generate the report using a view in SQL server.

    thanks

    Steve

  • August 17, 2006 at 9:55 am

    #655487

    Does it work when you write a parameterquery (as a query object) and use that query for the recordsource?

    Is vwIndivPTMFcastbyProject a linked table or do you

    use T-SQL?

  • August 18, 2006 at 3:30 am

    #655591

    Hi,

    vwIndivPTMFcastbyProject is a linked table.

    I've not tried using a query as the recordsource - I'll give it a go, thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply