Solved

SQL Join Question

Posted on 2007-12-04
18
225 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
Technology Partners: 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!

 
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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month9 days, 22 hours left to enroll

624 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