Solved

Getting DLookup to return a Null value in Access - VB6

Posted on 2006-07-18
7
436 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
  • 4
  • 2
7 Comments
 
LVL 34

Expert Comment

by:flavo
Comment Utility
If varX Is Null Then
ValidTxt = "Null"

Dave
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
Comment Utility
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
Comment Utility
If varX Is Null then    gave me a runtime error 424 for some reason.  The other suggestion worked great.  Thanks
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 34

Expert Comment

by:flavo
Comment Utility
what?
0
 
LVL 34

Expert Comment

by:flavo
Comment Utility
Well I'll be...I doesn't work :-(

Dave
0
 
LVL 65

Expert Comment

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

Expert Comment

by:flavo
Comment Utility
Yeap :-(
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…

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

9 Experts available now in Live!

Get 1:1 Help Now