[AccessD] Regarding multi-value field, from a reader

Well, the correct answer is not to use MVFs, and to practice proper
normalization techniques.

That being said, I'd actually store an integer value (see my SumDays()
function below) rather than the text value.

Regardless, if he stores the text as he is doing, or he chooses to save the
integer value,  the following should work in forms and queries:

me.txtOriginal = "WFTM"
? SortDays(SumDays(Me.txtOriginal))
MTWF


Public Function GetDayVal(strC As String) As Integer
Dim ReturnVal As Integer
Select Case strC
    'Case "S" 'Sunday
    '    ReturnVal = 1
    Case "M"
        ReturnVal = 2
    Case "T"
        ReturnVal = 4
    Case "W"
        ReturnVal = 8
    Case "R"
        ReturnVal = 16
    Case "F"
        ReturnVal = 32
    'Case "A" 'Saturday
    '    ReturnVal = 64
    Case Else
        ReturnVal = 0
End Select
GetDayVal = ReturnVal
End Function


Public Function SumDays(strInput) As Integer
    Dim i As Integer, intOutput As Integer
    For i = 1 To Len(Trim(strInput))
        intOutput = intOutput + GetDayVal(Mid(strInput, i, 1))
    Next i
    SumDays = intOutput
End Function


Public Function SortDays(intVal) As String
    Dim strOut As String
    'If intVal And 1 = 1 Then strOut = "S" 'Sunday Character?
    If (intVal And 2) = 2 Then strOut = strOut + "M"
    If (intVal And 4) = 4 Then strOut = strOut + "T"
    If (intVal And 8) = 8 Then strOut = strOut + "W"
    If (intVal And 16) = 16 Then strOut = strOut + "R"
    If (intVal And 32) = 32 Then strOut = strOut + "F"
    'If intVal And 64 = 64 Then strOut = "S" 'Saturday character?
    SortDays = strOut
End Function


Hope this helps,
David McAfee


On Wed, Oct 31, 2012 at 4:08 PM, Susan Harkins <ssharkins at gmail.com> wrote:
>
>  Received the following from a reader:
>>
>> "I set up a multivalued field in Access 2010 using a List Box and
>> providing a Value List with values M,T,W,R,F. Everything works fine but
>> for
>> one problem. When I chose M, W, F from my list, the multivalued filed
>> reads
>> F, M, W ... i.e., the entries are sorted left to right whereas I want them
>> to show up as M, W, F. Is there a way to do this?"
>>
>> =====Other than learning the basics so I could write about them, I don't
>> use them and have almost no experience. Anyone have a quick and easy
>> answer? I'm going to do a little research, but if anybody knows, please
>> share!
>>
>> Thanks!
>> Susan H.
>>
>