DLookup Syntax for text criteria

  • Thread starter Thread starter bymarce
  • Start date Start date
B

bymarce

I have a form called "WorkAssignments" on which my user can enter or look up
wokr assignments. I have a button that I want to send a filtered report
based on the initials of the person to whom the work was assigned and the
date on which it was assigned. I have a table called "Personel" that
contains first and last names, initials, and email addresses. I want to look
up the email addres in the "Personel" table based on the initials in the
unbound control "fAssignedTo" on the form. "fAssigned" to is used for
filtering the form. When I try to run this code it says "Invalid use of
Null" and highlights the Dlookup statement. How do I fix it?
Marcie

Dim SendTo As String
SendTo = DLookup("", "Personel", "[Initials] = ""Me.fAssignedTo""")
Dim strWhere As String
If Me.FilterOn Then
strWhere = Me.Filter
End If
Debug.Print strWhere

Dim MySubject As String, MyMessage As String
SendTo = Mail
MySubject = Me.MLO
MyMessage = "Please complete the following tests for " & Me.MLO & "."
DoCmd.SendObject acSendReport, "rptWorkAssignments", , SendTo, , ,
MySubject, MyMessage, False
 
Change the

SendTo = DLookup("", "Personel", "[Initials] = ""Me.fAssignedTo""")

To

SendTo = DLookup("[Email]", "Personel", "[Initials] = '"Me.fAssignedTo"'")

Single quote rather than double.
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.
 
Thanks. This fixed my problem.

RonaldoOneNil said:
SendTo = DLookup("", "Personel", "[Initials] = '" & Me.fAssignedTo &
"'")

[QUOTE="bymarce"]
I have a form called "WorkAssignments" on which my user can enter or look up
wokr assignments. I have a button that I want to send a filtered report
based on the initials of the person to whom the work was assigned and the
date on which it was assigned. I have a table called "Personel" that
contains first and last names, initials, and email addresses. I want to look
up the email addres in the "Personel" table based on the initials in the
unbound control "fAssignedTo" on the form. "fAssigned" to is used for
filtering the form. When I try to run this code it says "Invalid use of
Null" and highlights the Dlookup statement. How do I fix it?
Marcie

Dim SendTo As String
SendTo = DLookup("[Email]", "Personel", "[Initials] = ""Me.fAssignedTo""")
Dim strWhere As String
If Me.FilterOn Then
strWhere = Me.Filter
End If
Debug.Print strWhere

Dim MySubject As String, MyMessage As String
SendTo = Mail
MySubject = Me.MLO
MyMessage = "Please complete the following tests for " & Me.MLO & "."
DoCmd.SendObject acSendReport, "rptWorkAssignments", , SendTo, , ,
MySubject, MyMessage, False
[/QUOTE][/QUOTE]
Click to expand...
 
Back
Top