?
Solved

Dlookup is Null?

Posted on 2006-07-21
4
Medium Priority
?
615 Views
Last Modified: 2008-03-03
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.

0
Comment
Question by:buzzcarter
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 17157929
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
 

Author Comment

by:buzzcarter
ID: 17158506
LSM ConSulting

No, I know about the Nz return value on Null.  I cannot get the Dlookup to work with the criteria.
0
 
LVL 38

Accepted Solution

by:
puppydogbuddy earned 500 total points
ID: 17158572
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
 

Author Comment

by:buzzcarter
ID: 17158705
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

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

718 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question