Convert String To Integer To Get Max Number 2

[Happy2]

Can anyone please help me out with this:

LoanInfo table:

LoanNum
1A
2A
3A
4A
5A
6A
7A
8A
9A
10A

I want to get the maximum number (which is 10) in the table so I can increase 1 unit for the next LoanNum (11A).

I have a sql statement works in sql database but when I use CInt, it doesn't work in VBA Access.



This is my sql statement

SELECT MAX(convert(int,Left(LoanNum, Len(LoanNum) - 1))) AS LoanNum
FROM LoanInfo
WHERE right(LoanInfo.LoanNum, 1) = 'A'

Thanks in advance!

 

[RickSpr]

The CInt() function fails if the string isn't completely numeric. The Val() function, on the other hand, obtains an integer value from leading digits, ignoring everything after the first non-digit. So as long as the number is at the start of the column, you can use Val().
SELECT MAX(Val(LoanNum)) As MaxLoanNum
FROM ...

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[Happy2]

Hi Rick,

I don't know why I got a run-time error, it said that "Undefined function 'Value' in expression".

Btw, I am using xp version. The dataType of the column is varchar.

 

[TonyJollans]

Hi Happy2,

Try using Val, as Rick said, rather than Value.

Enjoy,
Tony

 

[Happy2]

I am sorry Rick, my mistake. Thanks Rick and Tony!