how can I replace SQL query with VBA logic
in [Form]
|
From: mls via AccessMonster.com on 13 May 2010 09:38 I have a table which looks like the following.. Sample group TestNo 29045 IG Test 1 29053 IG Test 1 29053 IR Test 1 29067 IG Test 1 29067 IR Test 1 and I need to correct my TestNo, If the sample is same and group is different then I need to assign different no, My query looks like this.. DoCmd.RunSQL "UPDATE tbl_Results set TestNo='Test 2' where group='IG' and TestNo='Test 1';" this query updates every row with IG whcih is not correct. I wan to update only if the sample is same and group is different. I want to get values of FixedNo and not ErrorNo. For this I need to use count(*) and having having count >2 but how can I do this. Is there a way to do this in VBA? ErrorNO FixedNo Test 2 Test 1 Test 2 Test 2 Test 1 Test 1 Test 2 Test 2 Test 1 Test 1 Thanks a lot -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/201005/1
From: Tom van Stiphout on 13 May 2010 09:54 On Thu, 13 May 2010 13:38:37 GMT, "mls via AccessMonster.com" <u55943(a)uwe> 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 >29045 IG Test 1 >29053 IG Test 1 >29053 IR Test 1 >29067 IG Test 1 >29067 IR Test 1 >and I need to correct my TestNo, If the sample is same and group is different >then I need to assign different no, My query looks like this.. >DoCmd.RunSQL "UPDATE tbl_Results set TestNo='Test 2' where group='IG' and >TestNo='Test 1';" >this query updates every row with IG whcih is not correct. I wan to update >only if the sample is same and group is different. I want to get values of >FixedNo and not ErrorNo. >For this I need to use count(*) and having having count >2 but how can I do >this. Is there a way to do this in VBA? >ErrorNO FixedNo >Test 2 Test 1 >Test 2 Test 2 >Test 1 Test 1 >Test 2 Test 2 >Test 1 Test 1 > >Thanks a lot
From: mls via AccessMonster.com on 13 May 2010 14:54 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 -- Message posted via http://www.accessmonster.com
From: Tom van Stiphout on 14 May 2010 09:47 On Thu, 13 May 2010 18:54:59 GMT, "mls via AccessMonster.com" <u55943(a)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
From: mls via AccessMonster.com on 14 May 2010 11:05
Tom, I fixed the syntax error. If the sample appears twice in the list then only I have to execute my code and change the TestNo. In order to capture the duplicate sampleid I am using dcount function to count. I can't use the WHERE clause as my samples change each and every time and I don't know what they will be. Thanks Tom van Stiphout 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. >[quoted text clipped - 65 lines] >>>> >>>>Thanks a lot -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/201005/1 |