Sql Max returning lowest -Negative as Max

[softbrix2]

I have a table that use to use 4 digits as part of the invoice. I've since did 9999 invoices and tried to change it to 99999. my invoice number is in format XXX-MM-9999
when change the max value in the database to 09999 to the select statement would get the max invnum then add 1 to it. Instead it would return the lowest negative number eg. -1954 instead of 01955.

SELECT MAX(RIGHT(INVOICEID,5)) AS invnum from orders where ucase(left(invoiceUD,3))='AJM'; I would get -2276 as the result but the highest number is 02284, but the lowest negative is -2276. I know it has the negative but should Max choose the Max Positive number? If I manually convert all the 4digit invnum to 5 digits it work fine.

is there something wrong with my sql statement? I tried to do where invum>0 but got error wrong datatype

 

[JBinQLD]

Mate,

Your formating hasn't mistakenly become 0-0000 resulting in the "-" becoming a subtraction symbol has it? I would explain all your numbers becoming negative since 0-XXX is of course -XXX.

 

[sqlmaniac]

Looks like it's comparing strings, not numbers. If you want to do a one-time update to increase the number of digits, what about

UPDATE Invoices SET InvoiceID = MID(InvoiceID, 1, InStrRev(InvoiceID, "-")) & RIGHT("00000000" & MID(InvoiceID, InStrRev(InvoiceID, "-") + 1), 5)

where the last number "5" is the number of digits you want. Be careful and test this as a SELECT before you run it on your data.

 

[softbrix2]

thanks this should work but i manually search & replaced about 22k records, I'll try your statement tommorrow.. Tired

 

[softbrix2]

you are correct. my numbers are picking up as negative because of the -, but shouldn't MAX return the higher positive interger and not the lowest?

 

[JBinQLD]

In the world of TEXT Max behaves srangely. To see, make a able with 1 text field in it. Enter values -11 and 1. Sort ZA - You'd expect being larger, 1 would be at the top - this is not the case. i dont know how the max or sort works as the - symbol has a lower ASCII value than any digit. Anyhoo, make sure you're dealing with an integer by incorporating a CInt function...

 

[PHV]

SELECT MAX([!]Val([/!]RIGHT(INVOICEID,5)[!])[/!]) AS invnum ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886