Hello,

I have noticed that the "Linebreaks after AND/OR" option does not seem to work, at least not with the options I have selected. Linebreaks before AND/OR seems to work fine. I am using the desktop edition, input SQL is 'Any SQL', output SQL is VB String(2). Example from MS Access:

SELECT IIf([KitSource]=1,"BioTrack","BioKit") AS KitDB, tblKits.KitID, tblKits.DateSent, tblKits.DateReceived, tblKits.Expired, tblKits.SampleType, tblKits.Resample, tblKits.KitSource
FROM (tblKits LEFT JOIN tblEventBN ON tblKits.KitID = tblEventBN.KitID) LEFT JOIN tblEventBN_R ON tblKits.KitID = tblEventBN_R.KitID
WHERE (((tblKits.SubjectID)=100010027) AND ((tblEventBN.KitID) Is Null) AND ((tblEventBN_R.KitID) Is Null));

Formatted with Linebreaks after AND/OR (note that it has removed a space after each AND):
strSQL= "" & _
"SELECT IIf([KitSource] = 1, "BioTrack", "BioKit") AS KitDB, " & _
"tblKits.KitID, " & _
"tblKits.DateSent, " & _
"tblKits.DateReceived, " & _
"tblKits.Expired, " & _
"tblKits.SampleType, " & _
"tblKits.Resample, " & _
"tblKits.KitSource " & _
"FROM(tblKits " & _
"LEFT JOIN tblEventBN " & _
"ON tblKits.KitID = tblEventBN.KitID) " & _
"LEFT JOIN tblEventBN_R " & _
"ON tblKits.KitID = tblEventBN_R.KitID " & _
"WHERE(((tblKits.SubjectID) = 100010027) AND((tblEventBN.KitID) IS NULL) AND((tblEventBN_R.KitID) IS NULL)) ;"

Formatted with Linebreaks before AND/OR:
strSQL= "" & _
"SELECT IIf([KitSource] = 1, "BioTrack", "BioKit") AS KitDB, " & _
"tblKits.KitID, " & _
"tblKits.DateSent, " & _
"tblKits.DateReceived, " & _
"tblKits.Expired, " & _
"tblKits.SampleType, " & _
"tblKits.Resample, " & _
"tblKits.KitSource " & _
"FROM(tblKits " & _
"LEFT JOIN tblEventBN " & _
"ON tblKits.KitID = tblEventBN.KitID) " & _
"LEFT JOIN tblEventBN_R " & _
"ON tblKits.KitID = tblEventBN_R.KitID " & _
"WHERE(((tblKits.SubjectID) = 100010027) " & _
"AND((tblEventBN.KitID) IS NULL) " & _
"AND((tblEventBN_R.KitID) IS NULL)) ;"



Edited 1 time(s). Last edit at 03/13/2012 06:06PM by guidomarcel.

Hi,
thanks for yor feedback. Could you pleas post the SQL before you formatted it?
Thanks
Guido

Hi Guido,

It's at the beginning of my post, but here it is again by itself. Again, this is from MS Access:

SELECT IIf([KitSource]=1,"BioTrack","BioKit") AS KitDB, tblKits.KitID, tblKits.DateSent, tblKits.DateReceived, tblKits.Expired, tblKits.SampleType, tblKits.Resample, tblKits.KitSource
FROM (tblKits LEFT JOIN tblEventBN ON tblKits.KitID = tblEventBN.KitID) LEFT JOIN tblEventBN_R ON tblKits.KitID = tblEventBN_R.KitID
WHERE (((tblKits.SubjectID)=100010027) AND ((tblEventBN.KitID) Is Null) AND ((tblEventBN_R.KitID) Is Null))

Hi,
did you de-select the option to have linebreaks before condition brackrts? If yes, then ther are 2 options clashing, i.e. the bracket option and the AND/OR option. The bracket option wins.

Regards
Guido

Yes. The only options checked under For Conditions are After AND/OR and Indent conditions in brackets. If you like I can send you the configuration file I am using.



Edited 1 time(s). Last edit at 03/13/2012 09:29PM by bbhulsey.

Hi,
yes please send the .cfg file to info@sqlinform.com