If "TableA.FieldA" exists in "TableB.FieldB" then return "1" else "2" (1 Viewer)

S

shdale

New member

Local time

Today, 08:56

Joined

Nov 14, 2012

Messages

8

• Nov 14, 2012

• #1

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

 

M

MStef

Registered User.

Local time

Today, 16:56

Joined

Oct 28, 2004

Messages

2,251

• Nov 14, 2012

• #2

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.

 

Attachments

• DemoPostCodeA20022003.zip

  11.6 KB · Views: 196

S

shdale

New member

Local time

Today, 08:56

Joined

Nov 14, 2012

Messages

8

• Nov 14, 2012

• #3

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.

 

M

MStef

Registered User.

Local time

Today, 16:56

Joined

Oct 28, 2004

Messages

2,251

• Nov 14, 2012

• #4

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: Nov 14, 2012

S

shdale

New member

Local time

Today, 08:56

Joined

Nov 14, 2012

Messages

8

• Nov 14, 2012

• #5

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.

 

M

MStef

Registered User.

Local time

Today, 16:56

Joined

Oct 28, 2004

Messages

2,251

• Nov 14, 2012

• #6

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.

 

S

shdale

New member

Local time

Today, 08:56

Joined

Nov 14, 2012

Messages

8

• Nov 16, 2012

• #7

Re: If "TableA.FieldA" exists in "TableB.FieldB" then return "1" else "2"

Many, many thanks - this worked really well

 

M

MStef

Registered User.

Local time

Today, 16:56

Joined

Oct 28, 2004

Messages

2,251

• Nov 16, 2012

• #8

Re: If "TableA.FieldA" exists in "TableB.FieldB" then return "1" else "2"

My congratulations !!

 

W

willbeend

New member

Local time

Today, 16:56

Joined

Oct 29, 2014

Messages

1

• Oct 29, 2014

• #9

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