We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

What is the equivelent of ISNA(vlookup) in Access 2003?

Medium Priority
1,883 Views
Last Modified: 2012-05-07
I would like to lookup from 1 query to another  by customer number and have it return the customer # or a NotHere response but I do not know the sintax.  In Excel it would be
=IF(ISNA(VLOOKUP(A197,Sheet2!A:L,1,FALSE)),"NotHere",(VLOOKUP(A197,Sheet2!A:L,1,FALSE)))

Comment
Watch Question

Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
You'd probably want to use DLookup(), which will return a single value from a table or query and combine that with IIF(), which tests a condition and then returns an argument based on whether the condition was true or false.
However with that said, one place you don't want to use Dlookup() is inside a query.  What the DLookup does can be accomplished in a query just by using table joins.
IIF() has three arguments:

IIF(<condition>,<value to return if condition true>,<value to return if condition false>)
Dlookup also has three arguments:
Dlookup("<name of field to return>","<Name of table or query to look in>","<Where arguement to filter the data>")
  You can look in the on-line help for more specifics or bounce back here for more detail if you need it.
JimD.
 

Author

Commented:
What I need to do is compare my 2 queries if cust has a value in  field x tell me...

Do you mean iif ([query1].[customer], "yes", "no")  

Author

Commented:
Expr1:iif([Query]! [Customer #] , [Query (2)]![Customer #], Yes, No) tells me that the expression has an invalid .(dot) or ! operator or invalid parentheses  You may have entered an invalid identifier or typed parentheses following the Null constant

Author

Commented:
I have also tried this:
Expr1: IIf(DLookUp("[Query]![Customer #]","[Query (2)]![Customer #]", is not null, "Yes","No")
and it won't let me out of the criteria box.
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
I'm a bit confused where your using this; is it in a query?  If so then as I mentioned, you really don't want to be using Dlookup().
Let me know how your using this...
JimD.

Author

Commented:
I wouldl ike to know if a value in a certain field in my query exists in query (2)  I have tried what I interpreted you to mean both the iif and the dlookup and cannot get either to work.
President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
Thank you for the concise information!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.