multi-value field disrupting forms and queries

[cs1020]

Hello,


A bit of background:


I run a database of students across 2 programs. We track which semester & which program they're part of, but we need returning students' information to display across both programs and multiple semesters with information accurate to each semester (ie test scores and attendance need to show up in the correct semester)


I changed the "program" and "semester" columns from either/or dropdown boxes to multiple checkboxes (ie, being able to select "spring 2016" AND "fall 2016") so that we don't have to make duplicate entries for returning students.


Now when I try to open the relevant forms and queries I get messages that multi-value fields cannot be used in an ORDER BY clause, or that they can't be used in a WHERE or HAVING clause.


Most of the forms and queries are pre-set to sort by program and semester, so I thought these would continue to function normally.


Any suggestions for how to update my forms and queries without having to start from scratch? Is there a better way to track this data?

[CJ_London]

you have to refer to the value property of the multivalue field for criteria. I don't use them myself so can't comment on sorting - except if the multivalue field has a rowsource of say ID and description, it will sort on the id, not the description.

[vicsaccess]

I'm not a fan of multi-value fields but i did find this page
https://support.office.com/en-us/art...3-b6624e1e323a

Down the page it mentions working with queries and changing the lookup field.