Help with syntax (again!)

[stevekos07]

I find I am continually asking for help with the correct syntax in expressions. In this case it might be the construction of the arguments as well. I have a text box in a form which I want to show the details of a new message for a client based on a criteria of >Date()-2.

In this case I am trying to use a DLookup function within an IIF function. I want to display the contents of the field [Details] associated with the ClientID for that record in the query. The syntax I am trying to use is:

=IIf(DLookUp("[ClientID]","qryNewClientMessages","[ClientID]=" & [ClientID]),[qryNewClientMessages]![Details]," ")

I have also tried:

=IIf(DLookUp("[ClientID]","qryNewClientMessages","[ClientID]=" & [ClientID]),[Details]," ")

I get a correct but inadequate result if I use the expression:

=IIf(DLookUp("[ClientID]","qryNewClientMessages","[ClientID]=" & [ClientID]),"New Message"," ")

Help greatly appreciated.

 

[MarkK]

How do we troubleshoot a "correct but inadequate result?"

 

[stevekos07]

How do we troubleshoot a "correct but inadequate result?"

Mmm, yes. What I meant to say is that I got a workable result from the syntax:

=IIf(DLookUp("[ClientID]","qryNewClientMessages","[ClientID]=" & [ClientID]),"New Message"," ")

but the result I ideally am looking for is to display the contents of the field [Details] from the query [qryNewClientMessages] rather than just a generic message.

I hope that's a bit clearer.

 

[MarkK]

Well, DLookup() looks up the value of a field in a table, but you are looking up the field called ClientID. Change that to Details.

=DLookUp("Details", "qryNewClientMessages", "ClientID = " & [ClientID])

What happens if you do something along those lines???