Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
coolwrld
Starting Member
10 Posts |
 Posted - 2006-03-13 : 14:58:15
|
| Here is the scenario.I have a 10 position varchar field that holds a project number. The project number is in the form yy-nnn... where yy is the current year (06) and nnn is a sequential number. The project number is displayed on a data entry form so that the next record has the next sequential number. So when the data entry form first opens the project number field has 06-1 after that record is entered the form is cleared and reinitialized and the project number now shows 06-2. If the form is closed and then reopened the project number would still show 06-2 if that previous record was not entered and 06-3 if it was.The way I am going about getting the next sequential number is the following:SELECT MAX(RIGHT(ERFNumber, LEN(ERFNumber)-3)) AS MaxERFNumber From Table1This approach is working fantastic until record 06-9 is entered. The above line does determine that 06-9 is the MAX record and then creates a record 06-10 but every subsequent time a record is attempted it still thinks 06-9 is the MAX value of the above statement.I am assuming that since the field is defined as a varchar the MAX function is applying character logic versus numeric logic thus the 9 being treated as the MAX value.Is there an SQL function that can change the result of RIGHT(ERFNumber, LEN(ERFNumber)-3) to a numeric value so that the MAX function will work correctly.I know there are many other ways to do this but in the interest of time because the application is almost finished, changing this now is going to be a major undertaking.Any help or guidence would be greatly appreciated. |
|
|
JoeNak
Constraint Violating Yak Guru
292 Posts |
Posted - 2006-03-13 : 15:12:53
|
| You can use CONVERT or CAST to get the results to a numberic value.SELECT MAX(CONVERT(INT, RIGHT(ERFNumber, LEN(ERFNumber)-3))) AS MaxERFNumber From Table1 |
 |
|
|
coolwrld
Starting Member
10 Posts |
Posted - 2006-03-13 : 15:20:21
|
| JoeNak,Thank you so very much. That works great. I figured there had to be a way I just couldn't find the function. |
|
|
|
coolwrld
Starting Member
10 Posts |
Posted - 2006-03-13 : 15:26:28
|
| JoeNak,I have one more question for you. That Select statement works great in the query analyizer, but I am getting a function undefined error in Access which is the frontend application to the SQL Server tables. Is there anyway to get that function to work in Access. I assumed Access used whatever functions are available in SQL Server when it is the table source> |
|
|
|
JoeNak
Constraint Violating Yak Guru
292 Posts |
Posted - 2006-03-13 : 16:06:40
|
| I'm not much of an Access guy. Try...SELECT MAX(CINT(RIGHT(ERFNumber, LEN(ERFNumber)-3))) AS MaxERFNumber FROM Table1 |
|
|
|
coolwrld
Starting Member
10 Posts |
Posted - 2006-03-13 : 16:18:52
|
| JoeNak,For not being much of an Access guy you nailed it. Again thanks for the help. |
|
|
|
|
|
|
SQL String Function Help