Code Does not skip

  • Thread starter Thread starter Heather
  • Start date Start date
H

Heather

HI,
I am not sure how to stop this from happing. The code
looks at Territory, month, year, and amount from a table
call tbl_ivo_ytd. So each territory could have up to 5
categories, Focus, Non Focus, BD Focus Firm, and
Enterprise. The data looks like the following. What is
happing is that VT02 is pulling VT01 amt and it errors out
because it is saying VT02 has amt 9788 = 0. VT02 Has no
activity for 2004 it should be skiped. I will post the
code and I hope all of it gets posted. THANKS.

CATEGORY TERR1 AMT TRADE_MONTH TRADE_YEAR
Enterprise VT01 56720 1 2004
BD Focus Firm VT01 284938.71 1 2004
Non-Focus VT01 9788 1 2004
Non-Focus VT02 11354.86 8 2003

Starts Code

Public Sub ivo_sum_adj(m, y)
Dim xsql
Dim xrs As Recordset
Dim ter
Dim cat
Dim mamt As Double
Dim yamt As Double


sql = "select TERR1,CATEGORY from tbl_ivo_ytd group by
TERR1,CATEGORY order by TERR1"
Set rs = CurrentDb.OpenRecordset(sql)

Do Until rs.EOF
ter = rs!TERR1
cat = rs!category

xsql = "select AMT from tbl_ivo_summary where category='"
& cat & "' and TERR1='" & ter & "'"
Set xrs = CurrentDb.OpenRecordset(xsql)

If xrs.EOF = True And xrs.BOF = True Then
mamt = 0
Else
mamt = xrs!amt
End If
xrs.Close
Set xrs = Nothing

xsql = "select sum(AMT)as am from tbl_ivo_ytd where
category='" & cat & "' and TERR1='" & ter & "' and
TRADE_MONTH<=" & m & " and TRADE_YEAR=" & y & ""
Set xrs = CurrentDb.OpenRecordset(xsql)

If xrs.EOF = True And xrs.BOF = True Then

Else
ERRORS OUT HERE!!! yamt = xrs!am
End If
xrs.Close
Set xrs = Nothing

xsql = "select * from tbl_rpt_sale_goal where TERR1='" &
ter & "' and category='" & cat & "'"
Set xrs = CurrentDb.OpenRecordset(xsql)

If Not xrs.EOF Then
xrs.Edit
xrs!sales = xrs!sales - mamt
xrs!ytd_sales = xrs!ytd_sales - yamt
xrs.Update
End If
xrs.Close
Set xrs = Nothing
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
End Sub
 
could be that xrs!am have NULL value, but yamt accept only double
try:
yamt = nz(xrs!am,0)
 
Back
Top