I have the attached code that works when displaying it in my list box, but does not work when I try and use VBA to put this SQL statement into the list box. I get the error "Compile error : expected end of statement" and Access just highlights the whole line of code.
I think the problem relates to the following part of the SQL but I am not sure:
[BCPTaskDuration] & " " & [Duration] AS Expr1
Any help would be really appreciated.
Dim MySql as String
MySql="SELECT XATasksToBCP.ABCPTaskID, XLupBusinessContinuityPhases.BusinessContinuityPhaseSeq, XATasksToBCP.Sequence, XATasksToBCP.BCPTaskID, XATasksToBCP.BusinessContinuityPhaseID, XATasksToBCP.Plan_Sys_ID, XATasksToBCP.BCPTask, [BCPTaskDuration] & " " & [Duration] AS Expr1 FROM (XATasksToBCP INNER JOIN XLupBusinessContinuityPhases ON XATasksToBCP.BusinessContinuityPhaseID = XLupBusinessContinuityPhases.BusinessContinuityPhaseID) LEFT JOIN XLupDuration ON XATasksToBCP.BCPTaskDurationPeriod = XLupDuration.ID WHERE (((XATasksToBCP.Plan_Sys_ID)=[Forms]![SB]![Plan_Sys_ID])) ORDER BY XLupBusinessContinuityPhases.BusinessContinuityPhaseSeq, XATasksToBCP.Sequence;