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

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

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

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

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

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

sorted - was a text / number field issue