characters found after end of SQL statement (1 Viewer)

[iankerry]

Hi

I have been trying to solve this for over an hour, and i'll bet it is something stupid.

I have four line of SQL = statements. Two to select the fields, one as a from and one as a WHERE. The most important one, and the one that is giving me trouble. (i know this because if i REM it out the code works).

so, something like this

SQL = "SELECT dbo_Promoters.postcode, dbo_Promoters.[Box Office] etc etc
SQL = SQL & "dbo_EventsLive.PromoOnCosts, PromoTotalCost, etc
SQL = SQL & "FROM dbo_WhichProjectLive INNER JOIN etc
SQL = SQL & "WHERE dbo_EventsLive.EventID=" & [currentID]

When I run this i get
Character found after end of SQL statement.

I have tried many things - like putting another quotes at the end, brackets. i even tried putting WHERE EventID = 1, but i still get the same message.

Can Anyone advise? Before I go nuts.

thanks

Ian

 

[Kiwiman]

Howzit

Try

SQL = SQL & "WHERE dbo_EventsLive.EventID=" & me.CurrentID

 

[vbaInet]

etc etc doesn't help us. We need to see the entire SQL statement enclosed in Code tags. Here's how you put it in code tags:

http://www.access-programmers.co.uk/forums/showthread.php?p=1009015#post1009015

 

[MSAccessRookie]

Hi

I have been trying to solve this for over an hour, and i'll bet it is something stupid.

I have four line of SQL = statements. Two to select the fields, one as a from and one as a WHERE. The most important one, and the one that is giving me trouble. (i know this because if i REM it out the code works).

so, something like this

SQL = "SELECT dbo_Promoters.postcode, dbo_Promoters.[Box Office] etc etc
SQL = SQL & "dbo_EventsLive.PromoOnCosts, PromoTotalCost, etc
SQL = SQL & "FROM dbo_WhichProjectLive INNER JOIN etc
SQL = SQL & "WHERE dbo_EventsLive.EventID=" & [currentID]

When I run this i get
Character found after end of SQL statement.

I have tried many things - like putting another quotes at the end, brackets. i even tried putting WHERE EventID = 1, but i still get the same message.

Can Anyone advise? Before I go nuts.

thanks

Ian

I agree with Kiwiman and vbaInet in regards to the need for complete disclosure. In the mean time, I suspect that your Query does not end with the code displayed, since it is common practice to end an SQL Query with a semi-colon (";"). Are there any more lines of code updating the String SQL? I suspect that they might contain (optional?) "GROUP BY" or "ORDER BY" statements to further refine the results. Once you locate the semi-colon, you can see if anything is being added afterwards.

-- Rookie

 

[iankerry]

Thanks Guys, sorry about not posting full code. As it was only the last line than was giving the error i thought it not necessary but i have now learnt different.

It was indeed a rouge semi colon on the penultimate line - as soon as i removed this the code worked.

Sometimes you look so hard you stop seeing...

thanks for all your input

SQL = "SELECT dbo_Promoters.postcode, dbo_Promoters.[Box Office], dbo_Promoters.PHONE, dbo_Promoters.ADDRESS1, dbo_Promoters.ADDRESS2, dbo_Promoters.TOWN, dbo_Promoters.COUNTY, dbo_Promoters.name, dbo_Venues.venue, dbo_Venues.[venue postcode], dbo_Companies.[contact name], dbo_Companies.address1, dbo_Companies.address2, dbo_Companies.town, dbo_Companies.county, dbo_Companies.postcode, dbo_Companies.mobile, dbo_Companies.Email, dbo_Companies.[Tel:], dbo_EventsLive.[Accom costs etc], dbo_Promoters.NAME, dbo_EventsLive.EventID, dbo_EventsLive.[event date], dbo_EventsLive.time, dbo_Promoters.email, dbo_EventsLive.AdultTP, dbo_EventsLive.FamilyTP, dbo_EventsLive.ChildTP, dbo_EventsLive.[workshop requested], dbo_EventsLive.[Total Cost of Event], dbo_EventsLive.[Promoter Fee], dbo_EventsLive.[Actual Cost of Show], dbo_EventsLive.[workshop notes], dbo_EventsLive.[workshop time], dbo_EventsLive.[workshop cost], dbo_EventsLive.GALSPCoCost, dbo_EventsLive.PromoShowCost,"

SQL = SQL & "dbo_EventsLive.PromoOnCosts , dbo_EventsLive.PromoTotalCost , dbo_WhichProjectLive.WhichProject, dbo_Shows.[Show Name]"

