Data type mismatch for Sum query

[Summer123]

Does anyone know how i can overcome this error? I currently have this query where i am converting the COBOL numbers into actual dollar amounts. What i want to now is sum up the actual dollar amounts (Amount_$ below) but when i do a Sum query it gives an error for "Data Type mismatch" can anyone please help?

SELECT [Record].Amount, IIf([Amount] Is Null Or (Right([Amount],1) Not In ('{','a','b','c','d','e','f','g','h','i','j','k',' l','m','n','o','p','q','r','{')),"Amount formatted incorrectly", IIf(Right([Amount],1)='{',1,IIf(Right([Amount],1)='}',-1,IIf(Right([Amount],1)<'J',1,-1)))*(Left([Amount],Len([Amount])-1)*10+IIf(Right([Amount],1)='{' Or Right([Amount],1)='}',0,IIf(Right([Amount],1)<'J',Asc(Right([Amount],1))-64,IIf(Right([Amount],1)<'S',Asc(Right([Amount],1))-73,0))))/100) AS [Amount_$]
FROM Record;

 

[Guus2005]

Apparently one of the fields is not a number but an alfabetic character.

Create the query without the group by clause and sort the Amount field in reverse order.
The culprit should appear at the top, the first non empty field.

Correct the problem and try again.

HTH

 

[Summer123]

But lets say we want the null or space value to be interpreted as 0's then what should be done? I mean is there a way to do this??

 

[Guus2005]

a Null value and a space are two different things.
There is the Nz function which replaces the value if NULL:

?Nz(NULL,0)
0
?Nz(1,0)
1

A space or an empty string are definitly not NULL.
So if X = " " the result using the Nz function should be

?Nz(X,0)
  << here is an empty string or a single space character depends what X is.

If you want " " or "" to be interpreted as 0 you need to use the following or similar

?IIF(Len(Trim(Nz(X,0)))=0,0,X)

Checking for a zero length string is allways faster than checking for X = "".
Since you a probably want your code to be fast, check for a zero length string.

HTH