Prev: Combo box that prints report
Next: Cannot View Form in 2007
From: John W. Vinson on 14 May 2010 11:52
On Fri, 14 May 2010 15:05:37 GMT, "mls via AccessMonster.com" <u55943(a)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]
From: mls via AccessMonster.com on 14 May 2010 12:36
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/Forums.aspx/access-forms/201005/1

From: mls via AccessMonster.com on 14 May 2010 12:38
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/Forums.aspx/access-forms/201005/1

From: Tom van Stiphout on 15 May 2010 01:25
On Fri, 14 May 2010 15:05:37 GMT, "mls via AccessMonster.com"
<u55943(a)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
From: John W. Vinson on 15 May 2010 02:32
On Fri, 14 May 2010 16:36:41 GMT, "mls via AccessMonster.com" <u55943(a)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]
First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Combo box that prints report
Next: Cannot View Form in 2007