Calculated field with NOT Equal issue

[kbassnac]

I'm working on a calculated field that should sum do the following:
CYPending=Sum of all [Requests].[Request] by [Requests].[Program] and [Requests].[FiscalYear] where [Requests].[OrigGrant] is null and [Requests].[DGR] is null AND [Requests].[Status] does not equal “W”

This is what I have so far CYPending: CCur(Nz(DSum("Nz([Request])","REQUESTS","FiscalYear='" & [FiscalYear] & "' and Program=""" & [REQUESTS].[Program] & """ and [REQUESTS].[OrigGrant] IS NULL and [REQUESTS].[DGR] IS NULL and [REQUESTS].[Status] <> ('""W""')")))

However, the value that is returned is not correct. If I remove Status from the calculation it works fine.




Any help would be appreciated,
Kathy in NE

[NTC]

why triple quotes around it? it isn't a form control call...

just a quick off the cuff thought.

[kbassnac]

Thanks for the response.
I was told to use the mutliple quotes since the Status field was a drop down text field. I did try it with just "W" and I received the same incorrect value.

[pbaldy]

Does this work?

and [REQUESTS].[Status] <> 'W'

[kbassnac]

Thanks, but that didn't work either.
The returned value is the calculation the total of [Requests].[Request] by [Requests].[Program] and [Requests].[FiscalYear] where [Requests].[Status] equals “W” and [Requests].[OrigGrant] is null and [Requests].[DGR] is null

[kennejd]

Try this:
dsum("[Request]","Requests","FiscalYear='" & [FiscalYear] & "' AND [Program]='" & [Program] & "' AND isnull([OrigGrant]) = true AND isnull([DGR]) = true and [Status] <> 'w'")

[kbassnac]

Thank you, but dsum("[Request]","Requests","FiscalYear='" & [FiscalYear] & "' AND [Program]='" & [Program] & "' AND isnull([OrigGrant]) = true AND isnull([DGR]) = true and [Status] <> 'w'") did not work, either. It returned a zero value in the field.

I'm open to more suggestions, please.

[pbaldy]

Can you post a sample db along with the expected result?

[kbassnac]

Thanks for the help. I did get a resolution for this question and thought I would post it. Not sure how to mark this one as Solved.

CYPending: CCur(Nz(DSum("Nz([Request])","REQUESTS","FiscalYear='" & [FiscalYear] & "' and Program=""" & [REQUESTS].[Program] & """ and [REQUESTS].[OrigGrant] IS NULL and [REQUESTS].[DGR] IS NULL and (Not ([REQUESTS].[Status] = 'W') Or REQUESTS.Status is Null)")))