Function not working after conversion

[lauritz]

Hi,

I have converted my Access 97 database to Access 2000 and I now have a function behaving oddly. It works fine in 97 but now returns inaccurate results in 2000. Function below:
Function getLifeExceedenceList()
Dim rsEngineList, rsEngineComponent As DAO.Recordset
Dim idx, x As Integer
Dim engineList(50) As String
Dim EngineID As String
Dim exceedenceList As String

Set rsEngineList = CurrentDb.OpenRecordset("select * from engine")

idx = 1
With rsEngineList
.MoveLast
.MoveFirst
While Not .EOF
engineList(idx) = .Fields("engineId")
idx = idx + 1
.MoveNext
Wend
End With

For x = 1 To 50 '50 is an arbitary number, we will bail if we don't need that many
EngineID = engineList(x)
Set rsEngineComponent = CurrentDb.OpenRecordset("SELECT * FROM enginecomponent WHERE engineid = '" & EngineID & "'", dbOpenDynaset)
With rsEngineComponent
If .RecordCount > 0 Then
.FindFirst ("cyclesRemaining <= '" & 0 & "'")
If Not .NoMatch Then
If exceedenceList = "" Then
exceedenceList = .Fields("engineId")
Else
exceedenceList = exceedenceList & "," & .Fields("engineId")
End If
End If
.MoveFirst
.FindFirst ("hoursRemaining <= '" & 0 & "'")
If Not .NoMatch Then
If exceedenceList = "" Then
exceedenceList = .Fields("engineId")
Else
exceedenceList = exceedenceList & "," & .Fields("engineId")
End If
End If
End If
End With
Next x
getLifeExceedenceList = exceedenceList
End Function

Any help gratefully accepted.

 

[ZmrAbdulla]

Set a reference to DAO x.xxx library.
Access 2000 and higher is default to ADO but the previous are DAO.

________________________________________________________
Zameer Abdulla
Help to find Missing people
Even a thief takes ten years to learn his trade.

 

[lauritz]

Thanks Zameer. I have the reference already. It won't compile with out it. Any other ideas?

 

[ZmrAbdulla]

Can you give me sample results from 97 and 2000 versions

________________________________________________________
Zameer Abdulla
Help to find Missing people
Even a thief takes ten years to learn his trade.

 

[lauritz]

Hi Zameer. The A2000 version returns only one engine with a negative or zero remaining life whilst the A97 version returns 3.

A2000
Engine: Eng1234567 - 0 hours remaining.

A97
Engine: Eng1234567 - 0 hours remaining
59K - -27.5 hours remaining
60K - -30 hours remaining.

Any ideas??

 

[RoyVidar]

I think I've read somewhere that sort order based on text is changed between Access 97 and later versions, but I don't know (perhaps the minus sign "-" is sorted in another way in later versions?).

What it looks like in your code, is that you compare numbers and text (cyclesRemaining and hoursRemaining seems to be text fields containing numeric information). I don't know, but would trying for instance

[tt] .FindFirst ("Val(cyclesRemaining) <= 0")[/tt]

do? Would you need something to test against Null too?

Roy-Vidar

 

[PHV]

Anyway, replace this:
Dim rsEngineList, rsEngineComponent As DAO.Recordset
Dim idx, x As Integer
with this:
Dim rsEngineList As DAO.Recordset, rsEngineComponent As DAO.Recordset
Dim idx As Integer, x As Integer

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886