Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Compile error : expected end of statement

Posted on 2009-05-13
5
Medium Priority
?
469 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
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…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

715 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