Full Version: Inner Join in VBA
UtterAccess Forums > Microsoft® Access > Access Forms
codyharper15
Hello,
don't have much experience with inner joins in VBA. I built three queries and ran them just in their tables and they worked fine. They go as follows:
Query1
SELECT Max(tblTSCU.TransDate) AS MaxOfTransDate
FROM tblTSCU;
Query2
SELECT tblTSCU.TransDate, Min(tblTSCU.NewBalance) AS MinOfNewBalance
FROM Query1 INNER JOIN tblTSCU ON Query1.MaxOfTransDate = tblTSCU.TransDate
GROUP BY tblTSCU.TransDate;
Query3
SELECT tblTSCU.*
FROM Query2 INNER JOIN (Query1 INNER JOIN tblTSCU ON Query1.MaxOfTransDate = tblTSCU.TransDate) ON Query2.MinOfNewBalance = tblTSCU.NewBalance;
These queries pull up the correct information, but I haven't figured out how to writ these same queries in VBA. Below I have my latest attempts:
strSQL = "SELECT Max(tblTSCU.TransDate) AS MaxOfTD FROM tblTSCU"
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

strSQL = "SELECT tblTSCU.TransDate, Min(tblTSCU.NewBalance) AS MinOfNB FROM tblTSCU " & _
"INNER JOIN tblTSCU ON tblTSCU.TransDate WHERE [TransDate] = #" & Nz(rs!MaxOfTD, 0) & "#"
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

strSQL = "SELECT * FROM tblTSCU WHERE [TransDate] = #" & rs!MaxOfTD & "# AND [NewBalance] = " & Nz(rs!MinOfNB, 0)
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
Can anyone help me write these? Thank You!!!
arnelgp
Youre on the right track, except that you have to declare first your recordset:
im rs1 as dao.recordset
dim rs2 as dao.recordset
dim rs3 as dao.recordset
set rs1 = currentdb.openrecordset (just copy your SQL from sql designer in SQL view here. Dont forget to enclosed them in double qoutes)
set rs2=.....
set rs3=....
when done, release your recordset object
set rs1 = nothing.
codyharper15
Thank you arnelqp!
thought i couldn't put queries in my SQL statements in VBA
fkegley
You can do it like this:
et rs1 = CurrentDb.OpenRecordset("QueryNameGoesHere")
You will have a problem though if the query uses a parameter. The OpenRecordset will not recognize it unless you add it to the parameters of the query.
Dim qdef As DAO.QueryDef
Set qdef = CurrentDb.QueryDefs("NameOfQuery")
qdef![Parameter Name Goes Here] = [Forms]![NameOfForm]![NameOfControl]
Example:
qdef![Forms!NameOfForm!NameOfControl] = [Forms]![NameOfForm]![NameOfControl]
It looks screwy but it works.
codyharper15
ha ha, Thanks for both of your help!!
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.