Query on Multi-value field

[kduschel]

I am trying to solve a problem with my DB. I have multiple communities and those communities have some unique vendors they use. I have a identifier field in Communities_Table called "Type_Field". The field gives the users 3 options "Type1", "Type2", "Type3". It does NOT allow for multiple selections. Then in my Vendors_Table there is a field called "VendorType_Field" which DOES allow multiple selections of "Type1", "Type2" and "Type3".



In a form, the user selects the Community and the Vendor. I want the vendor combo box to only show the vendors that have a type that is allowed for that community. I assume the Vendor_ComboBox needs to driven off of a query that gets filtered when the "Community_Field" is updated but I'm not sure how to filter a query on the multi-select field in the Vendors_Table. Any advice?

I'm an intermediate user on Access and a beginner on VBA.

Thanks!

[June7]

I NEVER use multi-value fields. Most experienced developers avoid them like the plague.

Review this discussion and the last post has another link.
http://answers.microsoft.com/en-us/o...f-fd69d99724f5

[orange]

I concur with June7. I suggest you identify the business rules/facts involved, and then design your tables and relationships based on the business requirements. I would avoid the use of multi valued fields.
Good luck.

[kduschel]

Thanks for the advice and the information! I believe I know the path I need to take.

[kduschel]

June7 / orange, I went back and decided on a different approach (I'm not sure how much better it is!), but I created a query with some IIF Statements. This concept works wonderfully in my basic test DB but once I try to do it in my full blown test environment I run into "This expression is typed incorrectly, or is too complicated to be evaluated". Below is my statement:

IIf([Forms]![Main_F2]![ProjectLocation_Txt]=1,1,"Null") Or IIf([Forms]![Main_F2]![ProjectLocation_Txt]=1,3,"Null") Or IIf([Forms]![Main_F2]![ProjectLocation_Txt]=2,2,"Null") Or IIf([Forms]![Main_F2]![ProjectLocation_Txt]=2,3,"Null") Or IIf([Forms]![Main_F2]![ProjectLocation_Txt]=3,3,"Null") Or IIf([Forms]![Main_F2]![ProjectLocation_Txt]=4,4,"Null").

What am I doing wrong?

Thanks!

[June7]

Why are the paramaters 1 and 2 repeated? How could the value 3 ever be returned?

Do not put Null within quote marks.

[kduschel]

Ahhh!!! I took the "" off and it worked! I hate silly mistakes! Below is an example of what I am trying to accomplish...just in case you have better way for me to achieve the same goal.

Thanks!


1 = MidAtlantic only vendors
2 = Southeast only vendors
3 = East
4 = Special District vendors

Most all of our vendors will have a type of EAST as they can be used for both MidAtlantic and Southeast projects. However, there are a several that are unique to the MA and SE. So if a Mid-Atlantic project is selected I want the user to see vendors with a type of 1 and 3. Same goes for a Southeast project (needs to see vendors that are type 2 and 3).

[June7]

Still don't see how value 3 would ever be returned.

[orange]

I'm not sure I'm following your explanation. You might be able to accomplish what you need by using tables.
You'd need your tblVendor and possibly a tblRegion, or a table VendorInRegion. The issue in your case seems to be related to the fact that your Regions/types are quite customized.

VendorInRegion could have a structure to allow Vendors to be in more than 1 region/type

VendorId
VendorType
where vendorId + vendorType would be a unique composite index.

e.g. Vendor 560 could be Type1 and Type3 etc.