Joins

I have two tables, one called users and the other resources.
I'd like to select several fields from users and one from Resource (called ResourceID) based on certain criteria.

My sql statement looks like this:

gstrSQL = "Select UserNum, ResourceID, UserFName, UserLName, Password, SecurityLevel, email, Telephone, ext, UserID from Users U, Resource R where "

If UserNum <> "" Then
gstrSQL = gstrSQL & "UserID='" & UserNum & "' And u.userfname = r.fname and u.userlname = r.lname"
Else
gstrSQL = gstrSQL & "UserID='" & UserID & "' And u.userfname = r.fname and u.userlname = r.lname"
End If

Before adding "ResourceID" to the selection criteria, this statement was working just fine. It's probably a silly mistake but I can't figure it out.
Thanks.You didn't let us know the DB you're working on, nor the error itself ...

In Oracle you can NOT name your table "Resource" as it is reserved word.

However, if your DB allows it, try to rewrite the query in a way to put table aliases in front of table columns, i.e.

SELECT u.UserNum, r.ResourceID, u.UserFName, ...
FROM Users U, Resource R
WHERE ...

Perhaps your tables contain the same column names and, if you don't explicitly say the table you are selecting its value from, the column becomes ambigously defined.