Compile error : expected end of statement

Hi experts,

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.

Regards
Terry
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;

Open in new window

TerenceHewettAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
rockiroadsConnect With a Mentor Commented:
Are you wanting a space between the two durations? if so use single quotes
[BCPTaskDuration] & ' ' & [Duration]


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"

Open in new window

0
 
bleach77Commented:
You forgot the " before ;
0
 
TerenceHewettAuthor Commented:
Hi bleach77,

I tried putting a " before the ; at the end of the statement and I still get the same error.

Any other suggestions are welcome.

Regards,
Terry
0
 
TerenceHewettAuthor Commented:
Thanks for all posts - Rocki, as usual a first class response - you fixed my problem.

Regards,
Terry
0
 
rockiroadsCommented:
No problem Terry

just remember if you want to quotes within a string, either use the single quotes
or if building up a string and you want double quotes, use chr$(34)

You were right with your suspicions

Readin this, you are closing the string (first double quote you see here matches the one earlier on) Then you start again, which is a compile error

BCPTaskDuration] & " " & [Duration] AS Expr1

" "   is not valid

to use chr$(34)


"Hello Sir, this " & chr$(34) & "word" & chr$(34) & " is in double quotes"

produces this  Hello Sir, this "word" is in double quotes

Note static text in double quotes and always concatenated with &

Hope that makes sense

0
All Courses

From novice to tech pro — start learning today.