Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Dlookup is Null?

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
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…

581 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