Dlookup to find values not in table?

[Guest]

I am using DLookup in VBA to pull certain values from a table. I have this
working successfully.

However, now I want to use Dlookup to determine if a value is not in a table
so I may provide a message to the user and close the form. I have created a
message box showing that nothing was found in my Dlookup, but when I try to
use a If Isnull or If field = "", to return my message it does not seem to
work. Am I doing something incorrectly or is there a different or better way
to do this?

strName = DLookup("[First Name]", "Users", "[Userid] = " & Chr$(34) _
& strID & Chr$(34)) & " " & DLookup("[Last Name]", "Users",
"[Userid] = " _
& Chr$(34) & strID & Chr$(34))

MsgBox "strName = " & strName 'testing value found

If IsNull(strName) Then
Msgbox "Second Message Here"
End If

Any guideance would be greatly appreciated.
Thanks!

 

[Andi Mayer]

I am using DLookup in VBA to pull certain values from a table. I have this
working successfully.

However, now I want to use Dlookup to determine if a value is not in a table
so I may provide a message to the user and close the form. I have created a
message box showing that nothing was found in my Dlookup, but when I try to
use a If Isnull or If field = "", to return my message it does not seem to
work. Am I doing something incorrectly or is there a different or better way
to do this?

strName = DLookup("[First Name]", "Users", "[Userid] = " & Chr$(34) _
& strID & Chr$(34)) & " " & DLookup("[Last Name]", "Users",
"[Userid] = " _
& Chr$(34) & strID & Chr$(34))

MsgBox "strName = " & strName 'testing value found

If IsNull(strName) Then
Msgbox "Second Message Here"
End If

Any guideance would be greatly appreciated.
Thanks!

your result is:
if noFirstName and no LastName then its a blank (between the two
Dlookup

if you want to combine last and first to see if it's there then use:

strName = DLookup("[First Name] & ' ' &[Last Name]", _
"Users", "[Userid] = '" & strID & "'" )

if strname=" " then
Msgbox "nothing found",vbcritical
else
Msgbox strname &" found"
endif

 

[Marshall Barton]

I am using DLookup in VBA to pull certain values from a table. I have this
working successfully.

However, now I want to use Dlookup to determine if a value is not in a table
so I may provide a message to the user and close the form. I have created a
message box showing that nothing was found in my Dlookup, but when I try to
use a If Isnull or If field = "", to return my message it does not seem to
work. Am I doing something incorrectly or is there a different or better way
to do this?

strName = DLookup("[First Name]", "Users", "[Userid] = " & Chr$(34) _
& strID & Chr$(34)) & " " & DLookup("[Last Name]", "Users",
"[Userid] = " _
& Chr$(34) & strID & Chr$(34))

MsgBox "strName = " & strName 'testing value found

If IsNull(strName) Then
Msgbox "Second Message Here"
End If

DLookup does indeed return Null if doesn't find a record.

BUT, since you're concatenating the DLookup results to what
appears to be a space character, the strName variable will
contain that space even when the DLookups return Null.

If you want strName to be Null when a match is not found,
the strName must be declared as a Variant and you would have
to use + instead of & to concatenate the values.

I don't know what other code you may have, but even this
much would probably be at least a little faster if you
instead opened a recordset to the matching data:

strSQL = "SELECT [First Name], [Last Name] " _
& "FROM Users " _
& "WHERE Userid=" & Chr$(34) & strID & Chr$(34)
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
strName = rs![First Name] & " " [Last Name]
Else
strName = ""
End If
rs.Close : Set rs = Nothing
Set db = Nothing

 

[Brian]

I am using DLookup in VBA to pull certain values from a table. I have this
working successfully.

However, now I want to use Dlookup to determine if a value is not in a table
so I may provide a message to the user and close the form. I have created a
message box showing that nothing was found in my Dlookup, but when I try to
use a If Isnull or If field = "", to return my message it does not seem to
work. Am I doing something incorrectly or is there a different or better way
to do this?

strName = DLookup("[First Name]", "Users", "[Userid] = " & Chr$(34) _
& strID & Chr$(34)) & " " & DLookup("[Last Name]", "Users",
"[Userid] = " _
& Chr$(34) & strID & Chr$(34))

MsgBox "strName = " & strName 'testing value found

If IsNull(strName) Then
Msgbox "Second Message Here"
End If

Any guideance would be greatly appreciated.
Thanks!

Something like this might be better (and more efficient):

strName = Trim(Nz(DLookup("[First Name] & "" "" & [Last Name]", "Users",
"[Userid] = " & Chr$(34) _
& strID & Chr$(34))))

This only does one Dlookup instead of two, it converts a null result to a
zero length string so you can reliably test for it, and it gets rid of the
extraneous space should only one part of the name be present.

 

[Tim Ferguson]

=?Utf-8?B?S2F0aHkgLSBMb3Z1bGxv?=

However, now I want to use Dlookup to determine if a value is not in a
table so I may provide a message to the user and close the form.

You can either check the returned value for Null

varTemp = DLookup(etc, etc)
If IsNull(varTemp) Then
' etc


or use DCount instead:

If DCount("*", "Somewhere", strCriterion)=0 Then
' etc


but if you are going to use the value anyway, then the first one is
obviously superior,


HTH


Tim F