Trouble with DLOOKUP in a Subform

[Mubashir sabir]

I am trying to look up a memo field called [Message] on a table called [Patient Contact Info Table] That has matching [MR#] and [Latest Date]. This is being done on a Subform, and the [SubformMR#] is linking the main form with the Subform.




When I use
=DLookUp("[Message]","[Patient Contact Info Table]","[MR#] = [SubFormMR#]" & "And [Date of Contact]=[Latest Date])
It does not work and just return a blank

When I use
=DLookUp("[Message]","[Patient Contact Info Table]","[MR#] = [SubFormMR#]" & "And [Date of Contact]=#8/29/2015#")
It works but obviously only gives me the message for that specific date

I've tried proper reference to the subform control [Latest Date] but still not working.
=DLookUp("[Message]","[Patient Contact Info Table]","[MR#] = [SubFormMR#]" & "And [Date of Contact]=Forms![View Patient Info Form]![New Contact Info Subform].Form![Latest Date]")
Not sure what I am doing wrong

[Latest Date]=Max([Date of Contact]) This gives Latest Date properly, but I can't get the DLOOKUP function to us this date. The MR# seems to be working.

[ranman256]

you shouldnt need Dlookup on a form.
use the query behind the form to a bound field.

[ssanfu]

I agree with ranman256. The memo field should be in the query.


I did notice you have spaces and special characters in your object names.

=DLookUp("[Message]","[Patient Contact Info Table]","[MR#] = [SubFormMR#]" & "And [Date of Contact]=[Latest Date])

Should use only letters, numbers and possibly the underscore.
Instead of
..."MR#", I would use "MR_Num"
..."Date of Contact" would be "Date_of_Contact" or "DateOfContact"