Pulling SQL query on multiple lines. (1 Viewer)

[jgier]

I am trying to write a query with a large amount of fields and need it to write on multiple lines. Any suggestions?

rsRecordset.Open _
Source:="Select MASS_TEMP_INPUT.LIC_ACTION_CD, MASS_TEMP_INPUT.[LIC-ID], MASS_TEMP_INPUT.[LIC-SYS-ASSGN-NBR], MASS_TEMP_INPUT.[LIC-ST-CS], MASS_TEMP_INPUT.[LIC-NBR], " _
& "MASS_TEMP_INPUT.[LIC-CLASS-CD], MASS_TEMP_INPUT.[LIC-SAN-TY-CD], MASS_TEMP_INPUT.[LIC-EFF-DT], MASS_TEMP_INPUT.[LIC-RES-IND], MASS_TEMP_INPUT.[LIC-ISS-DT], " _
& "MASS_TEMP_INPUT.[LIC-EXPR-DT], MASS_TEMP_INPUT.[LIC-ST-STTS-CD], MASS_TEMP_INPUT.[LIC-HUM-STTS-CD], MASS_TEMP_INPUT.[LIC-HUM-STTS-REAS-CD], " _
& "MASS_TEMP_INPUT.[LIC-TERM-PEND-DT], MASS_TEMP_INPUT.[LIC-TERM-DT], MASS_TEMP_INPUT.[LIC-LST-CHG-USER-ID], MASS_TEMP_INPUT.[LIC-HUM-STTS-CHG-DT], " & _
"MASS_TEMP_INPUT.[LIC-DBA-TAX-NBR], MASS_TEMP_INPUT.[LIC-DBA-NM], MASS_TEMP_INPUT.[LIC-TERM-FOR-CAUS], MASS_TEMP_INPUT.[LIC-FIRST-TM-IND]" _
" From MASS_TEMP_INPUT;", _
ActiveConnection:=CurrentProject.Connection

 

[pbaldy]

Not sure what your question is, since you already have line continuation characters in there. If you're having trouble with the SQL, this may help:

http://www.baldyweb.com/ImmediateWindow.htm

 

[jgier]

Yes, I have the characters in there, but I guess the VB environment doesn't like the characters within the SQL statement. I'm getting a Compile Error Expected: end of statement. So my question is really whether or not there are any other ways I could do a continuation.

 

[mdlueck]

I am trying to write a query with a large amount of fields and need it to write on multiple lines. Any suggestions?

rsRecordset.Open _
Source:="Select MASS_TEMP_INPUT.LIC_ACTION_CD, MASS_TEMP_INPUT.[LIC-ID], MASS_TEMP_INPUT.[LIC-SYS-ASSGN-NBR], MASS_TEMP_INPUT.[LIC-ST-CS], MASS_TEMP_INPUT.[LIC-NBR], " _

** snip **

The way I concantonate a long string in Access VBA uses both the & and _ characters. I see you intermix in the code you provided as example.

** snip **
                   "WHERE [p].[id] = " & ObjPartsTbl.id & " " & _
                   "AND [met].[tooltypeid] IN " & _
                   "(SELECT [mettt].[id] " & _
                   "WHERE [mettt].[aqeflg] <> 0) " & _
                   "ORDER BY [met].[id];"

And I could not tell... do you actually have the multiple SQL lines on multiple lines in the VBA editor? (Did you perhaps not put the SQL within [ CODE ] tags?)

 

[GregRun]

Also, the best thing to do is to build that string in a variable and then call the Open. Access likes this better than building the string inside of the Open.

Dim mySQL As String

mySQL = "WHERE [p].[id] = " & ObjPartsTbl.id & " " & _
                   "AND [met].[tooltypeid] IN " & _
                   "(SELECT [mettt].[id] " & _
                   "WHERE [mettt].[aqeflg] <> 0) " & _
                   "ORDER BY [met].[id];"
rst.Open Source:=mySQL, ActiveConnection:=CurrentProject.Connection

 

[jgier]

Thanks for all the suggestions, I tried this set of SQL statement and it looks like it works. It looked like it was getting confused with my original "FROM MASS_TEMP_INPUT;", _ . So I just placed the FROM MASS_TEMP_INPUT into my original SELECT statement and it appears to work.

