[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Getting DLookup to return a Null value in Access - VB6

Posted on 2006-07-18
7
Medium Priority
?
450 Views
Last Modified: 2012-06-22
Hello experts,

This should be pretty easy but I cannot figure it out.  I am new to VB and having problems.

I am trying to make a form in Access that will allow the user to input their employee number into a textbox.  Once they click the button, it will look for thier name in a table and retreive their info or return a Null value if the number is not found.  I have had not problems having it find records that are there, but I cannot figure out how to make it return a Null if there is no record found.

Here is my code:

' NumberInputTxt , FirstNameTxt , and ValidTxt  are all textboxes
' identipass is the table I am looking in

' the problem is I cannot get varX to = Null.  I have also tried if varX="" which did not work


Private Sub numberinputtxt_Click()

Dim CardNumber As String
Dim varX As Variant

CardNumber = NumberInputTxt.Text

varX = DLookup("[First]", "Identipass", "[Number]= " & CardNumber)

FirstNameTxt = varX

If varX = Null Then
ValidTxt = "Null"

Else

ValidTxt = "Found"
End If

End Sub

----------------------------------------------------------

Thanks in advance!
0
Comment
Question by:paries
[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
  • 4
  • 2
7 Comments
 
LVL 34

Expert Comment

by:flavo
ID: 17133256
If varX Is Null Then
ValidTxt = "Null"

Dave
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 2000 total points
ID: 17133291
DLOOKUP always returns null if no record foind

Flavo has given u the right syntax. The other way  is    If IsNull(varx) = True then



Alternativly, u can use NZ to return a string instead

varX = NZ(DLookup("[First]", "Identipass", "[Number]= " & CardNumber),"")
if varX = "" then
    msgbox "Not found"


0
 

Author Comment

by:paries
ID: 17133436
If varX Is Null then    gave me a runtime error 424 for some reason.  The other suggestion worked great.  Thanks
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 34

Expert Comment

by:flavo
ID: 17133475
what?
0
 
LVL 34

Expert Comment

by:flavo
ID: 17133500
Well I'll be...I doesn't work :-(

Dave
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17133615
Dave, thinking about it, I think may be it will work for object references only
0
 
LVL 34

Expert Comment

by:flavo
ID: 17134297
Yeap :-(
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

650 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