Solved

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

Posted on 2009-07-04
8
1,221 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

863 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

23 Experts available now in Live!

Get 1:1 Help Now