If rsRecordset.EOF = False Then
DoCmd.RunSQL "DELETE FROM TEMP_INPUT"
rsRecordset.MoveFirst
Do
Set rsRecordset = New ADODB.Recordset
rsRecordset.Open _
Source:="INSERT INTO TEMP_INPUT" & _
" Select MASS_TEMP_INPUT.LIC_ACTION_CD, MASS_TEMP_INPUT.[LIC-ID], MASS_TEMP_INPUT.[LIC-SYS-ASSGN-NBR], MASS_TEMP_INPUT.[LIC-ST-CS], MASS_TEMP_INPUT.[LIC-NBR], " _
& "MASS_TEMP_INPUT.[LIC-CLASS-CD], MASS_TEMP_INPUT.[LIC-SAN-TY-CD], MASS_TEMP_INPUT.[LIC-EFF-DT], MASS_TEMP_INPUT.[LIC-RES-IND], MASS_TEMP_INPUT.[LIC-ISS-DT], " _
& "MASS_TEMP_INPUT.[LIC-EXPR-DT], MASS_TEMP_INPUT.[LIC-ST-STTS-CD], MASS_TEMP_INPUT.[LIC-HUM-STTS-CD], MASS_TEMP_INPUT.[LIC-HUM-STTS-REAS-CD], " _
& "MASS_TEMP_INPUT.[LIC-TERM-PEND-DT], MASS_TEMP_INPUT.[LIC-TERM-DT], MASS_TEMP_INPUT.[LIC-LST-CHG-USER-ID], MASS_TEMP_INPUT.[LIC-HUM-STTS-CHG-DT], " _
& "MASS_TEMP_INPUT.[LIC-DBA-TAX-NBR], MASS_TEMP_INPUT.[LIC-DBA-NM], MASS_TEMP_INPUT.[LIC-TERM-FOR-CAUS], MASS_TEMP_INPUT.[LIC-FIRST-TM-IND] FROM MASS_TEMP_INPUT;", _
ActiveConnection:=CurrentProject.Connection

 

[pbaldy]

You can either add a WHERE clause to restrict to a particular record, or try the TOP predicate:

SELECT TOP 1 ...

 

[GregRun]

Just noticed something... you're explicitly declaring the table for each field but you're only querying one table. This works, but since you're having such problems with the length of the string i would suggest removing all of the "MASS_TEMP_INPUT." from your query string. You don't need it if you're only querying one table.

As to your question: To pull a single record you'll need a "WHERE" statement at the end of your SQL.

 

[jgier]

@pbaldy Do I place that in the original Select statement?

 

[pbaldy]

You can try adding it, sure. Without an ORDER BY clause, you're going to get a random record.

 

[jgier]

@GregRun I had originally created a sql statement with the Select * From MASS_TEMP_INPUT but I didnt think you could create a Where when you do a Select *. Also I don't want to just pull one record for viewing, I hope to pull one record through a upload process than movenext to second record.

 

[pbaldy]

You can certainly add a WHERE clause with SELECT *. You may want to outline the entire desired process. It sounds like a recordset might be a better option, if you want to process a record at a time for some reason.

 

[MSAccessRookie]

I am trying to write a query with a large amount of fields and need it to write on multiple lines. Any suggestions?

rsRecordset.Open _
Source:="Select MASS_TEMP_INPUT.LIC_ACTION_CD, MASS_TEMP_INPUT.[LIC-ID], MASS_TEMP_INPUT.[LIC-SYS-ASSGN-NBR], MASS_TEMP_INPUT.[LIC-ST-CS], MASS_TEMP_INPUT.[LIC-NBR], " _
& "MASS_TEMP_INPUT.[LIC-CLASS-CD], MASS_TEMP_INPUT.[LIC-SAN-TY-CD], MASS_TEMP_INPUT.[LIC-EFF-DT], MASS_TEMP_INPUT.[LIC-RES-IND], MASS_TEMP_INPUT.[LIC-ISS-DT], " _
& "MASS_TEMP_INPUT.[LIC-EXPR-DT], MASS_TEMP_INPUT.[LIC-ST-STTS-CD], MASS_TEMP_INPUT.[LIC-HUM-STTS-CD], MASS_TEMP_INPUT.[LIC-HUM-STTS-REAS-CD], " _
& "MASS_TEMP_INPUT.[LIC-TERM-PEND-DT], MASS_TEMP_INPUT.[LIC-TERM-DT], MASS_TEMP_INPUT.[LIC-LST-CHG-USER-ID], MASS_TEMP_INPUT.[LIC-HUM-STTS-CHG-DT], " & _
"MASS_TEMP_INPUT.[LIC-DBA-TAX-NBR], MASS_TEMP_INPUT.[LIC-DBA-NM], MASS_TEMP_INPUT.[LIC-TERM-FOR-CAUS], MASS_TEMP_INPUT.[LIC-FIRST-TM-IND]" _
& " From MASS_TEMP_INPUT;", _
ActiveConnection:=CurrentProject.Connection

It looks like you left off the Connect for the text on the last line.

 

[jgier]

Thanks for all the input!