Unable to build query on the Fly in Access 2007 ADP

I am trying to build a query on the fly with VBA. For some odd reason it seems to be cutting off my variables short.

//Current Statement
Dim sQuery As String
sQuery = "SELECT      dbo.VALVEINFO.NOMINALVALVESIZE, dbo.VALVEINFO.INTERNALVALVESIZE, dbo.VALVEINFO.PRESSURECLASS, dbo.VALVEINFO.VALVETYPE, " _
& "dbo.VALVEINFO.ACTUATION, dbo.VALVEINFO.NOTES, dbo.VALVEINFO.ENDCONNECTIONS, dbo.DRAWINGS.DRAWINGNUMBER, " _
& "dbo.VALVEINFO.DRAWINGID , dbo.VALVEINFO.VALVEID " _
& "FROM          dbo.DRAWINGS INNER JOIN " _
& "dbo.VALVEINFO ON dbo.DRAWINGS.DRAWINGID = dbo.VALVEINFO.DRAWINGID "

What shows up(in the debug screen):
"SELECT      dbo.VALVEINFO.NOMINALVALVESIZE, dbo.VALVEINFO.INTERNALVALVESIZE, dbo.VALVEINFO.PRESSURECLASS, dbo.VALVEINFO.VALVETYPE, dbo.VALVEINFO.ACTUATION, dbo.VALVEINFO.NOTES, dbo.VALVEINFO.ENDCONNECTIONS, dbo.DRAWINGS.DRAWINGNUMBER, dbo.VALVEINFO.DRAW"

Its almost like its cutting it off. I do have the Where section building fine, but when i try to concatenate them together, it shows this:
"SELECT      dbo.VALVEINFO.NOMINALVALVESIZE, dbo.VALVEINFO.INTERNALVALVESIZE, dbo.VALVEINFO.PRESSURECLASS, dbo.VALVEINFO.VALVETYPE, dbo.VALVEINFO.ACTUATION, dbo.VALVEINFO.NOTES, dbo.VALVEINFO.ENDCONNECTIONS, dbo.DRAWINGS.DRAWINGNUMBER, dbo.VALVEINFO.DRAW"

What do i seem to be missing?
tekkieguruAsked:
Who is Participating?
 
Richard DanekeTrainerCommented:
Concatentate your SQL.
use somethhing like:

Dim sQuery As String

sQuery = "SELECT      dbo.VALVEINFO.NOMINALVALVESIZE, dbo.VALVEINFO.INTERNALVALVESIZE, dbo.VALVEINFO.PRESSURECLASS, dbo.VALVEINFO.VALVETYPE, "
SQuery = sQuery & "dbo.VALVEINFO.ACTUATION, dbo.VALVEINFO.NOTES, dbo.VALVEINFO.ENDCONNECTIONS, dbo.DRAWINGS.DRAWINGNUMBER, "
 SQuery = sQuery & "dbo.VALVEINFO.DRAWINGID , dbo.VALVEINFO.VALVEID FROM          dbo.DRAWINGS INNER JOIN " 
SQuery = sQuery & "dbo.VALVEINFO ON dbo.DRAWINGS.DRAWINGID = dbo.VALVEINFO.DRAWINGID "

Open in new window

0
 
RDWaibelCommented:
Try this and post back the results of the DEBUG.PRINT

Dim sQuery As String
sQuery = "SELECT dbo.VALVEINFO.NOMINALVALVESIZE, dbo.VALVEINFO.INTERNALVALVESIZE, dbo.VALVEINFO.PRESSURECLASS, dbo.VALVEINFO.VALVETYPE, " _
& "dbo.VALVEINFO.ACTUATION, dbo.VALVEINFO.NOTES, dbo.VALVEINFO.ENDCONNECTIONS, dbo.DRAWINGS.DRAWINGNUMBER, " _
& "dbo.VALVEINFO.DRAWINGID , dbo.VALVEINFO.VALVEID " _
& "FROM dbo.DRAWINGS INNER JOIN " _
& "dbo.VALVEINFO ON dbo.DRAWINGS.DRAWINGID = dbo.VALVEINFO.DRAWINGID;"
Debug.Print sQuery
0
 
tekkieguruAuthor Commented:
Looks like that took care of it. Not sure why the other way wasnt working, but i'm used to working in Visual Studio using .net.

Thanks alot for the help!
0
 
Richard DanekeTrainerCommented:
Thank you.  It seems almost arbitrary, but there is some limit to the length of strings as you continue your lines.  It gets even more complicated with different editors - Visual Basic, VBA, SQL, etc.
Concatenating just simplifies putting the string together successfully without having to worry about the limit.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.