DSum error when using IIF

[AUGuy]

I'm attempting to do a complex DSum calculation to be used on a summary form. What i'm attempting to do is Sum the balance field of an existing table, however the balance has a sign indicator field (Cur-Bal-S) which is a text field that is either null or "-" for negative. Naturally, the equation that needs to happen is as follows:

[Cur-Bal] * iif([Cur-Bal-S]="-",-1,1)

This equation works perfectly in queries and other applications, however when i try to integrate it into the DSum, i get a Type Mismatch error. The code I'm trying to use is as follows:

?DSum("[Cur-Bal] * iif([Cur-Bal-S]="-",-1,1)","[CL - Month End Data]")

This returns Run-time Error '13'. Type Mismatch.


Any insight?

Thanks, Guy

 

[MarkK]

You can't use double quotes to delimit a string within a string delimited by double quotes directly. You get a type mismatch in this case because you are attempting to perform a subtraction on strings. Try ...

DSum("[Cur-Bal] * iif([Cur-Bal-S]='-',-1,1)","[CL - Month End Data]")

But I don't get why you wouldn't store the balance as a negative number. Failing that, store the sign indicator as a number, either a minus one, or a plus one, and multiply it in.

 

[boblarson]

Change this part:
"-"

to this

'-'

it is the double quotes that is messing with the part that needs to be in double quotes.

 

[AUGuy]

Thanks both of you, that does it!
The Balance field has an associated sign field because thats the way we receive it. Its a daily extract we get from IT and thats just how they decided to do it