Help with code or update query

[ste4en]

I have some data as shown below. It is storted by the qryLinks_link field (name and date) then by counter. I need to change the job number when it is 201901.

For each qryLink_link when the first jobnumber is 201901 it needs to be replaced with the jobnumber in the next record. If there is only one it stays as it is.

The 201901 jobnumber is always the first jobnumber for each qryLinks_links group.

Example of before and after is below.

Thanks

Steve


Change this

qryLinks_link Counter JobNumber
Abney, Timothy -1/1/2007 158651 201901
Abney, Timothy -1/10/2007 166861 201901
Abney, Timothy -1/10/2007 167474 4021133
Abney, Timothy -1/11/2007 168070 201901
Abney, Timothy -1/12/2007 169004 201901
Abney, Timothy -1/12/2007 169392 4021133
Abney, Timothy -1/12/2007 169402 4021126
Abney, Timothy -1/12/2007 169987 4021444
Abney, Timothy -1/13/2007 170121 201901
Abney, Timothy -1/13/2007 170660 4021112

qryLinks_link Counter JobNumber
Abney, Timothy -1/1/2007 158651 201901
Abney, Timothy -1/10/2007 166861 4021133
Abney, Timothy -1/10/2007 167474 4021133
Abney, Timothy -1/11/2007 168070 201901
Abney, Timothy -1/12/2007 169004 4021133
Abney, Timothy -1/12/2007 169392 4021133
Abney, Timothy -1/12/2007 169402 4021126
Abney, Timothy -1/12/2007 169987 4021444
Abney, Timothy -1/13/2007 170121 4021112
Abney, Timothy -1/13/2007 170660 4021112

 

[PHV]

You may try this update query (SQL code, typed, untested):
UPDATE yourTable
SET JobNumber = Nz(DLookUp("JobNumber","yourTable","qryLinks_link='" & qryLinks_link & "' AND Counter=" & DMin("Counter","yourTable","qryLinks_link='" & qryLinks_link & "' AND Counter>" & Counter)),JobNumber)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ste4en]

PH Thanks;

When I run the query I get a type conversion error for 2940 rows. If I run it anyway it removes ALL of the 201901 in the jobnumber field;

Jobnumber and Counter are both number fields.

Any other ideas?

Thanks