0

I have table containing multiple columns and I want to get maximum value from invoice number which consists of alphanumeric value like "AR564". I know I should have numeric column to make this happen but now I have a lot of data and it is difficult to change basic things. So I want to solve problem of getting "AR999" as maximum instead of "AR1000". I have tried doing 

SELECT MAX(CAST(SUBSTRING(invoice_number, 4, length(invoice_number)-2) AS UNSIGNED)) 
FROM table

but getting following error.

IErrorInfo.GetDescription failed with E_Fail (0x80004005)

I am using Microsoft Access database and please someone tell me what is the purpose of "4" in above sql statement.

Vadim Kotov
  • 8,084
  • 8
  • 48
  • 62
Neymar
  • 1
  • 1
  • MS Access does not have CAST, unless you have an SQL server back-end. Try `clng(mid(invoice_number,3))` Note that MAX is very, very unsafe for a number that means something. I recommend that you use a small table that can be locked to get the next value. See http://stackoverflow.com/a/11950647/2548 – Fionnuala Nov 16 '16 at 10:43
  • thanks it worked. Please tell me what does 3 mean in above expression? – Neymar Nov 22 '16 at 11:17
  • It is the start position. https://www.techonthenet.com/access/functions/string/mid.php – Fionnuala Nov 22 '16 at 12:09

0 Answers0