Hi to All! can anyone help me w/this code snippets?
I have a problem dealing with sql queries....I need to compare 2 queries to find unmatch value....I have a "Profile" table that contains Period,Name,RankCode...i want to copy data from period 1 to period 2 & find unmatch value......given that rank can be change before copying...here is what i did......
dim dbs as Database
dim rst1 as Recordset
dim rst2 as Recordset
dim rstcompare as Recordset
dim qry as String
dim dot as String
dim per as String
* "dot" refers to old period while "per" refers to new period
qry = "Select * from Profile Where Period='" + dot + "' ; "
Set rst1 = dbs.OpenRecordset(qry)
qry = "Select * from Profile Where Period='" + per + "' ; "
Set rst2 = dbs.OpenRecordset(qry)
qry = "SELECT rst2.BRUCode, rst2.NTP, rst2.RankCode, rst1.RankCode FROM rst2 "
qry = qry + "LEFT JOIN rst1 ON (rst2.RankCode = rst1.RankCode) AND (rst2.Name = rst1.Name) AND (rst2.BRUCode = rst1.BRUCode)"
qry = qry + "WHERE (((rst1.RankCode) Is Null)); "
Set rstcompare = dbs.OpenRecordset(qry)
* is my code valid? since i can't assign a fixed period in my query criteria, i substituted rst1 and rst2 to compare old & new period...
i'm getting error when setting rstcompare, it doesn't accept rst1 & rst2 as a substitute query....how can i resolve this prob? thanks for any help!
by the way, i'm using access 97
I have a problem dealing with sql queries....I need to compare 2 queries to find unmatch value....I have a "Profile" table that contains Period,Name,RankCode...i want to copy data from period 1 to period 2 & find unmatch value......given that rank can be change before copying...here is what i did......
dim dbs as Database
dim rst1 as Recordset
dim rst2 as Recordset
dim rstcompare as Recordset
dim qry as String
dim dot as String
dim per as String
* "dot" refers to old period while "per" refers to new period
qry = "Select * from Profile Where Period='" + dot + "' ; "
Set rst1 = dbs.OpenRecordset(qry)
qry = "Select * from Profile Where Period='" + per + "' ; "
Set rst2 = dbs.OpenRecordset(qry)
qry = "SELECT rst2.BRUCode, rst2.NTP, rst2.RankCode, rst1.RankCode FROM rst2 "
qry = qry + "LEFT JOIN rst1 ON (rst2.RankCode = rst1.RankCode) AND (rst2.Name = rst1.Name) AND (rst2.BRUCode = rst1.BRUCode)"
qry = qry + "WHERE (((rst1.RankCode) Is Null)); "
Set rstcompare = dbs.OpenRecordset(qry)
* is my code valid? since i can't assign a fixed period in my query criteria, i substituted rst1 and rst2 to compare old & new period...
i'm getting error when setting rstcompare, it doesn't accept rst1 & rst2 as a substitute query....how can i resolve this prob? thanks for any help!
by the way, i'm using access 97
