Solved

MAX ID problem

Posted on 2004-08-23
3
202 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Back Up Your Microsoft Windows Server®

Back up 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

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

730 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