[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2009-07-04
8
Medium Priority
?
1,416 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)))

0
Comment
Question by:kgittinger
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
8 Comments
 
LVL 58
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.
JimD.
 
0
 

Author Comment

by:kgittinger
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")  
0
 

Author Comment

by:kgittinger
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
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

Author Comment

by:kgittinger
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.
0
 
LVL 58
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...
JimD.
0
 

Author Comment

by:kgittinger
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.
0
 
LVL 58

Accepted Solution

by:
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.
JimD.
0
 

Author Closing Comment

by:kgittinger
ID: 31599797
Thank you for the concise information!
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

649 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