Solved

Compile error : expected end of statement

Posted on 2009-05-13
5
454 Views
Last Modified: 2013-11-27
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

0
Comment
Question by:TerenceHewett
  • 2
  • 2
5 Comments
 
LVL 4

Expert Comment

by:bleach77
ID: 24372867
You forgot the " before ;
0
 

Author Comment

by:TerenceHewett
ID: 24372929
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
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
ID: 24372936
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
 

Author Comment

by:TerenceHewett
ID: 24373883
Thanks for all posts - Rocki, as usual a first class response - you fixed my problem.

Regards,
Terry
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24374010
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

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question