Solved

MAX ID problem

Posted on 2004-08-23
3
199 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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

746 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

11 Experts available now in Live!

Get 1:1 Help Now