String Cuts Short

[saross]

Hi all. I've searched the forum for an answer to this, someone had a similar problem but never came back with the code for a solution to be posted so hope I'm not wasting anyone's time!

I am using string strSQL to take on an SQL statement that takes data from several tables to output to Excel. However, the string seems to cut the text short at around 250 characters. From what I can tell it should accept more characters than this and it certainly used to work... can anyone explain to me why it might be cutting it short? Here's the code:

strSQL = "SELECT TBLStaff.StaffName, TBLStaff.Initials, TBLOrg.Organisation, TblContact.Title, [FirstName] & ' ' & [LastName] AS Contact, [Department] & ', ' & [Address1] & ', ' & [Address2] & ', ' & [Address3] & ', ' & [Town] & ', ' & [Postcode] AS Address "
strSQL = strSQL & "FROM TBLOrg RIGHT JOIN (TBLStaff RIGHT JOIN (TblContact RIGHT JOIN TBLTender ON TblContact.ContactID = TBLTender.ContactID) ON TBLStaff.StaffID = TBLTender.StaffID) ON TBLOrg.OrgID = TBLTender.OrgID WHERE "
strSQL = strSQL & "(((TBLTender.TenderID)="
strSQL = strSQL & intTenderID & "));"

 

[Summerwind]

You could certainly make your strSQL string much shorter in a couple of ways:

Firstly, instead of using "strSQL = strSql &" , use the continuation code. ie.
strSQL = "SELECT fldOne,fldTwo, fldThree FROM " & _
"tblFields etc "

Secondly, name the table that the selected fields are coming from with a shortcut format:
strSQL = "SELECT f.fldOne,f.fldTwo,f.fldThree FROM " & _
"tblFields f etc "

Having spent a little time looking at your code, I'm wondering if it would actually work, but that is another story. Try the shortcuts above, then see how you get on.

 

[saross]

Thanks for this.

As it transpires, I had mis-named one of my fields. When I corrected that, the string gathered up all the text no problem!

Thanks again.