using Dlookup in a function

[max]

I have a table called subject_teacher_details with fields:
initials,subject_taught and class. I would like to use
Dlookup in a function called from a textbox on a report to
display the corresponding "initials" of the particular
subject's teacher for a particular class. I have done the
following but keep getting "#error" on the report.

please help.

Function findInitials( students_class, subject ) As String
' students_class passed as reference to a field on
report's record source
' subject passed as string ,ie "english"

Dim init As Variant 'holds the subject teachers
initials to be returned
'by this function





init = DLookup(" [subject_teacher_details]!
[initials]", "subject_teacher_details", _
" [subject_teacher_details]![class] =&'students_class'&
And [subject_teacher_details]![subject_taught]
=& 'subject'&")

If IsNull(init) = True Then
findInitial = CStr(" ") 'if dlookup returns null,
show blank space on report
Else
findInitial = CStr(init)
End If

End Function

 

[Nikos Yannacopoulos]

Max,

Your problem is in the use of quotes. Try this one:

init = DLookup(" [subject_teacher_details]![initials]",
"subject_teacher_details", _
"[subject_teacher_details]![class] = ' " & students_class & " ' And
[subject_teacher_details]![subject_taught] = ' " & subject & " ' ")

(I understand both arguments are string type). Also note that while the
fuinction mane is findInitials, you assign the result to findInitial (missed
the s at the end), which is another variable to Access!. Fix.

HTH,
Nikos

 

[Ted]

Hi,

I believe that all of the references to
[subject_teacher_details] shouldn't be needed since that
table is already defined as the domain by the second
argument. It probably wouldn't cause a problem, but if
the function still isn't working after Nick's corrections
you may want to try:

init = DLookup("[initials]","subject_teacher_details",
"[class] = '" & students_class & "' And [subject_taught]
= '" & subject & "'")

Also, if you want to, you could get rid of your last if
statement by using the Nz() function, as follows:

findInitials = Cstr(Nz(init,""))

But the Cstr() function shouldn't be needed since the
initials should not be a numeric value, so you should be
able to just use:

findInitials = Nz(init,"")

Also, if you wanted something other than a zero length
string when the value is Null you could enter something
such as "None" or "?" instead of just "" in the Nz()
funciton.

Hope that helps,

Ted

-----Original Message-----
Max,

Your problem is in the use of quotes. Try this one:

init = DLookup(" [subject_teacher_details]![initials]",
"subject_teacher_details", _
"[subject_teacher_details]![class] = ' " & students_class & " ' And
[subject_teacher_details]![subject_taught] = ' " & subject & " ' ")

(I understand both arguments are string type). Also note that while the
fuinction mane is findInitials, you assign the result to findInitial (missed
the s at the end), which is another variable to Access!. Fix.

HTH,
Nikos

I have a table called subject_teacher_details with fields:
initials,subject_taught and class. I would like to use
Dlookup in a function called from a textbox on a report to
display the corresponding "initials" of the particular
subject's teacher for a particular class. I have done the
following but keep getting "#error" on the report.

please help.

Function findInitials( students_class, subject ) As String
' students_class passed as reference to a field on
report's record source
' subject passed as string ,ie "english"

Dim init As Variant 'holds the subject teachers
initials to be returned
'by this function





init = DLookup(" [subject_teacher_details]!
[initials]", "subject_teacher_details", _
" [subject_teacher_details]![class] =&'students_class'&
And [subject_teacher_details]![subject_taught]
=& 'subject'&")

If IsNull(init) = True Then
findInitial = CStr(" ") 'if dlookup returns null,
show blank space on report
Else
findInitial = CStr(init)
End If

End Function

.