Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Dlookup is Null?

Posted on 2006-07-21
4
Medium Priority
?
618 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
  • 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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

916 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