Solved

SQL Join Question

Posted on 2007-12-04
18
217 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
  • 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

747 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now