Solved

Compile error : expected end of statement

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to use DLookup with IsNull Function 4 26
Sql Server group by 10 29
My SQL as Backend for Access 3 18
Display <> "Yes" records 10 10
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

777 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