On Thu, 13 May 2010 18:54:59 GMT, "mls via AccessMonster.com"
u55943@uwe wrote:

The syntax error is because you don't have a closing double-quote:
int1 = DCount("*", "tbl_results", "sample")

But that is probably not what you meant to do, since "sample" is not a
normally-formatted criteria string. Normally you would see
"sample=29045" or some such.

-Tom.
Microsoft Access MVP


Thanks Tom. It worked but I could not fix the group by clause in the code, it
is giving me ERRORNo values as I am not using group by clause.
I tried the this: int1 = DCount("*", "tbl_results", "sample), this is showing
syntax error ..

ErrorNO FixedNo
Test 2 Test 1
Test 2 Test 2
Test 1 Test 1
Test 2 Test 2
Test 1 Test 1

Sub Testno()
Dim rs As dao.Recordset
Dim cnt1 As Integer
Set rs = "CurrentDb.OpenRecordset(select * from tbl_results order by sample,
group, dbOpenDynaset)"
While Not rs.EOF
cnt1 = DCount("sample", "tbl_results")
If cnt1 1 Then
rs.Edit
If (rs.Fields("group").Value = "IR") And (rs.Fields("Testno").Value = "Test
1") Then
rs!Testno = "Test 1"
ElseIf (rs.Fields("group").Value = "IG") And (rs.Fields("Testno").Value =
"Test 1") Then
rs!Testno = "Test 2"
ElseIf (rs.Fields("group").Value = "IR") And (rs.Fields("Testno").Value =
"Test 3") Then
rs!Testno = "Test 3"
ElseIf (rs.Fields("group").Value = "IG") And (rs.Fields("Testno").Value =
"Test 3") Then
rs!Testno = "Test 4"
End If
End If
rs.Update
rs.MoveNext
Wend
rs.Close
End Sub


Tom van Stiphout wrote:
dim rs as dao.recordset
set rs=currentdb.openrecordset("select * from tblResults order by
Sample, Group", dbOpenDynaset
while not rs.eof
'do your processing
rs.edit
rs!TestNo = "???"
rs.update
rs.movenext
wend
rs.close

Note the orderby clause in the sql statement. It is important to
understand a table is unordered until we add an orderby clause.

The processing code may involve keeping the values of the last-visited
row, and comparing with the current row. I didn't write this code
because it depends on the finer points of what you really want. You
can probably handle that.

-Tom.
Microsoft Access MVP

I have a table which looks like the following..
Sample group TestNo
[quoted text clipped - 20 lines]

Thanks a lot