This question is really dumb but....

[Kevin_S]

Hi everyone,

I can't believe I am having problems with this but.....

I am embedding a few SQL statements in vb for option group selction choices. These select statements are really long and when I copy/paste them in from the query design grid to the module they look all jumbled on top of one another (still work perfectly fine however?!?!)

On top of that - one particularly long sql statement I can't even paste it in as it is so long it gets cut off!!!

I tried using " &_ " to stop and start the sql string but that is not working (I'm probably doing this wrong

Can someone suggest a way to "clean up" embedded sql stings. PLEASE!! I'm getting carple tunnel in my wrist from haveing to scroll to the right so much

Thanks gang,
Kevin

 

[WayneRyan]

Kevin,

Example:

strSQL = "Select FieldA, FieldB, FieldC " & _
         "From YourTable " & &
         "Where FieldA = '" & Me.txtFieldA & "' And " & _
         "      FieldB = '" & Me.txtFieldB & "' And " & _
         "      FieldC = '" & Me.txtFieldB & "';"

Hope that formatted correctly, that's my biggest problem
here.

Wayne

 

[Kevin_S]

Hi Wanye,

Gosh this is really perplexing!?!?! For some unknown reason this will not work. The statements are soooo long that I get a compile error when I try to run and the wording is all jumbled together!!!

Any ideas on how I can get this resolved? the " & _ function does not seem to work...

Thanks -Kevin

 

[Peter D]

When I do this I'll copy the query text into a text file, then add continuation characters and quotes, etc..

Make sure you have a space after the "&" in your continuation... Ex:

strSql = ""
strSql = strSql & "Select Myfield" & _
strSql = strSql & " From Mytable"


Hope this helps,

 

[WayneRyan]

Kevin,

If you are still having trouble, cut and paste the code here.

Wayne

 

[Kevin_S]

Hi Guys,

First - Thanks for the help on this - I appreciate it

Second - A little background - I am using the sql string as a recordsource for a listbox which is filtered by a combo box. The following is the code I am trying to make the rowsource for the listbox:

SELECT qryParsePositionID.PositionID, tblLocation.Location, tblTitle.PosTitle AS [Position Title], tblEmployTerm.EmploymentType AS [Employment Type], tblCommission.ClassPos AS [Commission Status], Right(tblPosition.PositionID,2) AS [Work Group #]
FROM ((((((qryParsePositionID INNER JOIN tblLocation ON qryParsePositionID.LocationID = tblLocation.LocationID) INNER JOIN tblPosition ON qryParsePositionID.PositionID = tblPosition.PositionID) INNER JOIN tblTitle ON qryParsePositionID.TitleID = tblTitle.TitleID) INNER JOIN tblEmployTerm ON qryParsePositionID.EmployTermID = tblEmployTerm.EmployTermID) INNER JOIN tblCommission ON qryParsePositionID.ClassID = tblCommission.ClassID) INNER JOIN (tblDistrict INNER JOIN tblLinkLocDist ON tblDistrict.DistrictID = tblLinkLocDist.DistrictID) ON tblLocation.LocationID = tblLinkLocDist.LocationID) LEFT JOIN tblEmployee ON tblPosition.PositionID = tblEmployee.PositionID
WHERE (((tblDistrict.DistrictID)=[forms]![frmSearch]![cboSiteList]) AND ((tblEmployee.EmployeeID) Is Null))
ORDER BY tblLocation.Location;


Peter - I haven't yet tried your solution - I have to take care of something real quick and then I will give it a try. One problem I am having is that even when I split out the SELECT, FROM, ORDER segments of the sql I still get a jumbled, non legible, mess for any sections that are longer that the screen view is wide - however - when I highlight this mess it clears up and I can read it again - really strange....

Thanks for the help!
Kevin

 

[WayneRyan]

Kevin:

sql = "SELECT qryParsePositionID.PositionID, " & _
      "tblLocation.Location,  " & _
      "tblTitle.PosTitle AS [Position Title],  " & _
      "tblEmployTerm.EmploymentType AS [Employment Type],  " & _
      "tblCommission.ClassPos AS [Commission Status],  " & _
      "Right(tblPosition.PositionID,2) AS [Work Group #]  " & _
      "FROM ((((((qryParsePositionID  " & _
      "INNER JOIN tblLocation ON  " & _
      "qryParsePositionID.LocationID = tblLocation.LocationID) " & _ 
      "INNER JOIN tblPosition ON  " & _
      "qryParsePositionID.PositionID = tblPosition.PositionID) " & _ 
      "INNER JOIN tblTitle ON  " & _
      "qryParsePositionID.TitleID = tblTitle.TitleID)  " & _
      "INNER JOIN tblEmployTerm ON  " & _
      "qryParsePositionID.EmployTermID = tblEmployTerm.EmployTermID) " & _ 
      "INNER JOIN tblCommission ON  " & _
      "qryParsePositionID.ClassID = tblCommission.ClassID)  " & _
      "INNER JOIN (tblDistrict  " & _
      "INNER JOIN tblLinkLocDist ON  " & _
      "tblDistrict.DistrictID = tblLinkLocDist.DistrictID)  " & _
      "ON tblLocation.LocationID = tblLinkLocDist.LocationID) " & _ 
      "LEFT JOIN tblEmployee ON tblPosition.PositionID = tblEmployee.PositionID  " & _
      "WHERE (((tblDistrict.DistrictID)=[forms]![frmSearch]![cboSiteList]) AND " & _ 
      "((tblEmployee.EmployeeID) Is Null))  " & _
      "ORDER BY tblLocation.Location;"

Wayne

 

[Kevin_S]

WOW!!!

That got it working Wayne! Thanks for the assist!

Now the only problem is I have to do this about 12 more times to make the rest of 'em readable

Thanks for the tip and the help...
Kevin