Solved

Ms Access - VBA DLookup returning Null Values

Posted on 2008-10-16
2
925 Views
Last Modified: 2013-12-25
Afternoon,

I have a set of DLookups which populate a text field accoring to what ID number someone puts in another text field. Can someone tell me how I would output a message box if that ID retrn no records? My current code is;

strSPNValue = txtSPN.Value
txtSPNName = DLookup("[spn_name]", "Sel_Historical_SPN_Lookup", "SPN ='" & strSPNValue & "'")

Thanks
0
Comment
Question by:andyb7901
2 Comments
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
Use an If-then block.  If Dlookup returns a Null, then there are no records found.

Dim varLU as Variant
strSPNValue = txtSPN.Value
VarLU =  DLookup("[spn_name]", "Sel_Historical_SPN_Lookup", "SPN ='" & strSPNValue
if isNull(VarLU) then
    MsgBox "No records found"
   ' etc
end if
txtSPNName = VarLU
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
Comment Utility
Or:

txtSPNName = Nz(DLookup("[spn_name]", "Sel_Historical_SPN_Lookup", "SPN ='" & strSPNValue & "'"), "")
If txtSPNName = "" Then
    'do something
End If
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…

763 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now