Solved

SQL Join Question

Posted on 2007-12-04
18
218 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
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…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

911 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

25 Experts available now in Live!

Get 1:1 Help Now