SQL = SQL & "FROM dbo_WhichProjectLive INNER JOIN (dbo_Shows INNER JOIN (dbo_Companies INNER JOIN (dbo_Promoters INNER JOIN (dbo_Venues INNER JOIN dbo_EventsLive ON dbo_Venues.ID = dbo_EventsLive.VenueID) ON dbo_Promoters.ID = dbo_EventsLive.PromoterID) ON dbo_Companies.ID = dbo_EventsLive.CompanyID) ON dbo_Shows.ID = dbo_EventsLive.ShowID) ON dbo_WhichProjectLive.ID = dbo_EventsLive.WhichProject_ID "

SQL = SQL & " WHERE dbo_EventsLive.EventID=" & [currentID]

If OpenConnection Then
        If rs.State = adStateOpen Then rs.Close
         rs.Open SQL, cn, adOpenKeyset, adLockOptimistic

 

[vbaInet]

It's [/code] not [/end code] just like it mentions in the link.

It would be nice if you could edit your post and re-copy and paste your code inside the box so that the indents remain.

 

[iankerry]

thanks for pointing that out, and sorry again.

Ian

 

[vbaInet]

I hope you notice the added and fixed bits:

SQL = "SELECT dbo_Promoters.postcode, dbo_Promoters.[Box Office], dbo_Promoters.PHONE, dbo_Promoters.ADDRESS1, dbo_Promoters.ADDRESS2, dbo_Promoters.TOWN, dbo_Promoters.COUNTY, dbo_Promoters.name, dbo_Venues.venue, dbo_Venues.[venue postcode], dbo_Companies.[contact name], dbo_Companies.address1, dbo_Companies.address2, dbo_Companies.town, dbo_Companies.county, dbo_Companies.postcode, dbo_Companies.mobile, dbo_Companies.Email, dbo_Companies.[Tel:], dbo_EventsLive.[Accom costs etc], dbo_Promoters.NAME, dbo_EventsLive.EventID, dbo_EventsLive.[event date], dbo_EventsLive.time, dbo_Promoters.email, dbo_EventsLive.AdultTP, dbo_EventsLive.FamilyTP, dbo_EventsLive.ChildTP, dbo_EventsLive.[workshop requested], dbo_EventsLive.[Total Cost of Event], dbo_EventsLive.[Promoter Fee], dbo_EventsLive.[Actual Cost of Show], dbo_EventsLive.[workshop notes], dbo_EventsLive.[workshop time], dbo_EventsLive.[workshop cost], dbo_EventsLive.GALSPCoCost, dbo_EventsLive.PromoShowCost, " & _
          "dbo_EventsLive.PromoOnCosts , dbo_EventsLive.PromoTotalCost , dbo_WhichProjectLive.WhichProject, dbo_Shows.[Show Name] " & _
      "FROM dbo_WhichProjectLive INNER JOIN (dbo_Shows INNER JOIN (dbo_Companies INNER JOIN (dbo_Promoters INNER JOIN (dbo_Venues INNER JOIN dbo_EventsLive ON dbo_Venues.ID = dbo_EventsLive.VenueID) ON dbo_Promoters.ID = dbo_EventsLive.PromoterID) ON dbo_Companies.ID = dbo_EventsLive.CompanyID) ON dbo_Shows.ID = dbo_EventsLive.ShowID) ON dbo_WhichProjectLive.ID = dbo_EventsLive.WhichProject_ID " & _
      "WHERE dbo_EventsLive.EventID = " & [currentID] & ";"

 

[iankerry]

Ah, so here it looks like there is an

& _

at the end of each line so that i dont have to do the SQL= SQL & bit?

and a final ; in quotes.

It seems a much neater solution, thanks. Hope I didn't miss anything?

 

[vbaInet]

All correct!

So instead of continuously re-saving into the sql string variable, you concatenate it all in one go into the variable.

The most important thing (which was causing the problem) that you missed was at the end of each line, you didn't add a space. An example of the output was:

dbo_EventsLive.WhichProject_ID[COLOR=Blue][B]WHERE[/B][/COLOR] dbo_EventsLive.EventID

See your WHERE clause? There was no space.

By the way, SQL isn't a good name for a variable. It could be reserbed for VBA or Access.

 

[iankerry]

Yup, I can see now. And at last it all seems to work ok! That's a major new time saver for us. (not me it took many nights to create this little routine, but good fun) - simply clicking a button to send an email with info from the database in it - by way of a contract.

I am a happy chappy...

Thanks

Ian

 

[vbaInet]

A pretty good job then.

Good luck with the rest of your project!