Solved

SQL Join Question

Posted on 2007-12-04
18
223 Views
Last Modified: 2008-07-03
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

0
Comment
Question by:Tony789
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 4
  • +2
18 Comments
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 20407423
What difficulty are you experiencing?
0
 

Author Comment

by:Tony789
ID: 20407504
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
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 20407518
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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 76

Expert Comment

by:GrahamSkan
ID: 20407531
Crossed post. So the quotes are necessary.
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 20407539
Which line gives the error?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 20408396
More information.
0
 
LVL 12

Expert Comment

by:jkaios
ID: 20408480
I believe the error occurs on line 10:

   FirmSubTemp1 = ab.Fields("FirmSub")

Does the column name "FirmSub" actually exist in the Correspondents table?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 20409088
Then it would have failed on the ab.Open, unless of course the questions is using (God forbid) On Error Resume Next
0
 
LVL 2

Expert Comment

by:andrewneely
ID: 20411421
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
 

Author Comment

by:Tony789
ID: 20411708
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
 
LVL 2

Expert Comment

by:andrewneely
ID: 20411792
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
 

Author Comment

by:Tony789
ID: 20412023
MSHFlexGrid3.TextMatrix(intj, 2) = Var1.Fields("UserID").Value
0
 

Author Comment

by:Tony789
ID: 20412087
I changed the recordset to Var1.
0
 

Author Comment

by:Tony789
ID: 20412232
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
 

Author Comment

by:Tony789
ID: 20412348
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
 
LVL 2

Accepted Solution

by:
andrewneely earned 250 total points
ID: 20412441
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
 

Author Comment

by:Tony789
ID: 20412555
IT WORKS!!  Thank you very much.

0
 
LVL 2

Expert Comment

by:andrewneely
ID: 20412667
Your welcome.  Glad to help.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question