[AccessD] One Question (Was Two Questions)

Hi Karen

Joe had a similar request some years ago:

> If I have data similar to:
> JOE001
> JOE002
> JOE003
> JOE005
> JOE006

> Is there a way, using just SQL, to return the next available number in the
> sequence, JOE004, and be flexible enough to return JOE007 if JOE004 had
> already existed?
> Or do I have to use code to iterate through the records to check for
> "holes"?

Except for the indication of some malfunctioning during the design state as already pointed out by others, there is:

<SQL>

  SELECT TOP 1
    TextID,
    Left(TextID, Len(TextID)-3) &
      Right(String(3,"0") & Right(TextID,3)+1,3) AS NextID
  FROM
    tblID
  WHERE
    (Nz(
      (Select Top 1
        Val(Right(TextID,3))
      From
        tblID As T
      Where
        T.TextID > tblID.TextID)
      -Val(Right([TextID],3)),
    2)>1)
  ORDER BY
    tblID.TextID;

</SQL>

All the "3"s count for the length of the numeric right part of the textid. 
Note the "magic 2" in Nz() which cares for the return of the last textid if no "holes" exist.

/gustav

-- 
You should be able to simplify this (as your IDs are pure numbers) to your need.

/gustav

>>> karenr7 at oz.net 14-06-2006 05:27 >>>
OK, so with your collective help, this thing is coming along. 

I want to sort the dictation passwords (a unique 5 digit number)with the
highest number on top so that when adding a new dictating provider to the
database, I can see what the next highest new number should be.
Unfortunately, I inherited this really crappy Excel spreadsheet and there's
a bunch of codes out of sequence. I think that people were just being lazy
and giving a random high number  to incoming requests because they didn't
feel like looking something up. So I can't just have some code to pick the
highest number, because it would be out of sequence.

Now I've got an unbound text box with the following code: 

SELECT tStaff.StaffID, [LanierID] & " -- " & [LastName] &
IIf(IsNull([FirstName]),"",", " & [FirstName]) AS Lanier FROM tStaff ORDER
BY [LanierID] DESC; 

I think the answer is probably going to be no, but... Is there a way I can
mark in the unbound box when the numbers go out of sequence.

In other words:

13888
-------------------> Is there some way to put a line here or different
colored text or some kind of marker 
so that the users can can see at a glance what the next highest sequential
number shuld be?
12347
12346
12345

Any ideas?

Regards,

Karen Rosenstiel
Seattle WA USA