CODEWITHSUNDEEP
vbams-access
vorbis
vorbis

Reputation: 13

Looking for more values in a column of a table

I have a query in Access which opens a table. In this table I should check one column. This column can contain several values, or can be empty. My search terms are a bunch of strings and integers, for example "V1234" or 974623.

My final aim is to have a MsgBox if the column is empty, or contains any data out of my search terms.

I was thinking of the DCount function, but it is good for 1 criteria at once:

If DCount("VendorCode", "MyTable", "[VendorCode] = 'V1234'") = 0 Then
    MsgBox....

Question 1: Can I somehow use a variant a a criteria?

For example:

Dim vendors As Variant

vendors = Array("V1234", "V2345", "V3456", 9543435)

If DCount("VendorCode", "MyTable", "[VendorCode] = vendors") = 0 Then
    MsgBox....*

Question 2: If question 1 is a complete dead end, what is the most effective way to look for more data in one column?

Upvotes: 0

Views: 30

Answers (1)

Gustav
Gustav

Reputation: 55816

You can use In:

vendors = Array("V1234", "V2345", "V3456", "9543435") ' Note: All strings.

If DCount("VendorCode", "MyTable", "[VendorCode] In ('" & Join(vendors, "','") & "')") = 0 Then
    MsgBox....*

Upvotes: 1

Related Questions