syntax issues

[middletree]

I've seen posts here and elsewhere which read something along the lines of
"PULLNG MY HAIR OUT!!!!!" or "HELLLLPPP!". Well, I know that kind of
subject line isn't descriptive, but I sure can relate right now. I've been
struggling for days--days!!-- on this one simple query. I really need to
get past this thing and move on. Please help.

I have a classic ASP page, and it gives you 4 dropdowns. You can select any
or none of them. Each of them is built by an ACCESS table. These 4 tables
are union tables; they have just 2 fields: the PK from the master table,
called Personal, and one of the 4 static tables that I call People, Area,
Ability, Gift.

Anyway, when someone selects a value from one or more of the dropdowns, the
next page grabs the values and puts them into variables (strGiftID,
strAbilityID, strAreaID, and strPeopleID). This is where I am running into
problems. If you see my previous threads on this, you'll see lots of
suggestions, and I have tried every one of them.

Here's my current code:

strSQL = "SELECT P.Fname, P.LName, P.PreferredName, P.PersonalID FROM
Personal P "
strSQL = strSQL & "INNER JOIN PersonalGift PG ON P.PersonalID =
PG.PersonalID "
strSQL = strSQL & "INNER JOIN PersonalArea PA ON P.PersonalID =
PA.PersonalID "
strSQL = strSQL & "INNER JOIN PersonalAbility PAB ON P.PersonalID =
PAB.PersonalID "
strSQL = strSQL & "INNER JOIN PersonalPeople PP ON P.PersonalID =
PP.PersonalID "

If strGiftID <> "" Then
strWhere = "GiftID = "&strGiftID&" AND "
End if
If strAreaID <> "" Then
strWhere = strWhere & "AreaID = "&strAreaID&" AND "
End if
If strAbilityID <> "" Then
strWhere = strWhere & "AbilityID = "&strAbilityID&" AND "
End if
If strPeopleID <> "" Then
strWhere = strWhere & "PeopleID = "&strPeopleID&" AND "
End if

If Len(strWhere) > 0 Then
' Remove the last AND
strWhere = Left(strWhere, Len(strWhere) - 5)
strSQL = strSQL & "WHERE " & strWhere
End If
----------------------------------------------

Here's the error:

'P.PersonalID = PG.PersonalID INNER JOIN PersonalArea PA ON P.PersonalID =
PA.PersonalID INNER JOIN PersonalAbility PAB ON P.PersonalID =
PAB.PersonalID INNER JOIN PersonalPeople PP ON P.PersonalID =
PP.PersonalID'.
/grace/list.asp, line 64

(where line 64 is the line which executes the SQL statement)


Please, please let me know what you see that I am doing wrong.

TIA

 

[John Vinson]

Here's the error:

'P.PersonalID = PG.PersonalID INNER JOIN PersonalArea PA ON P.PersonalID =
PA.PersonalID INNER JOIN PersonalAbility PAB ON P.PersonalID =
PAB.PersonalID INNER JOIN PersonalPeople PP ON P.PersonalID =
PP.PersonalID'.
/grace/list.asp, line 64

(where line 64 is the line which executes the SQL statement)


Please, please let me know what you see that I am doing wrong.

Leaving out the parentheses that Access requires in multiple-table
INNER JOIN clauses.

Try creating this Query in Access in the query grid, and then going to
SQL view to see how it parenthesizes the INNER JOINs. I know, I know -
it's not ANSI standard SQL and your query is; but Access just doesn't
conform to ANSI standard, and needs its parentheses.

 

[middletree]

Thanks, but that didn't work. It was already suggested, and I tried
everything that was suggested. Believe me.

In this case, I don't recall why it didn't work. Perhaps because of the IF
thing.

 

[Dirk Goldgar]

I've seen posts here and elsewhere which read something along the
lines of "PULLNG MY HAIR OUT!!!!!" or "HELLLLPPP!". Well, I
know that kind of subject line isn't descriptive, but I sure can
relate right now. I've been struggling for days--days!!-- on this one
simple query. I really need to get past this thing and move on.
Please help.

I have a classic ASP page, and it gives you 4 dropdowns. You can
select any or none of them. Each of them is built by an ACCESS table.
These 4 tables are union tables; they have just 2 fields: the PK from
the master table, called Personal, and one of the 4 static tables
that I call People, Area, Ability, Gift.

Anyway, when someone selects a value from one or more of the
dropdowns, the next page grabs the values and puts them into
variables (strGiftID, strAbilityID, strAreaID, and strPeopleID). This
is where I am running into problems. If you see my previous threads
on this, you'll see lots of suggestions, and I have tried every one
of them.

Here's my current code:

strSQL = "SELECT P.Fname, P.LName, P.PreferredName, P.PersonalID FROM
Personal P "
strSQL = strSQL & "INNER JOIN PersonalGift PG ON P.PersonalID =
PG.PersonalID "
strSQL = strSQL & "INNER JOIN PersonalArea PA ON P.PersonalID =
PA.PersonalID "
strSQL = strSQL & "INNER JOIN PersonalAbility PAB ON P.PersonalID =
PAB.PersonalID "
strSQL = strSQL & "INNER JOIN PersonalPeople PP ON P.PersonalID =
PP.PersonalID "

If strGiftID <> "" Then
strWhere = "GiftID = "&strGiftID&" AND "
End if
If strAreaID <> "" Then
strWhere = strWhere & "AreaID = "&strAreaID&" AND "
End if
If strAbilityID <> "" Then
strWhere = strWhere & "AbilityID = "&strAbilityID&" AND "
End if
If strPeopleID <> "" Then
strWhere = strWhere & "PeopleID = "&strPeopleID&" AND "
End if

If Len(strWhere) > 0 Then
' Remove the last AND
strWhere = Left(strWhere, Len(strWhere) - 5)
strSQL = strSQL & "WHERE " & strWhere
End If
----------------------------------------------

Here's the error:

'P.PersonalID = PG.PersonalID INNER JOIN PersonalArea PA ON
P.PersonalID = PA.PersonalID INNER JOIN PersonalAbility PAB ON
P.PersonalID = PAB.PersonalID INNER JOIN PersonalPeople PP ON
P.PersonalID = PP.PersonalID'.
/grace/list.asp, line 64

(where line 64 is the line which executes the SQL statement)


Please, please let me know what you see that I am doing wrong.

Is there no error number or message? Maybe you posted that in some
other thread, but I'm reading this one.

Does it fail regardless of whether anything is selected in the various
dropdowns? Does it work if nothing is selected? Does it work if
something is selected in every dropdown? Is there any combination of
dropdown selections that works?