Running a SearchForm with unbound textboxes from an Access Query

[Boost]

Hi there,



I have a query that pulls related data from two tables - thus allowing me to run searches of the data from one data-source.

I have created a very basic search form, that contains unbound textboxes related to the query via criteria formatting.

I am able to do basic searches on static data - IE. that of surname, initials, ID number, etc.

However, when it comes to attempting to search multi-value fields from the query such as language (which has a multi-value list containing a list of 100 languages) my search form falls over.

The criteria that I have applied to the single-value fields is:

Like "*" & [Forms]![SearchF]![txtRank] & "*" 

or

Like "*" & [Forms]![SearchF]![txtSurname] & "*"

What criteria should I be adding to the multi-value fields for them to show up in my search form? I think my issue is that not every data record has a value listed under each multi-value field. IE. the record may not have a language set, which means the record is empty. But i don't know what criteria to apply to ignore this issue.

I learnt most of what to do from this YouTube video - and have set everything in accordance to what has been outlined here - however do by now have a better understanding of access processes.

https://www.youtube.com/watch?v=CTiA_4Me0cl&t=379s

[isladogs]

You've discovered for yourself one of the reasons why most experienced developers do not use multivalue fields.
They are not easily searchable by standard methods due to the way the data is stored.

My advice would be to scrap the MVFs and store the items as separate records.

[Boost]

Cheers for the feedback - I've seen that written elsewhere too.

I almost have no alternative - unless you could indicate how it would be possible...

I have been developing a skills and qualifications database that is related to already held company data. Therefore, I have had to relate employee ID's to their particular skill ID. Within the skill ID record, I have multiple columns each containing particaulrs such as the of the particular language, particular qualification etc.

How could I split these multi-value fields in a streamlined fashion?

Cheers for your feedback.

[isladogs]

It's late here and I'm about to log off so I can't provide details.
The Split function could be used to separate out all the values into separate records.
Suggest you google how to do so.
Make sure you backup your data before trying to manipulate it

[Boost]

Have since shifted my focuses - and have started an alternative forum post that develops on the advice of Ridders52. It can be located:

https://www.accessforums.net/showthr...505#post402505