Access to MySQL help

[hydrazi]

Hello,

I am helping a client leave Access for PHP and MySQL. I am having an SQL issue right now with this statement:

SELECT
`getbankid`.OrderID,
`getbankid`.`Customer Name`,
`getbankid`.`Bank Name`,
`getbankid`.BankID,
`getbankid`.Filenumid & ((Max(Right(filesthisyear.`GCE File`,6)))+100) AS Expr1,
`getbankid`.Filenumid
FROM
`getbankid` LEFT JOIN `filesthisyear` ON `getbankid`.BankID = `filesthisyear`.Lender
GROUP BY `getbankid`.OrderID, `getbankid`.`Customer Name`, `getbankid`.`Bank Name`, `getbankid`.BankID, `getbankid`.Filenumid;


Basically, it runs, but Expr1 has a value of 0 on every record. Any ideas are greatly appreciated.

[Fumigator]

Select filesthisyear.`GCE File` and tell us what the values of that column are.

[Old Pedant]

You are using the & operator. In Access, that is a string concatenation operator. In any other database, it is the bit-wise AND operator.

So you are doing a bitwise AND of those two values and it is not surprising the result is zero.

HOWEVER...

MySQL doesn't support string concatenation with the an operator, in any case.

You must use a CONCAT() function.

http://dev.mysql.com/doc/refman/5.1/en/non-typed-operators.html

I *THINK* that you need to replace
`getbankid`.Filenumid & ((Max(Right(filesthisyear.`GCE File`,6)))+100) AS Expr1,

with

CONCAT(`getbankid`.Filenumid, ((Max(Right(filesthisyear.`GCE File`,6)))+100)) AS Expr1,

But I don't get how you are adding the number 100 to the *STRING* that comes from that RIGHT(....,6).

There's probably a big problem in there, as well. That wasn't even really good code with Access. It's probably toast with MySQL.

If you think that Right(`GCE File`,6) is a number, you are sadly mistaken. And if it is a string that happens to have a numeric value, then it could have a value of up to 999999, of course (that's what the 6 implies), so getting it's MAX value and then adding 100 to that makes no sense, really.

Maybe if you explained what all that code is supposed to be doing--and especially what values will be found in the `GCE File` field--we could suggest a different/better way.