Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-14-2012, 02:02 AM   #1
shdale
Newly Registered User
 
Join Date: Nov 2012
Posts: 8
Thanks: 2
Thanked 0 Times in 0 Posts
shdale is on a distinguished road
If "TableA.FieldA" exists in "TableB.FieldB" then return "1" else "2"

Hi, I need help to enter a field entry in the MS Access 2003 > Query > Design View.

Basically, I have two tables, one has a manually entered postcode, the other table has a list of postcodes. I want the query to check the manually entered postcode against the second table with the list of postcodes, and return a 1 if the postcode exists in the lookup table, and a 2 if it does not find the postcode.

Any help much appreciated.

Thanks, S

shdale is offline   Reply With Quote
Old 11-14-2012, 02:53 AM   #2
MStef
Newly Registered User
 
Join Date: Oct 2004
Location: Zagreb - Croatia
Posts: 2,251
Thanks: 0
Thanked 84 Times in 77 Posts
MStef will become famous soon enough MStef will become famous soon enough
Re: If "TableA.FieldA" exists in "TableB.FieldB" then return "1" else "2"

Look at "DemoPostCodeA20022003.mdb" (attachment, zip).
Look at TableA, TableB, Query1, Module1.
Run query and see, I think it is what you want.
Attached Files
File Type: zip DemoPostCodeA20022003.zip (11.6 KB, 167 views)
MStef is offline   Reply With Quote
Old 11-14-2012, 05:50 AM   #3
shdale
Newly Registered User
 
Join Date: Nov 2012
Posts: 8
Thanks: 2
Thanked 0 Times in 0 Posts
shdale is on a distinguished road
Re: If "TableA.FieldA" exists in "TableB.FieldB" then return "1" else "2"

Many thanks MStef, can I ask where the 'FieldAB' comes from/what does it reference in the query? I can't see it in the tables.

shdale is offline   Reply With Quote
Old 11-14-2012, 06:12 AM   #4
MStef
Newly Registered User
 
Join Date: Oct 2004
Location: Zagreb - Croatia
Posts: 2,251
Thanks: 0
Thanked 84 Times in 77 Posts
MStef will become famous soon enough MStef will become famous soon enough
Re: If "TableA.FieldA" exists in "TableB.FieldB" then return "1" else "2"

FieldAB isn't a field it is a FUNCTION in Module1.

Last edited by MStef; 11-14-2012 at 06:18 AM.
MStef is offline   Reply With Quote
Old 11-14-2012, 06:18 AM   #5
shdale
Newly Registered User
 
Join Date: Nov 2012
Posts: 8
Thanks: 2
Thanked 0 Times in 0 Posts
shdale is on a distinguished road
Re: If "TableA.FieldA" exists in "TableB.FieldB" then return "1" else "2"

I know I sound thick - but what do you mean by a function in Module1? All I know about are tables and the query.
shdale is offline   Reply With Quote
Old 11-14-2012, 06:21 AM   #6
MStef
Newly Registered User
 
Join Date: Oct 2004
Location: Zagreb - Croatia
Posts: 2,251
Thanks: 0
Thanked 84 Times in 77 Posts
MStef will become famous soon enough MStef will become famous soon enough
Re: If "TableA.FieldA" exists in "TableB.FieldB" then return "1" else "2"

You can make a FUNCTION (if you know to do that).
Function can be called from form, also from a query.
MStef is offline   Reply With Quote
The Following User Says Thank You to MStef For This Useful Post:
shdale (11-16-2012)
Old 11-16-2012, 01:47 AM   #7
shdale
Newly Registered User
 
Join Date: Nov 2012
Posts: 8
Thanks: 2
Thanked 0 Times in 0 Posts
shdale is on a distinguished road
Re: If "TableA.FieldA" exists in "TableB.FieldB" then return "1" else "2"

Many, many thanks - this worked really well :-)

shdale is offline   Reply With Quote
Old 11-16-2012, 02:09 AM   #8
MStef
Newly Registered User
 
Join Date: Oct 2004
Location: Zagreb - Croatia
Posts: 2,251
Thanks: 0
Thanked 84 Times in 77 Posts
MStef will become famous soon enough MStef will become famous soon enough
Re: If "TableA.FieldA" exists in "TableB.FieldB" then return "1" else "2"

My congratulations !!
MStef is offline   Reply With Quote
Old 10-29-2014, 05:20 AM   #9
willbeend
Newly Registered User
 
Join Date: Oct 2014
Posts: 1
Thanks: 1
Thanked 0 Times in 0 Posts
willbeend is on a distinguished road
Re: If "TableA.FieldA" exists in "TableB.FieldB" then return "1" else "2"

I am also interested in this kind of solution. The only problem is that in "FieldA" I have multiple words (ex. in Table A - 2nd row of FieldA the value is 554 4485533 00) and I also want to return de value "1" if a row of FieldA contains a matching word in any TableB - FieldB records.

In Excel I solved the problem with this array formula,
{=OR(NOT(ISERROR(SEARCH('TableB'!$A$1:$A$3;'TableA '!A1))))}
but I just can't translate it to Access

willbeend is offline   Reply With Quote
Reply

Tags
exists , lookup , postcode , table

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Expr1: Format("012P","0000") Why I get "0001" amolin General 4 10-10-2012 08:33 PM
Query to Convert "Name" field to "Last Name" "First Name" "Middle Name"? derekbeck Queries 5 03-26-2012 03:23 PM
Qry To Exclude Record If All Fields On Page Is Blank Except Field "xx", "yy" And "zzz vancey Queries 0 03-17-2011 01:43 AM
Defaulting a "Medium Time" field to be "p.m." instead of "a.m."? Cobo Tables 4 04-26-2010 02:37 PM
One query to return either records within "Date Range" or "12-Month Rolling" Cosmos75 Queries 2 05-26-2005 03:45 AM




All times are GMT -8. The time now is 08:28 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World