looping condition

[zulhimi89]

Hi experts,

I'm using vba to do my coding. I have to check whether the data exists in the db or not. so I'd done this to check it.

Set rs = New ADODB.Recordset
With rs
.Open "pr_q", cn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
curr_year = Year(Date)
q = 4
.MoveLast
.Find "Q=" & q, , adSearchBackward
End With

* pr_q is the query name & "Q" is the field name

If rs("Q") = " & q & " & rs("Year") = " & curr_year & " Then
Set rs = New ADODB.Recordset
With rs
.Open "select sector, year, sum(val(prcompany))/count(sector) as Mean, sum(val(prcompany))/2 as Median from pr_q where year=" & curr_year & " and Q=" & q - 3 & " group by year, sector order by sector", cn

- sequence of action-


ElseIf rs("Q") = " & q-1 & " & rs("Year") = " & curr_year & " Then
Set rs = New ADODB.Recordset
With rs
.Open "select sector, year, sum(val(prcompany))/count(sector) as Mean, sum(val(prcompany))/2 as Median  from pr_q where year=" & curr_year & " and q= 'Q3' group by year, sector order by sector", cn

-sequence of action-

Else
End if

'===================================================

The problem is, it doesn't go into my loop. I'd check my condition few times, but still can't find the solution. Please help me.. :( thank you so much.

[AndreRet]

'Firstly, I would minimise or clean up my code us follow...

Dim rsSearchx As String, rsSearchy As String

rsSearchx = "select sector, year, sum(val(prcompany))/count(sector) as Mean, sum(val(prcompany))/2 as Median from pr_q where year=" & curr_year & " and Q=" & q - 3 & " group by year, sector order by sector"
rsSearchy = "select sector, year, sum(val(prcompany))/count(sector) as Mean, sum(val(prcompany))/2 as Median  from pr_q where year=" & curr_year & " and q= 'Q3' group by year, sector order by sector"

Set rs = New ADODB.Recordset
With rs
.Open "pr_q", cn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
curr_year = Year(Date)
q = 4
.MoveLast
.Find "Q=" & q, , adSearchBackward
End With
 
' pr_q is the query name & "Q" is the field name
 
If rs("Q") = " & q & " & rs("Year") = " & curr_year & " Then
'####Change your recordset name, you already have an open recordset called rs to do the searching.
Set rsSearch = New ADODB.Recordset
With rs
.Open rsSearchx, cn
 
'- sequence of action-
 
 
ElseIf rs("Q") = " & q-1 & " & rs("Year") = " & curr_year & " Then
Set rsSearch = New ADODB.Recordset
With rs
.Open rsSearchy, cn
 
'-sequence of action-
 
Else
End If

'I hope this was your problem, thye naming of rs...

[zulhimi89]

thank you so much andre !! i thought when i re-set the rs, i dont have to name it differently.. :P thx again ;)

[AndreRet]

Only a pleasure. Happy coding.