simple loop questions

[swarv]

I have the following:

Dim I As Integer
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Set db = CurrentDb()
strSQL = "SELECT tbl_users.bradfordscore, tbl_users.fullname FROM tbl_users WHERE bradfordscore > (daysperweek * 15)"
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
I = 0
 Do While Not rst.EOF
       I = I + 1
       MsgBox strSQL
       rst.Edit
       'rst!RunningSum = lRunningSum
       rst.Update
       'lRunningSum = lRunningSum + rst!Duration
       rst.MoveNext
    Loop
    rst.Close

but where it says msgbox strSQL I want it to add the tbl_users.fullname and tbl_users.bradfordscore to a sheet somehow.

Is this possible?

Thanks

Martin

 

[swarv]

change of code

Dim I As Integer
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Set db = CurrentDb()
strSQL = "SELECT tbl_users.bradfordscore, tbl_users.name FROM tbl_users WHERE bradfordscore > (daysperweek * 15)"
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
I = 0
 Do While Not rst.EOF
        lgst = rst("bradfordscore")
        Name1 = rst("name")
        MsgBox lgst
        MsgBox Name1
       rst.MoveNext
    Loop
    rst.Close

the above works but doesn't quite give the result needed. It seems to pick the wrong bradfordscore from the table and miss somebody out completly.

Any ideas welcome.

thanks

 

[swarv]

I think its somethign to do with the * 15 but not sure.
any idea?

 

[DCrake]

Create a query using the same sql as the recordset and see if the results are expected. The syntax looks ok and like you say it may be something to do with the formula. Make sure that all recrods have data in them to be *15
Null*15 = Error

David

 

[swarv]

Hi David,

My query looks like this:

SELECT tbl_users.bradfordscore, tbl_users.name
FROM tbl_users
WHERE tbl_users.bradfordscore > [forms]![frm_homepage]![daysweek2];

the results are still not what I am expecting.
basically daysweek2 is 75.
I am gettnig back users whose score is 9.

it seems such a simple query aswell

cheers

 

[swarv]

made the query simpler:

SELECT tbl_users.bradfordscore, tbl_users.name
FROM tbl_users
WHERE tbl_users.bradfordscore > "75";

but it still returns the record where the score is 9. I have a user where the score is 125 and it should show this one.

this is driving me nuts

 

[swarv]

sorted - was a text / number field issue