dlookup with 3 criteria

[noe1818 via AccessMonster.com]

Here is my expression:

=IIf(DLookUp("[cmps 28d]","[Laxatives]","[Client ID]=""" & [Client ID] & """
And [Ass #]=" & [Ass #] & " And [Assessment date]=" & [Assessment date])=True,
DLookUp("[28d d/w]","[Laxatives]","[Client ID]=""" & [Client ID] & """ And
[Ass #]=" & [Ass #] & " And [Assessment date]=" & [Assessment date]),0)

Client ID is text
Ass # (shor for assessment number) is number
Assessment date is date

My data is not being shown. It just says 0, when it should be 7

Can anyone see where the problem lies?

 

[John Spencer]

Dates are normally delimited with # characters in Access.

=IIf(DLookUp("[cmps 28d]","[Laxatives]","[Client ID]=""" & [Client ID] & """
And [Ass #]=" & [Ass #] & " And [Assessment date]=" & [Assessment
date])=True,
DLookUp("[28d d/w]","[Laxatives]","[Client ID]=""" & [Client ID] & """ And
[Ass #]=" & [Ass #] & " And [Assessment date]=#" & [Assessment date] &
"#"),0)

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..