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

A user who can't normalize data can't (usually) implement a VBA procedure. 
If I send this to him, it's just going to create more work for me! ;)

Susan H.


> 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.
>>>
>>
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com