Dlookup is Null?

Tell me why this doesn't work.

DutyByQuarter is a Query
[FltDate By Quarter] is a field that started as a Date
        FltDate By Quarter: Format$(PilotDataCombined.P1OnDutyDate,"\Qq yyyy")
        P1OnDutyDate is Date
'---------------
Public Function TimeThisQuarter(PilotID As Integer, Interval As String)
TimeThisQuarter = Nz(DLookup("[Sum of FltTime]", "[DutyByQuarter]", "[P1] = " & PilotID _
        & " And [FltDate By Quarter] = " & "'Interval'"))
End Function
'---------------
MsgBox TimeThisQuarter(2,"Q2 2006") 'comes up Empty
This varibles enter into the Query as a check work correctly
If I reduce the function to only PilotID, Dlookup works correctly.

buzzcarterAsked:
Who is Participating?
 
puppydogbuddyConnect With a Mentor Commented:
Try it without the brackets around "DutyByQuarter"....and, as per LSM Consulting, you should add the nz function with a return value if null.

Public Function TimeThisQuarter(PilotID As Integer, Interval As String)
TimeThisQuarter = Nz(DLookup("[Sum of FltTime]", "DutyByQuarter", "[P1] = " & PilotID _
        & " And [FltDate By Quarter] = " & "'Interval'"))
End Function
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
You haven't specified what the Nz function should return if it returns a null ... is that what your question is?

Nz(DLookup("[Sum of FltTime]", "[DutyByQuarter]", "[P1] = " & PilotID & " And [FltDate By Quarter] = " & "'Interval'"))

This should return the value in [Sum of FltTime] ... if not, and DLookup returns a Null value, then Nothing is returned. If you want to return something else:

Nz(DLookup("[Sum of FltTime]", "[DutyByQuarter]", "[P1] = " & PilotID & " And [FltDate By Quarter] = " & "'Interval'"), "Your_Nz_Return_Value_Here")

0
 
buzzcarterAuthor Commented:
LSM ConSulting

No, I know about the Nz return value on Null.  I cannot get the Dlookup to work with the criteria.
0
 
buzzcarterAuthor Commented:
I got it.

I changed the field in my source query, removing the space.

FltDate By Quarter: Format$([PilotDataCombined].[P1OnDutyDate],"qyyyy")

Public Function TimeThisQuarter(PilotID As Integer, Interval)

TimeThisQuarter = Nz(DLookup("[Sum of FltTime]", "DutyByQuarter", "[P1] = " & PilotID & _
    " And [FltDate By Quarter] = " & Interval), "No Data Found")

End Function

Thanks Everyone
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.