DLookup with multiple criteria

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

Guest

Can someone please tell me what I am doing wrong. Here is what I have:

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

[ProgramType] and [Team] are numbers, [Date] is date, and the rest are text.
I am not getting an error, just a blank field. Please help, I've been
fighting with this for days. Thanks.
 
This Guy said:
Can someone please tell me what I am doing wrong. Here is what I have:

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

[ProgramType] and [Team] are numbers, [Date] is date, and the rest are text.
I am not getting an error, just a blank field. Please help, I've been
fighting with this for days. Thanks.
Click to expand...

Not tested, but you were missing some "&" in your posted code and you were
asking Nz() to substitute a numeric zero even though the supplied field was
text.

=DSum("[NI]","[tblProductionData]","[ProgramType]=" & [ProgramType] & " And
NZ([PostcardPhase],"0")='" & NZ([PostcardPhase],"0") & "' And [Resort]='" &
[Resort] & "' And [Team]=" & [Team] & " And [CallCenter]='" & [CallCenter] & "'
And [Date]=#" & [Date] & "# ")
 
I think you're missing a & and " in 2 clauses:
1) "[ProgramType]=" & [ProgramType] And
should be
"[ProgramType]=" & [ProgramType] & " And

2) [Team]=" & [Team] And
should be
[Team]=" & [Team] & " And

HTH,
 
=DSum("[NI]","[tblProductionData]", _
"[ProgramType]=" & [ProgramType] & _
" And NZ([PostcardPhase],0)='" & NZ([PostcardPhase],0) & _
"' And [Resort]='" & [Resort] & _
"' And [Team]=" & [Team] & _
" And [CallCenter]='" & [CallCenter] & _
"' And [Date]=#" & [Date] & "#")

HTH
Van T. Dinh
MVP (Access)
 
Back
Top