Solved

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

Posted on 2009-07-04
8
1,204 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
  • 5
  • 3
8 Comments
 
LVL 57
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
 

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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 57
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 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

759 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now