Moving to the next record

[BPhilb]

I'm trying to return a value that is part of my original number. It's returning the first value just like I want but every value after that is returning the first value again. I'm guessing that I don't have the record moving to the second record which I thought would happen with my movenext statement. As you can tell I'm not great at this yet and have wrote this code in DAO but was hoping somebody might be able to give me a jumpstart.

Function Test(x) As Double
Dim db As Database
Dim rst As Recordset

Set db = CurrentDb()
Set rst = db.OpenRecordset("ITEM", , dbForwardOnly)

Set x = rst("ISBN1&quot
rst.MoveFirst
Do Until rst.EOF
If (Left([x], 3)) = "978" Then
i = (Mid([x], 5, 6))

Test = x

Else
rst.MoveNext

End If
Loop

End Function

Thanks in advance

Brad

 

[Haitham]

Dear Brad,

I Guess U Have To Modefy It Like This As I Understood.

Set x = rst.Fields("ISBN1&quot

Good Luck

 

[BPhilb]

Haitham

Thanks for the response. I'm probably not explaining this very well. The first record in the table is a number (9780516060347) which I am editing to 51606. The next record is 9780516201580 which should become 51620. The problem is that it still returns the value of 51606. I can't understand why this is happening for the life of me. I did set the fields = ISBN1 but still encountered the same problem. I hope this makes more sense. Would you have any other ideas that I could try? Thanks again.

Brad

 

[cmmrfrds]

If (Left([x], 3)) = "978" Then
i = (Mid([x], 5, 6))

Test = x
"-- Need to advance under the equal condition also.
Else
rst.MoveNext

End If

 

[SyntaxCorrect]

Dear Brad:

Please read the comment I have embedded in your code.

Function Test(x) As Double
Dim db As Database
Dim rst As Recordset

Set db = CurrentDb()
Set rst = db.OpenRecordset("ITEM", , dbForwardOnly)

Set x = rst("ISBN1&quot
rst.MoveFirst
Do Until rst.EOF
If (Left([x], 3)) = "978" Then
i = (Mid([x], 5, 6))

Test = x

' **********************************************************
Try adding the following line of code before the Else statement. With the code that you have currently, every time you have text matching pattern you are trying to find the Recordset pointer is never advanced to the next record.
' **********************************************************
rst.MoveNext
' **********************************************************

Else
rst.MoveNext

End If
Loop

End Function


Regards.

SyntaxCorrect