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

Posted on 2009-07-04
Medium Priority
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

Question by:kgittinger
  • 5
  • 3
LVL 59
ID: 24777626
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.

Author Comment

ID: 24777650
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 Comment

ID: 24777675
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.


Author Comment

ID: 24777800
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.
LVL 59
ID: 24777872
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...

Author Comment

ID: 24778062
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.
LVL 59

Accepted Solution

Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 total points
ID: 24778092
OK, if your working inside of a query, then you don't want to use Dlookup(). A Dlookup is really nohing more then a SQL statement, which you can do right in the query. What you want to do is this:
1. Open your first query in design view.
2. Add the 2nd query to it, using "add table" - You can use a table or a query as a "table" to get more data from.
3. Click on the customer ID in your first table and then drag it over to the customer ID in the table you just added.
4. Now double click on the line between the first table and the table you just added.
5. Select the option (2 or 3) that gives you all records from your first table and only records from the 2nd table where a match occurs - this is called an outer join.

You now have the data to work with from the 2nd query if it's there. At this point, you could filter for records from your first table where there is data or where there is not (by pulling down the customer ID from the 2nd table and then doing a IS NULL or NOT IS NULL check in the criteria line).
You can also define a column like this in the query:
DataExists:IIF([<2nd Query name>].[Customer #] IS NULL,"NO CUSTOMER DATA", "CUSTOMER DATA EXISTS")
Make sure you change <2nd query name> to the name of the 2nd query as it appears in the designer window.

Author Closing Comment

ID: 31599797
Thank you for the concise information!

Featured Post

7 new features that'll make your work life better

It’s our mission to create a product that solves the huge challenges you face at work every day. In case you missed it, here are 7 delightful things we've added recently to monday to make it even more awesome.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Implementing simple internal controls in the Microsoft Access application.
An introductory discussion about Oracle Analytic Functions which are used to calculate or compute Aggregate values, based on a group of rows.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

600 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