Unable to select data range from a report

Question

Post reply

Unable to select data range from a report

Stephen Craig

SSC-Addicted

Points: 464
More actions
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

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

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

Jo Pattyn SSC-Dedicated Points: 32350 More actions · Answer 1

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

Stephen Craig SSC-Addicted Points: 464 More actions · Answer 2

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

Jo Pattyn SSC-Dedicated Points: 32350 More actions · Answer 3

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?

Stephen Craig SSC-Addicted Points: 464 More actions · Answer 4

Hi,

vwIndivPTMFcastbyProject is a linked table.

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