Like Statement with Multiple Values

[cjvenables]

Hey guys,

I am able to use the LIKE statement in Access 2007 to search for 1 vendor number, but I now need to be able to search for 28 different vendor numbers. How do I do that using the LIKE function? Should I be using something else?

Also, if I have 2 tables that are the same type of data, only different years (2010 & 2009), how would I run a query to return values from both tables (finding vendor numbers in both tables)? Would this be easier to run in VBA? I want to keep the tables separate as i do not always need to find data from multiple years.

Thanks!

 

[BKZwaan]

Aside from using the Like command followed by Or, I'm not aware of a better way.

For the two tables you will need to Append one of them to the other or create a new table using the Make Table Querie and then Append that with the the table you did not use to Make a new table.

 

[AlexHedley]

Is there a reason you are using LIKE?
Do you not have a list of the vendor numbers you need to search for?

You could create a UNION on the tables you wish to check for.
Then just filter to only show the vendors you want.

SELECT [2001].VendorID, [2001].VendorName
FROM 2001, Vendors
WHERE ((([2001].VendorID)=[Vendors].[VendorID]))
UNION ALL
SELECT [2002].VendorID, [2002].VendorName
FROM 2002, Vendors
WHERE ((([2002].VendorID)=[Vendors].[VendorID]))

Result
VendorID VendorName
1 a
3 c
1 a
3 c


Tables
2001
VendorID VendorName
1 a
2 b
3 c

2002
VendorID VendorName
1 a
2 b
3 c

Vendors
VendorID
1
3

 

[cjvenables]

@AlexHedley

I have the specific vendor numbers, I just don't know what to use to search them other than Like. I will take any easier way.

Thanks!