DLookup Value Not Equal to Request

D

DMH

Please look at this string and see if you can tell me how to change it. This
one tells me when something is already in the database. What I need to know
is when a requested NSN does not match what is already in the database.
Thanks –

=DLookUp("NSN","EMEDS EQUIPMENT DATA","NSN = [txtNSN] ") Is Null
 
B

BruceM

With spaces in the table names you need brackets, and you need to handle the
Where differently if txtNSN contains a text value:
=DLookUp("NSN","[EMEDS EQUIPMENT DATA]","NSN = """ & [txtNSN] & """")

However, I'm not clear on where this expression appears, or what you would
have happen if the NSN value is not already in the table. Maybe you want
something like:
=IIf(DLookUp("NSN","[EMEDS EQUIPMENT DATA]", _
"NSN = """ & [txtNSN] & """") Is Null,"Not There","OK")

Note that the underscore is for clarity here, and is not part of the
expression.
 
K

Klatuu

The syntax for your expression would only work in a query and then it will
return True if the the value is not in the database an False if not in the
databasse, but I am not sure it is even correct in a query.

In VBA, you would use:

If IsNull(DLookUp("NSN","[EMEDS EQUIPMENT DATA]","NSN = """ & [txtNSN] &
""""")) Then
MsgBox "Not Found"
Else
MsgBox "Found It"
End If

Also, note the chage in the criteria part. It will not work as your wrote
it. The reference to the control has to be outside the quotes. The syntax
I provided assumes NSN is a text field. If it is a numeric field, the
syntax would be:

DLookUp("NSN","[EMEDS EQUIPMENT DATA]","NSN = " & [txtNSN])

Also note the brackets around the domain name. If you use spaces in a name
(bad idea), you have to use brackets around the name.
 
Top