Problem passing parameter from excel through vba

[Sambo8]

Hi There,

Please can someone help with the code below. My issue is to do with the formating of pulling an entered date from Excel and passing it as a parameter into the sql string. If I hard code sql with the dates no problem, but it isn't accepting the parameters from excel. Any ideas?


cndbs.Open strconn

Dim rsdbs As ADODB.Recordset
Set rsdbs = New ADODB.Recordset

Set curSheet = ActiveWorkbook.Worksheets("Sheet2")

With curSheet
strFrom = VBA.Format(curSheet.Range("E1").Value, "dd mmm yyyy")
strTo = VBA.Format(curSheet.Range("F1").Value, "dd mmm yyyy")


End With

strText = "SELECT projects.PRJ_ID AS 'Hours_By_Project', projects.PRJ_NAME AS ' ',Sum(timesheets.TST_DURATION) AS 'Hours' FROM CampusDevelopment.dbo.view__activities activities, CampusDevelopment.dbo.view__projects projects, CampusDevelopment.dbo.View__timesheets timesheets WHERE projects.PRJ_ID = timesheets.PRJ_ID And timesheets.ACT_ID = activities.ACT_ID And (timesheets.TST_DATE >= '" & strFrom & " ' And timesheets.TST_DATE < ' " & strTo & " ' And (projects.PRJ_ID Like 'BPPIT%')) GROUP BY projects.PRJ_ID, projects.PRJ_NAME"

Cheers

Sam

 

[rmikesmith]

Sam,

I think you need to use something like (modifications in bold):

strText = "SELECT projects.PRJ_ID AS 'Hours_By_Project', projects.PRJ_NAME AS ' ',Sum(timesheets.TST_DURATION) AS 'Hours' FROM CampusDevelopment.dbo.view__activities activities, CampusDevelopment.dbo.view__projects projects, CampusDevelopment.dbo.View__timesheets timesheets  WHERE projects.PRJ_ID = timesheets.PRJ_ID And timesheets.ACT_ID = activities.ACT_ID And (timesheets.TST_DATE >= ' [b]#[/b]" & strFrom & "[b]#[/b] ' And timesheets.TST_DATE < ' [b]#[/b]" & strTo & "[b]#[/b] ' And (projects.PRJ_ID Like 'BPPIT%')) GROUP BY projects.PRJ_ID, projects.PRJ_NAME"

Regards,
Mike

 

[Sambo8]

Hi Mike,

I gave this a try, and I also tried without the apostraphe's, but I'm still getting this runtime error '-2147217900(8004oe14)' Is there any special formating that I need to do in the cells in Excel?

Cheers

Sam

 

[rmikesmith]

Sam,

Just noticed in your date assignment statements you are formating the date without standard separator characters ( - or /) but rather spaces. Ex.

strFrom = VBA.Format(curSheet.Range("E1").Value, "[b]dd mmm yyyy[/b]")

Are dates in that format allowed in SQL (not an expert on SQL)? Did you hard code your dates in the same format when you tested?


Regards,
Mike