Solved

Compile error : expected end of statement

Posted on 2009-05-13
5
445 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

911 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now