SQL Join Question

FirmSubVar holds a value that is found in table UserID in the FIRMSUB column.
Table tbl_Closed has USERID column.
Table User ID has USERID and FIRMSUB columns.

I want to join tbl_Closed and UserID tables based on the FIRMSUB value and date.

Please let me know if you need more information.
Dim FirmVar As Variant
 
strconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\TechLog.mdb"
Set conn = New adodb.Connection
conn.Open strconn
 
strsql1 = "Select FirmSub from Correspondents Where [Firm Name] = '" & FirmVar & "'"
Set ab = New adodb.Recordset
ab.Open strsql1, conn, adOpenDynamic, adLockOptimistic
FirmSubTemp1 = ab.Fields("FirmSub")
 
Diff = DateAdd("m", -2, Now())
 
strsql = "SELECT tbl_Closed.*, [User ID].UserID, [User ID].FirmSub From tbl_Closed INNER JOIN [User ID] ON tbl_Closed.UserID = [User ID].UserID Where [User ID].FirmSub = '" & FirmSubTemp1 & "' and tbl_Closed.Time_Close >= #" & Diff & "#"
Set rs = New adodb.Recordset
rs.Open strsql, conn, adOpenDynamic, adLockOptimistic 

Open in new window

Tony789Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
andrewneelyConnect With a Mentor Commented:
Tony789,
In short, yes, your matching the tables on a common value.  You can do the inner join the old way to see what it is doing.  For example,

select [user id].*, tbl_closed.* from [user id],tbl_closed where [user id].userid=tbl_closed.userid

does the same thing as the inner join statement.  As a matter of fact, you can select and join more than two tables using this method.

As for the error, your UserID is not qualified. You have two fields named UserID, one from the tbl_closed table and one from the [user id] table.  The computer may be getting confused as to which one to use. Try doing a
MSHFlexGrid3.TextMatrix(intj, 2) = Var1.Fields("tbl_Closed.UserID").Value.  See if that helps.


Also, as future reference, embedding spaces into field names is a bad thing.  It will cause you trouble in the future.  
0
 
GrahamSkanRetiredCommented:
What difficulty are you experiencing?
0
 
Tony789Author Commented:
I get the error "Item cannot be found in the collection..."  I checked and the item is there.

FirmSubTemp1 can either be number or numbers+characters.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
GrahamSkanRetiredCommented:
I don't think you need the # signs in the SQL. They indicate that the date is a string in the American format-
#MM/DD/YY#.

Also, you only need the single quotes here:  '" & FirmSubTemp1 & "' if the USerID is as string
0
 
GrahamSkanRetiredCommented:
Crossed post. So the quotes are necessary.
0
 
GrahamSkanRetiredCommented:
Which line gives the error?
0
 
Anthony PerkinsCommented:
More information.
0
 
jkaiosIT DirectorCommented:
I believe the error occurs on line 10:

   FirmSubTemp1 = ab.Fields("FirmSub")

Does the column name "FirmSub" actually exist in the Correspondents table?
0
 
Anthony PerkinsCommented:
Then it would have failed on the ab.Open, unless of course the questions is using (God forbid) On Error Resume Next
0
 
andrewneelyCommented:
Item cannot be found in the collection..."  ususlly means that either you misspelled the column name OR you did a select coulmn1, coulmn2 from database, and then referenced column3 (i.e. referenced a column that was not returned in the query)
0
 
Tony789Author Commented:
Thank you all for your input.

"Does the column name "FirmSub" actually exist in the Correspondents table?"
Yes.  I used a MsgBox and it returns the correct value.

I checked the spelling and made sure all the columns exist in the database.

"you did a select coulmn1, coulmn2 from database, and then referenced column3 (i.e. referenced a column that was not returned in the query)"

andrew:  Can you please elaborate on this?

Thanks
0
 
andrewneelyCommented:
I've had this error many, many times.  The first thing I suspect is that I somehow misspelled something.  What I do is go to the database and copy the field name and then paste the field name directly in the code.  This way, I am sure I'm not messing it up.

The other thing that could be going on is say you did a select statement in your code that selected two columns.  The recordset returned will have only those two columns.  I've been looking at the database, and put a reference in for a third column that did not appear in the select statement.  (example "select c1,c2 from database where something=true" then in code "somestring=rs.fields("c3").value

What line is the error being thrown?
0
 
Tony789Author Commented:
MSHFlexGrid3.TextMatrix(intj, 2) = Var1.Fields("UserID").Value
0
 
Tony789Author Commented:
I changed the recordset to Var1.
0
 
Tony789Author Commented:
strconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\TechLog.mdb"
Set conn = New adodb.Connection
conn.Open strconn

strsql1 = "Select FirmSub from Correspondents Where [Firm Name] = '" & FirmVar & "'"
Set ab = New adodb.Recordset
ab.Open strsql1, conn, adOpenDynamic, adLockOptimistic
FirmSubTemp1 = ab.Fields("FirmSub")

Diff = DateAdd("m", -2, Now())

strsql4 = "SELECT tbl_Closed.*, [User ID].UserID, [User ID].FirmSub From tbl_Closed INNER JOIN [User ID] ON tbl_Closed.UserID = [User ID].UserID Where [User ID].FirmSub = '" & FirmSubTemp1 & "' and tbl_Closed.Time_Close >= #" & Diff & "#"
Set Var1 = New adodb.Recordset
Var1.Open strsql4, conn, adOpenDynamic, adLockOptimistic
0
 
Tony789Author Commented:
The statement below:
INNER JOIN [User ID] ON tbl_Closed.UserID = [User ID].UserID

Is this joining the tables USER ID and TBL_CLOSED based on identical UserIDs?

0
 
Tony789Author Commented:
IT WORKS!!  Thank you very much.

0
 
andrewneelyCommented:
Your welcome.  Glad to help.
0
All Courses

From novice to tech pro — start learning today.