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

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)))

kgittingerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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
kgittingerAuthor 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")  
0
kgittingerAuthor 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
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

kgittingerAuthor 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.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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
kgittingerAuthor 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.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kgittingerAuthor Commented:
Thank you for the concise information!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.