Using Calculations with an Alphanumeric Text Field

[JeffreyE]

One of the tables in my database has as a key index text field the pathology number for our lab. It is in a standard format of SYYYY-0000 with YYYY the year and 0000 consecutive integers from 0001 onward through the end of the year. I am using an input mask for entry:<br>
<br>
&gt;&quot;S2000-&quot;0000;0;_<br>
<br>
This works OK but the first number keyed in is ignored. (Keying in 0001 is now requiring one to type 00001.) Any idea what is going on?<br>
<br>
Second, it would be helpful if I could have a default of SYYYY-(0000+1) for the next record because these are almost always accessioned in order.<br>
<br>
Third, I have a query to check for skipped pathology numbers. It builds a table beginning with the first case of the year. One can go to the end of the table and compare the last pathology number with the number of records which should be the same. If there are fewer records than the last path number, one has to scroll through the table typically by 10s and find the missing number(s). Is there a method of detecting a skip from S2000-0555 to S2000-0557 in a text field where the output is the missing number, S2000-0556, rather than the more manual method I use now.<br>
<br>
Thanks for your help in advance.<br>
<br>
JeffreyE

 

[rochelle]

I have to ask. Why is your key designed this way? It looks like trouble waiting to happen! First of all, you have to manually edit your input mask every year. Secondly, you are carrying at least 2 totally meaningless characters in your key: &quot;S&quot; and &quot;-&quot; Why not split the key into 2 separate fields -- year and a sequential counter? The characters &quot;S&quot; and &quot;-&quot; could be added back on reports if you think your users need to see them. Or are there other items in your table that have different letters? Third, what will happen to your formatting when you inevitably get more than 9999 records in a single year?<br>
<br>
If you do need to leave the key as is, you can get your default through the following calculation:<br>
=&quot;S2000-&quot; & Format$(Right(DMax(&quot;[PathNum]&quot;,&quot;Table1&quot,4)+1,&quot;0000&quot<br>
<br>
To find the missing values in the sequence you can build 2 queries as follows:<br>
<br>
The first query called PP1 adds 1 to each existing key value as follows:<br>
SELECT &quot;S2000-&quot; & Format$(Right([PathNum],4)+1,&quot;0000&quot AS PathnumP1 FROM Table1;<br>
<br>
The second query joins the first query to the original keyfield and looks for any missing values:<br>
SELECT PP1.PathnumP1 FROM Table1 RIGHT JOIN PP1 ON Table1.PathNum = PP1.PathnumP1 WHERE (((Table1.PathNum) Is Null));<br>
<br>
This will show you the first record of one or more missing records in the sequence. It will always point to the last record as the beginning of missing records so you can assume all the records are there if there is only one record in the resultset of the second query.