Solved

MAX ID problem

Posted on 2004-08-23
3
200 Views
Last Modified: 2011-09-20
I have a numbering system i need to implement for a job databse.

When a user want a new job number they click the said Access 2000 front end button and the next number in sequence has to be given to them.

EG UK20006
      UK2007   number given would be UK2008

Having problems wrintg this as the cant increment as the type is varchar not int whats the work around

Using SQL server 2000.

I was asuming the button would fire a procedure.

Thanks in advance

MArk
0
Comment
Question by:ellandrd
  • 2
3 Comments
 
LVL 10

Accepted Solution

by:
Jay Toops earned 500 total points
ID: 11871526
This will do it unless you need to increment UK as well
also watch what happens when the id ROLLS OVER.


declare @currentid varchar (20)
declare @newid varchar(20)
set @currentid = 'UK2007'
set @newid = left(@currentid,2) + cast ( right(@currentid,4) + 1 as varchar(4) )
print @newid

Jay
0
 
LVL 16

Author Comment

by:ellandrd
ID: 11871828
How do i get

select MAX (JOBNO) FROM tbljobno

into

set @currentid = 'UK2007'


Thaks for the help


0
 
LVL 16

Author Comment

by:ellandrd
ID: 11871836
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now