Inserting "19" into all entries of a column 1

[JeffreyE]

As a pathologist, I have a database that keeps records of cases back to 1993. The pathology accession numbers before January 1, 2000 were "S followed by the two digit year number followed by a dash then the case number for that year (example S99-3579). With the new year we switched to a four digit year format (i.e. S2000-0001), but as the key field in this table, sorting in ascending order puts the 2000 cases first and will continue to insert all new cases before the first 1993 cases. There may be more obvious ways around this but I thought if I inserted "19" into all S9X-XXXX entries then the order would again be correct. Thanks for your help in advance.

 

[Zelandakh]

create a query that only contains the field in question. The where clause should be length(field)=8 so that you do not include the Y2K ones. Change the query to an append query rather than a select. Field appends to left(field,1)&&quot;19&quot;&right(field,7).<br>
<br>
This means that every number is in the same format so whilst it may not be the most efficient way of doing things it is the most consistent.

 

[JeffreyE]

I have been successful through the select query that did not include the Y2K group and I have changed the query to append. Then I must be doing something wrong. I keep receiving &quot;You are about to append 0 records.&quot; I think it is something wrong with where or how the SQL statement is put in with regard to &quot;left(field,1)&&quot;19&quot;right(field,6). (I am doing this on a A9X-XXX (autopsy group) first before I do it on the 50 thousand in number S9X-XXXX group.<br>
<br>
Do I simply type in left(field,1)&&quot;19&quot;right(field,6) into the append query criteria line or do I need something else? Hope you get this with this 5 day old thread.<br>
<br>
Thanks, JeffreyE

 

[JimHorton]

Jeffrey,<br>
Are your fields guaranteed to be in the format A9X-XXXX, ie, total 8 characters? or could they be A9X-XXX (7 char.)? If it varies, and the only other case would be where the year is 4 digit: A19XX-XXX or A19XX-XXXX, (9 or 10 char), then use this as the criteria: Where Len([field]) &lt;=8<br>
<br>
Also, you're missing a concat operator (&) after the &quot;19&quot;: the Update To should be<br>
Left$([field],1) & &quot;19&quot; & mid$([field],2,10)<br>
The Mid() is preferrable than Right(), since we may not know how long the string is, and the third argument to Mid() (the 10 in my example) can overrun the total length without harm--that is, put a value you know to be at least the lenght of the remainder, and it will just give the remainder--no error is caused when the third arg. is too long.<br>
The second arg. to Mid() (the Start pos) is the important one here, and we know will always be 2 in your example.<br>
--Jim

 

[JeffreyE]

INSERT INTO [Diagnoses--Autopsies] ( autAutopsyNo )<br>
SELECT [Diagnoses--Autopsies Query].autAutopsyNo<br>
FROM [Diagnoses--Autopsies Query]<br>
WHERE ((([Diagnoses--Autopsies Query].autAutopsyNo)=Left$([autAutopsyNo],1) & &quot;19&quot; & Mid$([autAutopsyNo],2,10)));<br>
<br>
This is the SQL statement that I create and still the &quot;0 records will be appended&quot; message. The field size in the table is 12 so that is not limiting the size increase. The query field after eliminating the A2000-XXX records has a uniform length of 7 (A9X-XXX) prior to attempting the append. Could it be that the splitting of my database limits my ability to append this table's records and, if so, can I do anything to bypass the split? Both databases, the original (with the forms, queries, reports, etc.) and the back-end file (with the tables) only are on my disk and not shared on a network.

 

[JimHorton]

Do you want to just update the existing data? If so, just do<br>
Update [Diagnoses--Autopsies] SET AutAutopsyNo = <br>
Left$([autAutopsyNo],1) & &quot;19&quot; & Mid$([autAutopsyNo],2,10)))<br>
WHERE len([autAutposyNo]) &lt;= 8<br>
<br>
The problem with what you had was you had used the Update clause as the Where clause, in an Insert query. Let me know if it's an Append that you really want...<br>
<br>
--Jim

 

[jgarnick]

I did something similar to this using an update query. I just say to update xx field to &quot;19&quot;& xx.

 

[JimConrad]

Zelandakh mis-spoke when he said to use an APPEND query. He meant to say use an UPDATE query. <p>Jim Conrad<br><a href=mailto:jconrad3@visteon.com>jconrad3@visteon.com</a><br><a href= > </a><br>

 

[vbaprogammer]

Have you tried going to the table itself, selecting Edit¦Replace, type in "S9" for find, and "S199" for replace?<br>
<br>
The replace feature has some options such as Current Column, Exact match, etc.