Dlookup to find values not in table?

  • Thread starter Thread starter Guest
  • Start date Start date
G

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!
 
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!
Click to expand...

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
 
Kathy said:
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
Click to expand...


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
 
Kathy - Lovullo said:
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!
Click to expand...

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.
 
=?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.
Click to expand...

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
 
Back
Top