Inner Join property not found...

[SwordMonkey]

Dim stDocName As String
stDocName = "rpt_rev_sum"

Dim db As Database
Dim dbf As QueryDef
Dim records As DAO.Recordset
Dim BPDef As TableDef
Dim BPRecs As DAO.Recordset

Dim period As Long
Dim strQry As String

Set db = CurrentDb()
Set dbf = db.QueryDefs("rpt_rev_sum&quot
Set BPDef = db.TableDefs("BP&quot
Set BPRecs = BPDef.OpenRecordset
/* ^ This was just me making sure BP.BPNumber actually existed, and it did.

dbf.SQL = "select distinct pd_no from deltek.rpt_rev_sum where fy_cd = '2003';"

Set records = dbf.OpenRecordset

records.MoveFirst
records.MoveLast
period = records!pd_no

strQry = "select proj_id,pd_no,fy_cd,tgt_itd_costs FROM deltek.rpt_rev_sum " & _
"WHERE fy_cd = '2003' AND pd_no = '" & period & "' " & _
"INNER JOIN BPRecs ON rpt_rev_sum.proj_id = BPRecs.BPNumber;"
dbf.SQL = strQry

DoCmd.OpenQuery stDocName, acNormal, acEdit

Didn't work if I did INNER JOIN BP, or doing it on BPRecs as a recordset containing all of the BP table. When running it says property not found.

For background, rpt_rev_sum is a SQL pass-through query to Oracle and BP is an Access table.

 

[Norris68]

The INNER JOIN should come before the WHERE criteria. It thinks the INNER JOIN statement is part of your criteria.

 

[SwordMonkey]

Changed it to this:

strQry = "select proj_id,pd_no,fy_cd,tgt_itd_costs FROM deltek.rpt_rev_sum " & _
"INNER JOIN BPRecs ON rpt_rev_sum.proj_id = BPRecs.BPNumber " & _
"WHERE fy_cd = '2003' AND pd_no = '" & period & "';"""
dbf.SQL = strQry

Still no luck, still says property not found.

 

[danvlas]

The part:
FROM deltek.rpt_rev_sum
looks odd...it has a dot that shouldn't be there...could that be the problem?

HTH




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html