Mulivalue Field Issue

[abbaddon223]

I know, I should have stayed well clear to start with.... :banghead:

Any help on this would be greatly appreciated!!

I have two tables: 1 is "tb_Q&A_PPI". This contains a field called "Lender"

I have another table: "tb_Q&A_PPI_Matrix" which has a field "Excluded_Lenders"

The field "Excluded Lenders" is a mulivalue field. Example values: Ocean Finance, HFC

I want to build a select query that takes a single value from the "Lender" field in tb_Q&A_PPI" and says, 'f the value in that field is like a value in "Exlcluded_Lenders" don't return it'

I was going with having the value "Lender" in the query grid, then the criteria being Not Like [tb_Q&A_PPI_Matrix]![Excluded_Lenders]

Which gives me a "You cannot use a Have or Where query on a mulivalue field"

Can any one help please?

 

[spikepl]

I know, I should have stayed well clear to start with

but you are into autoflagellation, and just couldn't keep your hands off?

If not, then it is not too late to drop the multivalued fields, and avoid a lot of future pain.

 

[boblarson]

In order to do this you need to use this in your field selection area on the query by example grid (the query designer):

FieldName.Value

So, normally in a query you use just FieldName but with multi-valued fields (and I agree with spikepl that mvf's are a pain) you need to use FieldName.Value