A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

how can I replace SQL query with VBA logic



 
 
Thread Tools Display Modes
  #1  
Old May 13th, 2010, 02:38 PM posted to microsoft.public.access.forms
mls via AccessMonster.com
external usenet poster
 
Posts: 46
Default how can I replace SQL query with VBA logic

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/For...forms/201005/1

  #2  
Old May 13th, 2010, 02:54 PM posted to microsoft.public.access.forms
Tom van Stiphout[_2_]
external usenet poster
 
Posts: 1,653
Default how can I replace SQL query with VBA logic

On Thu, 13 May 2010 13:38:37 GMT, "mls via AccessMonster.com"
u55943@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

  #3  
Old May 13th, 2010, 07:54 PM posted to microsoft.public.access.forms
mls via AccessMonster.com
external usenet poster
 
Posts: 46
Default how can I replace SQL query with VBA logic

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

  #4  
Old May 14th, 2010, 02:47 PM posted to microsoft.public.access.forms
Tom van Stiphout[_2_]
external usenet poster
 
Posts: 1,653
Default how can I replace SQL query with VBA logic

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

  #5  
Old May 14th, 2010, 04:05 PM posted to microsoft.public.access.forms
mls via AccessMonster.com
external usenet poster
 
Posts: 46
Default how can I replace SQL query with VBA logic

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/For...forms/201005/1

  #6  
Old May 14th, 2010, 04:52 PM posted to microsoft.public.access.forms
John W. Vinson
external usenet poster
 
Posts: 18,261
Default how can I replace SQL query with VBA logic

On Fri, 14 May 2010 15:05:37 GMT, "mls via AccessMonster.com" u55943@uwe
wrote:

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.


You certainly do NOT need to know the sample number to find duplicates. Could
you explain the logic which identifies which records need to be updated? A
Query referencing the table should certainly be able to do this.
--

John W. Vinson [MVP]
  #7  
Old May 14th, 2010, 05:36 PM posted to microsoft.public.access.forms
mls via AccessMonster.com
external usenet poster
 
Posts: 46
Default how can I replace SQL query with VBA logic

Sample group TestNo
29045 IG Test 1
29053 IG Test 1
29053 IR Test 1
29067 IG Test 1
29067 IR Test 1

John, In the above table, I want to update Testno, only for the records which
has same sampleid and different group( 'IG') as 'Test 2'. TestNo for record
should not change.

Thanks
John W. Vinson wrote:
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.


You certainly do NOT need to know the sample number to find duplicates. Could
you explain the logic which identifies which records need to be updated? A
Query referencing the table should certainly be able to do this.


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201005/1

  #8  
Old May 14th, 2010, 05:38 PM posted to microsoft.public.access.forms
mls via AccessMonster.com
external usenet poster
 
Posts: 46
Default how can I replace SQL query with VBA logic

Sorry, I mean TestNo for first record should not change as this sample has
single record.

mls wrote:
Sample group TestNo
29045 IG Test 1
29053 IG Test 1
29053 IR Test 1
29067 IG Test 1
29067 IR Test 1

John, In the above table, I want to update Testno, only for the records which
has same sampleid and different group( 'IG') as 'Test 2'. TestNo for record
should not change.

Thanks
Tom, I fixed the syntax error.
If the sample appears twice in the list then only I have to execute my code

[quoted text clipped - 5 lines]
you explain the logic which identifies which records need to be updated? A
Query referencing the table should certainly be able to do this.


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201005/1

  #9  
Old May 15th, 2010, 06:25 AM posted to microsoft.public.access.forms
Tom van Stiphout[_2_]
external usenet poster
 
Posts: 1,653
Default how can I replace SQL query with VBA logic

On Fri, 14 May 2010 15:05:37 GMT, "mls via AccessMonster.com"
u55943@uwe wrote:

Sorry, MLS, but it seems apparent you need professsional programming
assistance to complete this task. In my mind one of the problems is
that the criteria have not rigorously been defined. If they have in
your mind, then the issue is of communicating this to the developer.

-Tom.
Microsoft Access MVP


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

  #10  
Old May 15th, 2010, 07:32 AM posted to microsoft.public.access.forms
John W. Vinson
external usenet poster
 
Posts: 18,261
Default how can I replace SQL query with VBA logic

On Fri, 14 May 2010 16:36:41 GMT, "mls via AccessMonster.com" u55943@uwe
wrote:

Sample group TestNo
29045 IG Test 1
29053 IG Test 1
29053 IR Test 1
29067 IG Test 1
29067 IR Test 1

John, In the above table, I want to update Testno, only for the records which
has same sampleid and different group( 'IG') as 'Test 2'. TestNo for record
should not change.


UPDATE tablename AS A
SET [TestNo] = "Test 2"
WHERE EXISTS
(SELECT B.[Sample] FROM tablename AS B
WHERE B.Sample = A.Sample
AND B.Group A.Group)

should work, if I understand you correctly (but back up your database first
because I probably don't!)
--

John W. Vinson [MVP]
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:39 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.