command button

G

Guest

I have a form with a command button on it and when i press that button i
would like it to search for a number. I have in my table a list of numbers
example: sp-001/sp-002/sp-003 ex... I would like for the button to shoe the
first or lowest number missing. for example if i have in my table sp-001 then
sp-003 i would like it to show sp-002 in the box. Thanks so much for any
help.
 
R

Rob

Erik

Is this a one time processing you need? Because this can be a bit complex.

First, I have to assume that "sp-001" is the full text and not a compound of
text and a number.
Second, I have to assume that "sp-" will stay consistent and "###" will be 3
digit number that will reset after 999.
Which leads me to, what happens when the 1000th is reached?

I created a simple table and used the field "Field1" to simulate the values
for the "sp-###".
With that I have 2 columns, the text and the corresponding numeric value.
Create a recordset, and traverse through that to make a determination if
there is a break.
Can you at least work that from this part?

SELECT Table1.Field1, CInt(Right(Table1.Field1, 3)) as ANumber
FROM Table1
ORDER BY Table1.Field1;

Field1 ANumber
---------------------------
sp-001 1
sp-002 2
sp-003 3
sp-005 5
sp-006 6


--
FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

FMS Advanced Systems Group
http://www.fmsasg.com/
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel pivot tables - specific expand/collapse buttons visibility 1
Need help adding a front slash "/" in between numbers that do nothave it. Examples included. 12
Numbering documents 1
Record output in queries 1
preserving numbering in a SEQ after inserting a new field 3
Adding a blank row between values 2
Listing out Budget, Actual and Forecast 4
Extraction of Dates 4

Top