SQL syntax--getting demoralized

[middletree]

Where? Around the INNER JOINs? I don't see how that is possible, as the
parentheses will be uneven, depending on what was selected.

(watch the wrap)

strSQL = "SELECT DISTINCT Personal.FName, Personal.LName,
Personal.PreferredName, Personal.PersonalID "
strSQL = strSQL & "FROM (((Personal "
If strGiftID <> "" then
strSQL = strSQL &"INNER JOIN PersonalGift ON Personal.PersonalID
=PersonalGift.PersonalID) "
End if
If strAbilityID <> "" then
strSQL = strSQL & "INNER JOIN PersonalAbility ON Personal.PersonalID
=PersonalAbility.PersonalID) "

etc. See what I mean?

 

[TomB]

No, sorry. The way you originally had it.....


if len(strGiftID)>0 then
sWhere="PersonalGift.GiftID=" & strGiftID & " AND "
end if
etc.

strSQL=strSQL & sWhere

 

[middletree]

Oh, gosh. Dont' even know if I have that code; it's been changed so many
times.

 

[middletree]

Well, the problem got fixed. I have a firend, a former co-worker, and he
added some code fairly quickly. In anyone cares, here it is:

strSQL = "SELECT DISTINCT Personal.FName, Personal.LName,
Personal.PreferredName, Personal.PersonalID "
strSQL = strSQL & "FROM (((Personal LEFT OUTER JOIN PersonalGift ON " strSQL
= strSQL & "Personal.PersonalID =PersonalGift.PersonalID) "
strSQL = strSQL & "LEFT OUTER JOIN PersonalAbility ON Personal.PersonalID
=PersonalAbility.PersonalID) "
strSQL = strSQL & "LEFT OUTER JOIN PersonalPeople ON Personal.PersonalID=
PersonalPeople.PersonalID) "
strSQL = strSQL & "LEFT OUTER JOIN PersonalArea ON Personal.PersonalID=
PersonalArea.PersonalID"

If Len(strGiftID) Or Len(strAreaID) > 0 Or Len(strPeopleID) > 0 Or
Len(strAbility) > 0 Then
strSQL = strSQL & " Where Personal.FName = Personal.FName "
If Len(strGiftID) > 0 Then
strSQL = strSQL & " And Personal.PersonalID In (select PersonalID from
PersonalGift where GiftID = " & strGiftID & ")"
If Len(strAreaID) > 0 Then
strSQL = strSQL & " And Personal.PersonalID In (select PersonalID from
PersonalArea where AreaID = " & strAreaID & ")"
If Len(strPeopleID) > 0 Then
strSQL = strSQL & " And Personal.PersonalID In (select PersonalID from
PersonalPeople where PeopleID = " & strPeopleID & ")" If Len(strAbilityID) >
0 Then
strSQL = strSQL & " And Personal.PersonalID In (select PersonalID from
PersonalAbility where AbilityID = " & strAbilityID & ")"

Thanks, everybody, for your time!