Running this VB program in MS-Access.

I get an "ODBC -- call failed" error when I am looking for all ssn beginning with one number (see below).

When I change the like statement to two numbers "ssn like '21%'", I do not get the error and the program runs successfully.

Any thoughts?


strsql = "SELECT ssn, activity_date, annualized_salary "
strsql = strsql + "FROM dbo_salaryhistory "
strsql = strsql + "WHERE Activity_Date >= #1/1/2004# AND "
strsql = strsql + "ssn like '2%' "
strsql = strsql + "ORDER BY ssn, activity_date "

------------

Option Compare Database
Option Explicit


Sub Main1()

Open "U:\out.txt" For Output As #1

Dim rs As ADODB.Recordset
Dim tot As Integer
Dim cnt As Integer
Dim pct As Double
Dim strsql As String
Dim old_ssn As String
Dim lo_salary As Double
Dim hi_salary As Double

MsgBox ("Begin the Program")

strsql = "SELECT ssn, activity_date, annualized_salary "
strsql = strsql + "FROM dbo_salaryhistory "
strsql = strsql + "WHERE Activity_Date >= #1/1/2004# AND "
strsql = strsql + "ssn like '2%' "
strsql = strsql + "ORDER BY ssn, activity_date "


old_ssn = "999"
cnt = 0
tot = 0
Set rs = New ADODB.Recordset

rs.ActiveConnection = CurrentProject.Connection
rs.LockType = adLockReadOnly
rs.Open strsql
rs.MoveFirst
Do Until rs.EOF


If (old_ssn <> rs!ssn) And (old_ssn <> "999") Then
If cnt >= 3 Then
tot = tot + 1
pct = ((hi_salary - lo_salary) / lo_salary)
Print #1, tot, cnt, old_ssn, FormatCurrency(lo_salary, 2), FormatCurrency(hi_salary, 2), FormatPercent(pct, 2)
End If
cnt = 0
End If

cnt = cnt + 1
old_ssn = rs!ssn
If cnt = 1 Then
lo_salary = rs!annualized_salary
End If
hi_salary = rs!annualized_salary

rs.MoveNext
Loop
rs.Close


If cnt >= 3 Then
tot = tot + 1
pct = ((hi_salary - lo_salary) / lo_salary)
Print #1, tot, cnt, old_ssn, FormatCurrency(lo_salary, 2), FormatCurrency(hi_salary, 2), FormatPercent(pct, 2)
End If

MsgBox ("End of Program")

Close #1
End Sub