Adding Month and Year to code

  • Thread starter Thread starter Jean
  • Start date Start date
J

Jean

I have this code and currently it does it at one month at
a time. I now need it to look to see if the month and
year per 2 of the catagories match then subtract the Comm
amounts. Kind of like this

if mnth and year with the catagory of NonFocus =
mnth and year with catagory wild then (subtract the
COMM_AMT - wcamt) Right now it is only one month at a time
so it works. Any suggestions would be great. THANKS..





Dim sql
Dim rs As Recordset
Dim ter
Dim wcamt
Dim xsql
Dim xrs As Recordset

sql = "select * from tbl_comm_summary where CATEGORY
='Wildcard'"
Set rs = CurrentDb.OpenRecordset(sql)

Do Until rs.EOF

ter = rs!TERR
wcamt = rs!COMM_AMT

xsql = "select * from tbl_comm_summary
where CATEGORY='Non Focus Firm' and TERR='" & ter & "'"
Set xrs = CurrentDb.OpenRecordset(xsql)

xrs.Edit
xrs!COMM_AMT = xrs!COMM_AMT - wcamt
xrs.Update

xrs.Close
Set xrs = Nothing

rs.MoveNext

Loop
rs.Close
Set rs = Nothing
 
Hi Jean

Consider having two variables for each recordset; one for the month and the other for the year. Use the functions month() and year() to extract the month value and year value of each recordset date. Then compare the two sets of variables

Luc
Jonatha

----- Jean wrote: ----

I have this code and currently it does it at one month at
a time. I now need it to look to see if the month and
year per 2 of the catagories match then subtract the Comm
amounts. Kind of like thi

if mnth and year with the catagory of NonFocus
mnth and year with catagory wild then (subtract the
COMM_AMT - wcamt) Right now it is only one month at a time
so it works. Any suggestions would be great. THANKS.





Dim sq
Dim rs As Recordse
Dim te
Dim wcam
Dim xsq
Dim xrs As Recordse

sql = "select * from tbl_comm_summary where CATEGORY
='Wildcard'
Set rs = CurrentDb.OpenRecordset(sql

Do Until rs.EO

ter = rs!TER
wcamt = rs!COMM_AM

xsql = "select * from tbl_comm_summary
where CATEGORY='Non Focus Firm' and TERR='" & ter & "'
Set xrs = CurrentDb.OpenRecordset(xsql

xrs.Edi
xrs!COMM_AMT = xrs!COMM_AMT - wcam
xrs.Updat

xrs.Clos
Set xrs = Nothin

rs.MoveNex

Loo
rs.Clos
Set rs = Nothin
 
Back
Top