Chris

If you want to fix this problem the correct way, you need to get rid of the Multi Value field and replace it with a lookup table.
Multi Value fields will keep biting your rear end for the lifetime of your application.

Hi Sami,

Yes you're totally right, I'm very much a novice when it comes to this and when i read and watched videos about the Multi Value Field and really don't want to use it I'm just struggling to think of a way of doing it though

Many thanks

Chris

What I did in my form is to put in the on click event of the multi value list box, I set the value to a TempVars and then use the TempVars in the WHERE clause.

Chris,

What are the possible values in the Multi Value fields?

We will work with you to fix this

Hi Sami,

So my database is all about joints. Every joint we work on has its own record with the data being inputed into a single form. These joints can form isolations for several different vessels. On my Form I have a field called Isolation Pack and the value is literally the name of the vessel ie 16F104 or 16G6 or 16F107. So i may need to put all those vessel numbers into the Isolation pack field.

When I open my report it's filtered using the Isolation pack field which is why I'd love to be able to have multiple values in a field or some sort of way around it.

Thank you so much for your assistance!

Many thanks

Chris

One of the hardest things for me about understanding someone's database is to understand their terminology and their business. To me, I don't understand the relationship in the real world between joints, isolation packs, vessels, and all these other terms that are unique to your business. Is it possible that you could relate this to something that everyone would understand, for example, customers, orders, products, that kind of thing? That's why I picked that example from my database because everyone can relate to a retail store. But I don't understand your business, so if you could somehow convert that over to an order terminology, then it would make it much easier for us to help you.

As well as what Richard has said, the isolation pack field needs to become its own child table of whichever table you had the multi value field.
So instead of one field with multiple values, you will have a child table with as many values as you want. This is a proper relational database structure. Now all your searches and filtering become straightforward.

Hi Richard and Sami,

I get what your saying, I use access for something which not a lot people would of thought it's capable of doing but for me it's a fantastic tool. Doesn't help as well that I'm a total novice when it comes to this, I've created practically everything by watching Richard's videos! So thanks for that.

So trying to put it into something you may understand:

I have a database for car spares, on that database there is a record for Brake discs, these brake discs can be used on a Ford Focus or on a Ford Fiesta. I've created a report to show me what spares are required for different cars and when I click on the report to open it asks what car I'm looking for. So I type in ford focus and all the spares including the brake discs come up. I then run the report again and ask for the Ford Fiesta spares but the brake discs don't come up on the report because on the database record i can only have either Ford Focus or Ford Fiesta not both (multiple values)

I hope this example makes sense

Many thanks

Chris

Many-to-Many

That does make sense now. So you have a table of parts and each part has a list of which cars it works with (one-to-many). Now you want to be able to have a form where you select MULTIPLE cars and have it show you a list of all of the available parts that work with ALL of those cars? Do I understand correctly?

Hi Richard,

Yes that's exactly what I'm looking for yes! I've been trying a many to many but just can't make it work!