SQL from mulit select listbox not working

  • Thread starter Thread starter TK
  • Start date Start date
T

TK

I have a multi select list box I'm trying to use to get old transcripts.
Here is the sql I'm trying to use:
'**** Build SQL for gathering all records for student
strSQL = "INSERT INTO tblTempTranscripts ( StudentID, ClassID, [Year],
Term1, Term2, Term3, FinalExam, Resit ) " _
& "SELECT tblStudentGrades_Archive.StudentID,
tblStudentGrades_Archive.ClassID, tblStudentGrades_Archive.Year,
tblStudentGrades_Archive.Term1, tblStudentGrades_Archive.Term2,
tblStudentGrades_Archive.Term3, tblStudentGrades_Archive.FinalExam,
tblStudentGrades_Archive.Resit " _
& "FROM tblStudentGrades_Archive " _
& "WHERE ((tblStudentGrades_Archive.StudentID)= " & Me.StudentID &
") AND (Not (tblStudentGrades_Archive.Term1) Is Null)) OR (Not
(tblStudentGrades_Archive.Term2) Is Null)) OR (Not
(tblStudentGrades_Archive.Term3) Is Null)) OR (Not
(tblStudentGrades_Archive.FinalExam) Is Null)) AND
((tblStudentGrades_Archive.Year) = "
For Each varItem In Me.lstYearLevel.ItemsSelected
strSQL = strSQL & Me.lstYearLevel.ItemData(varItem) & " OR
((tblStudentGrades_Archive.Year)= "
MsgBox Me.lstYearLevel.ItemData(varItem)
Next varItem

'Trim the end of strSQL
strSQL = Mid(strSQL, 1, Len(strSQL) - 37) & "))"
Debug.Print strSQL
CurrentProject.Connection.Execute strSQL


The problem is in the trim I think - If I select only one item in the list,
I get a "Extra ) in query ..." error. If I take one of those out at the end
of the trim, and I select more than one item I get the "missing ) ..." erro.
Can anyone help me?
Thanks in advance.
TK
 
Hi TK

The problem is that you are adding one extra "(" for each time you go around
the loop. You can fix it (I think) by adding a ")" just before the " OR"
and finishing off with a single ")".

You seem to have gone a bit mad with your parentheses. At least half of
them are unnecessary, and make your SQL very difficult to read. For
example, you don't need to say:
.... (Not (tblStudentGrades_Archive.Term1) Is Null)...
You can leave out the inner pair:
.... (Not tblStudentGrades_Archive.Term1 Is Null) ...

Also, you can use IN for your multiselect list (and I suggest using another
string variable to construct the list):

Dim strYears as String
For Each varItem In Me.lstYearLevel.ItemsSelected
strYears = strYears & Me.lstYearLevel.ItemData(varItem) & ","
Next varItem
strSQL = strSQL & " AND (tblStudentGrades_Archive.Year IN ("
& Left(strYears, Len(strYears)-1) & "))"
 
Graham,
Thanks for the input. Now the problem is that it is giving me everyone in
the school and I can see why - I am basically telling it to give me the
records which match the studentID AND the first condition of the Term 1 not
being null - OR any other record which does not have a null value for term 2
or 3. When I created a query which worked, it was very long because it had a
line for each condition - i.e.
StudentID match AND Year match AND term not being null for all three terms.
How can I shorten that and still loop through the items selected list in
one, relatively easy SQL?
In the end of the day, I want it to give me all the records pertaining to
the chosen student and the chosen year(s), but it does need to check the
terms because let's say the student did not come to the school until term 2
or term 3, then any of the first terms could be empty while one is not and
we need to get those grades too.
The problem with using the query I built is getting the years from the items
selected date in the list box. By manually entering the years of course the
query worked great.
Any and all help will be greatly appreciated.
TK

Graham Mandeno said:
Hi TK

The problem is that you are adding one extra "(" for each time you go around
the loop. You can fix it (I think) by adding a ")" just before the " OR"
and finishing off with a single ")".

You seem to have gone a bit mad with your parentheses. At least half of
them are unnecessary, and make your SQL very difficult to read. For
example, you don't need to say:
... (Not (tblStudentGrades_Archive.Term1) Is Null)...
You can leave out the inner pair:
... (Not tblStudentGrades_Archive.Term1 Is Null) ...

Also, you can use IN for your multiselect list (and I suggest using another
string variable to construct the list):

Dim strYears as String
For Each varItem In Me.lstYearLevel.ItemsSelected
strYears = strYears & Me.lstYearLevel.ItemData(varItem) & ","
Next varItem
strSQL = strSQL & " AND (tblStudentGrades_Archive.Year IN ("
& Left(strYears, Len(strYears)-1) & "))"

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

TK said:
I have a multi select list box I'm trying to use to get old transcripts.
Here is the sql I'm trying to use:
'**** Build SQL for gathering all records for student
strSQL = "INSERT INTO tblTempTranscripts ( StudentID, ClassID, [Year],
Term1, Term2, Term3, FinalExam, Resit ) " _
& "SELECT tblStudentGrades_Archive.StudentID,
tblStudentGrades_Archive.ClassID, tblStudentGrades_Archive.Year,
tblStudentGrades_Archive.Term1, tblStudentGrades_Archive.Term2,
tblStudentGrades_Archive.Term3, tblStudentGrades_Archive.FinalExam,
tblStudentGrades_Archive.Resit " _
& "FROM tblStudentGrades_Archive " _
& "WHERE ((tblStudentGrades_Archive.StudentID)= " & Me.StudentID &
") AND (Not (tblStudentGrades_Archive.Term1) Is Null)) OR (Not
(tblStudentGrades_Archive.Term2) Is Null)) OR (Not
(tblStudentGrades_Archive.Term3) Is Null)) OR (Not
(tblStudentGrades_Archive.FinalExam) Is Null)) AND
((tblStudentGrades_Archive.Year) = "
For Each varItem In Me.lstYearLevel.ItemsSelected
strSQL = strSQL & Me.lstYearLevel.ItemData(varItem) & " OR
((tblStudentGrades_Archive.Year)= "
MsgBox Me.lstYearLevel.ItemData(varItem)
Next varItem

'Trim the end of strSQL
strSQL = Mid(strSQL, 1, Len(strSQL) - 37) & "))"
Debug.Print strSQL
CurrentProject.Connection.Execute strSQL


The problem is in the trim I think - If I select only one item in the list,
I get a "Extra ) in query ..." error. If I take one of those out at the end
of the trim, and I select more than one item I get the "missing ) ..." erro.
Can anyone help me?
Thanks in advance.
TK
Click to expand...
Click to expand...
 
Hi TK

Closely examine the output from your Debug.Print strSQL. If you can't see
the problem then copy it and paste in a reply here. I seriously suggest you
start by getting rid of unnecessary parentheses, as these can make debugging
much more difficult.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

TK said:
Graham,
Thanks for the input. Now the problem is that it is giving me everyone in
the school and I can see why - I am basically telling it to give me the
records which match the studentID AND the first condition of the Term 1 not
being null - OR any other record which does not have a null value for term 2
or 3. When I created a query which worked, it was very long because it had a
line for each condition - i.e.
StudentID match AND Year match AND term not being null for all three terms.
How can I shorten that and still loop through the items selected list in
one, relatively easy SQL?
In the end of the day, I want it to give me all the records pertaining to
the chosen student and the chosen year(s), but it does need to check the
terms because let's say the student did not come to the school until term 2
or term 3, then any of the first terms could be empty while one is not and
we need to get those grades too.
The problem with using the query I built is getting the years from the items
selected date in the list box. By manually entering the years of course the
query worked great.
Any and all help will be greatly appreciated.
TK

Graham Mandeno said:
Hi TK

The problem is that you are adding one extra "(" for each time you go around
the loop. You can fix it (I think) by adding a ")" just before the " OR"
and finishing off with a single ")".

You seem to have gone a bit mad with your parentheses. At least half of
them are unnecessary, and make your SQL very difficult to read. For
example, you don't need to say:
... (Not (tblStudentGrades_Archive.Term1) Is Null)...
You can leave out the inner pair:
... (Not tblStudentGrades_Archive.Term1 Is Null) ...

Also, you can use IN for your multiselect list (and I suggest using another
string variable to construct the list):

Dim strYears as String
For Each varItem In Me.lstYearLevel.ItemsSelected
strYears = strYears & Me.lstYearLevel.ItemData(varItem) & ","
Next varItem
strSQL = strSQL & " AND (tblStudentGrades_Archive.Year IN ("
& Left(strYears, Len(strYears)-1) & "))"

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

TK said:
I have a multi select list box I'm trying to use to get old transcripts.
Here is the sql I'm trying to use:
'**** Build SQL for gathering all records for student
strSQL = "INSERT INTO tblTempTranscripts ( StudentID, ClassID, [Year],
Term1, Term2, Term3, FinalExam, Resit ) " _
& "SELECT tblStudentGrades_Archive.StudentID,
tblStudentGrades_Archive.ClassID, tblStudentGrades_Archive.Year,
tblStudentGrades_Archive.Term1, tblStudentGrades_Archive.Term2,
tblStudentGrades_Archive.Term3, tblStudentGrades_Archive.FinalExam,
tblStudentGrades_Archive.Resit " _
& "FROM tblStudentGrades_Archive " _
& "WHERE ((tblStudentGrades_Archive.StudentID)= " &
Click to expand...
Click to expand...
Me.StudentID
& the
end
Click to expand...
Click to expand...
 
Back
Top