I have a select statement that is producing an error that after TOOOO many hours was able to figure out WHAT was causing but not sure why...

Here is the statement as I had it written....

Select client.clientnum, client.clientname, ;
Trim(code.codenum)+" "+code.codedesc as code, ;
Trim(employee.lastname)+", "+employee.firstname as employee, ;
log.logdate, ;
log.time, ;
log.rate, ;
log.time*log.rate as amount ;
from Timepro!log left join timepro!employee on log.employeeid = employee.employeeid ;
inner join timepro!code on log.codeid = code.codeid ;
inner join timepro!client on log.clientid = client.clientid ;
where log.firmid=gFirmid and log.logdate>=gStartdate and log.logdate<=gEnddate and log.employeeid<>0 ;
into cursor curswip ;
order by client.clientname, val(code.codenum),employee


The error I was getting is
SQL ERROR: Column CLIENTNAME is not found


After extensive trial an error, if I remove the VAL() from around the field CODE.CODENUM the error disappears - now just the sorting order is wrong (10,15,1000,1100,20,2000,30,40 instead of 10,15,20,30,40,1000,1100,2000). I most likely will be switching the field for CODE.CODENUM to a numeric instead of a text but shouldn't my SELECT statement have worked and what does that have to do with CLIENTNAME. I spent so much time trying to figure out why CLIENTNAME was messing up