SQL statement not working as wanted

[Catrina]

I am using Access with a VB project. I am creating Queries to help with reports.

I have employee totals stored in a table as strings(I have to store them this way to be compatable with the program the totals are tranmitted to). Negative numbers are stored as 00500-. When summing these I need to look for the - and change that total into a negative. I have tried using an IIF statement, but I get a false returned evey time. Here is part of my SQL statement:

IIf((Right([EmployeeHours].[AdjToNet],1)="-&quot,(0 Val([EmployeeHOurs].[AdjToNet])),
[EmployeeHours].[AdjToNet]) AS adjToNet

Does Access not understand the right,left, mid commands? I get no errors, the statement is just not working correctly. Any suggestions?

Thanks

Catrina

 

[PaulF]

try this

IIf(Right([EmployeeHours].[AdjToNet],1)="-",-1* Val([EmployeeHours].[AdjToNet]),Val([EmployeeHours].[AdjToNet])) AS Adjusted

PaulF

 

[dneufarth]

or you might use

CDbl([EmployeeHours].[AdjToNet]) AS adjToNet

instead of the if construct.


Dave