[RESOLVED] Problem with complex SQL string

[longwolf]

I'm using VB6 with an Access DB.
In one of my subs I have a complex SQL string.
It is pulling up records but it's not returning one of the fields I need.

rsUseA.Fields("TextID") is getting an 'item cannot be found' error.
Which seems strange since the TextID field is being used in the condition statement of the SQL string.

Any idea what's wrong?
Here's most of the code:

ReDim Preserve g_tTexts(0) 'clear the utd
    sSQL = "SELECT * FROM tblSelectedGroups SG, tblSelectedTexts ST, tblTexts T " & _
            "WHERE (ST.UserID = " & CStr(g_lUserID) & _
            ") AND (SG.UserID = " & CStr(g_lUserID) & _
            ") AND (ST.USE = True) " & _
            "AND (ST.TextID = T.TextID) " & _
            "AND (SG.GroupID = T.GroupID)"
    Set rsUseA = New ADODB.Recordset
    rsUseA.Open sSQL, cn, adOpenKeyset, adLockPessimistic, adCmdText
    If Not rsUseA.EOF Then
        rsUseA.MoveFirst
        Do Until rsUseA.EOF
            g_tTexts(lCnt).TextID = rsUseA.Fields("TextID")
            g_tTexts(lCnt).Msg = rsUseA.Fields("Text")
            g_tTexts(lCnt).dtEarliest = rsUseA.Fields("EarliestStart")
            g_tTexts(lCnt).dtLatest = rsUseA.Fields("LatestRun")

I'm getting the error in line 13.
All of the rest of the rsUseA.Fields are loading without a problem.

[Mark Gambo]

It appears that you have the same a Field called "TextID" in the tblSelectedGroups, tblSelectedTexts and tblTexts Tables. You will need to explicitly specify table and field you want to use in your SQL String.

[Mark Gambo]

Try something like this:

sSQL = "SELECT *, ST.UserID as TargetField FROM tblSelectedGroups SG, tblSelectedTexts ST, tblTexts T " & _
            "WHERE (ST.UserID = " & CStr(g_lUserID) & _
            ") AND (SG.UserID = " & CStr(g_lUserID) & _
            ") AND (ST.USE = True) " & _
            "AND (ST.TextID = T.TextID) " & _
            "AND (SG.GroupID = T.GroupID)"

and then try this:

rsUseA.Fields("TargetField")

[brucevde]

Since you are using Access you can access fields with the same name using the TableAlias.FieldAlias syntax, in your case

rsUseA.Fields("ST.TextID")
rsUseA.Fields("T.TextID")

Note this is an Access Jet Provider solution, other database providers do not include the TableAlias as part of the Recordset.Field.Name.

[Mark Gambo]

Note this is an Access Jet Provider solution, other database providers do not include the TableAlias as part of the Recordset.Field.Name.

@ Bruce - It would make life alot easier!

[longwolf]

Thx Mark and Bruce!

Both methods work and I'm glad to learn both of them.

(I did have to make a sm change to Mark's code, ST.UserID to ST.TextID)