DSum Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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.
 
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
 
I knew that. Thank you for jogging my memory.

Brendan Reynolds said:
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)


This Guy said:
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.
Click to expand...
Click to expand...
 
Back
Top