SQL error

[nepotist]

Hello I am programming in ArcGIS (using VBA) and using Access as a database.

I have a function that is called to generate a SQL string and this call is made by few other function. It works fine when all other functions call it except for one, which pops an error


Error Number 3075; Description Extra ) in query expression
'((((tblConcurrencySegments.SegmentID)=)))'.

Here is the function that builds the SQl statement

Public Function BuildSQLInfo() As String
Dim sSQLInfo As String
Dim sSegListInfo As String
Dim j As Integer
sSegListInfo = "(tblConcurrencySegments.SegmentID)=" & frmInfoList.ListBox.List(0)
j = 1

Do While j < frmList.ListBox.ListCount
sSegListInfo = sSegListInfo & " OR (tblConcurrencySegments.SegmentID) = " & frmInfoList.ListBox.List(j)
j = j + 1
Loop
MsgBox sSegListInfo
sSQLInfo = "SELECT tblConcurrencySegments.SegmentID, tblConcurrencySegments.RoadName, tblConcurrencySegments.From, tblConcurrencySegments.To, tblProjectList.ConcurrencyID, tblProjectList.ProjectName, tblTripDiary.LinkTrips " _
& "FROM (tblConcurrencySegments INNER JOIN tblTripDiary ON tblConcurrencySegments.SegmentID = tblTripDiary.LinkNumber) INNER JOIN tblProjectList ON tblTripDiary.ConcurrencyID = tblProjectList.ConcurrencyID " _
& "WHERE (((" & sSegListInfo & ")));"
'**********************************************

BuildSQLInfo = sSQLInfo
Debug.Print BuildSQLInfo
MsgBox BuildSQLInfo
End Function


the out put for this function is as follows

SELECT tblConcurrencySegments.SegmentID, tblConcurrencySegments.RoadName, tblConcurrencySegments.From, tblConcurrencySegments.To, tblProjectList.ConcurrencyID, tblProjectList.ProjectName, tblTripDiary.LinkTrips FROM (tblConcurrencySegments INNER JOIN tblTripDiary ON tblConcurrencySegments.SegmentID = tblTripDiary.LinkNumber) INNER JOIN tblProjectList ON tblTripDiary.ConcurrencyID = tblProjectList.ConcurrencyID WHERE ((((tblConcurrencySegments.SegmentID)=)));


I am not sure what is causing the error and the soultion it. I have checked the parenthesis and they look good.
Can anyone see something that I am not able to see??

[Movian]

This is probably really stupid on my part...

but at the end you have
((((tblConcurrencySegments.SegmentID)=)));
What is the = sign there for ?

you apear to have the correct number of parenthesis however im confused with this as you are comparing (tblConcurrencySegments.SegmentID) against nothing..... inside brackets....

[nepotist]

That is actually a variable that would get the value form a list box.
Makes sense??

[hansup]

sSegListInfo = "(tblConcurrencySegments.SegmentID)=" & frmInfoList.ListBox.List(0)
What is that line intended to do?

What is frmInfoList.ListBox?

What does this message box display?MsgBox "frmInfoList.ListBox.List(0): '" & frmInfoList.ListBox.List(0) & "'"

[nepotist]

I figured out the problem. The code works fine it was approach in the ArcGIS that was coded wrong.
Once again thanks Movian and hansup!