DSum Question

[Guest]

Here is what I have on a form:

=DSum("[NI]","[tblProductionData]","[ProgramType]='" & [ProgramType] & "'
AND [PostcardPhase]='" & [PostcardPhase] & "' AND [Resort]='" & [Resort] & "'
AND [Team]='" & [Team] & "' AND [CallCenter]='" & [CallCenter] & "' AND
[Date]=#" & [Date] & "#")

Everything works fine as long as all the criteria fields have data. The
problem occurs when one of the criteria fields is blank within the table.
When this happens it gives me nothing. Just a blank. All of the fields will
always have data except the PostcardPhase field. What do I need to add so
that it still gives me a sum when PostcardPhase is blank? Thank You.

 

[Brendan Reynolds]

Wrap the reference to the field that may be Null in an NZ() ...

& NZ([PostcardPhase], 0) &

See NZ Function in the help file for more information.

 

[Rob Parker]

You need to test for null in that field, and change the criteria string
appropriately. Try:

.... AND " & iif(IsNull([PostcardPhase]),"[PostcardPhase] Like '*'
","[PostcardPhase]= '" & [PostcardPhase] & ""') & AND [Resort] ...

Haven't tested, so maybe I've got my nested quotes out of whack with what's
in the rest of the string - adjust if necessary ;-)

If the field could contain a zero-length string rather than being Null,
you'll need to change the first parameter in the iif as follows:

.... iif(IsNull([PostcardPhase]) Or Len([PostcardPhase]) = 0, ...

HTH,

Rob

 

[Guest]

I knew that. Thank you for jogging my memory.

Wrap the reference to the field that may be Null in an NZ() ...

& NZ([PostcardPhase], 0) &

See NZ Function in the help file for more information.

--
Brendan Reynolds (MVP)

Here is what I have on a form:

=DSum("[NI]","[tblProductionData]","[ProgramType]='" & [ProgramType] & "'
AND [PostcardPhase]='" & [PostcardPhase] & "' AND [Resort]='" & [Resort] &
"'
AND [Team]='" & [Team] & "' AND [CallCenter]='" & [CallCenter] & "' AND
[Date]=#" & [Date] & "#")

Everything works fine as long as all the criteria fields have data. The
problem occurs when one of the criteria fields is blank within the table.
When this happens it gives me nothing. Just a blank. All of the fields
will
always have data except the PostcardPhase field. What do I need to add so
that it still gives me a sum when PostcardPhase is blank? Thank You.