Link to home
Start Free TrialLog in
Avatar of buzzcarter
buzzcarter

asked on

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.

SOLUTION
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of buzzcarter
buzzcarter

ASKER

LSM ConSulting

No, I know about the Nz return value on Null.  I cannot get the Dlookup to work with the criteria.
ASKER CERTIFIED SOLUTION
Avatar of puppydogbuddy
puppydogbuddy
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of buzzcarter
buzzcarter

ASKER

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