How do we bind a date field to a "Value List" combobox with 2 columns?

H开发者_运维技巧ow do we bind a date field to a combobox (with Row Source Type = Value List)?

We setup the combobox like this in the Form Load in VBA:

For i = 0 To 6
    Dim strValueMember As String
    Dim strDisplayMember As String
    strValueMember = format(DateAdd("d", 0 + i, anchorDate), "dd-MMM-yy") & ";"
    strDisplayMember = format(DateAdd("d", 0 + i, anchorDate), "ddd dd-MMM-yy") & ";"
    strItems = strItems & strValueMember & strDisplayMember

Next i
strItems = Left(strItems, Len(strItems) - 1)

Me.cboDeliveryDate.RowSource = strItems

This gives us a neat little combobox with the user able to pick from a list of 7 days. As a bonus he gets to see the day of the week (actually, for this end user, day name is quite important).

The combobox has two columns, a value column (for true date) and a display column (to show the day name).

But there are problems whichever Bound Column we set:

If we set the Bound Column to column 0, it apparently works, but the value stored is 31/12/1899, 1/1/1900, 2/1/1900 etc. So it's just reading the combo's listindex and converting that to a date.

If we set the Bound Column to column 1, it does actually work in that the selected value is written back to the database. But it is never displayed on the form, so the user obviously won't feel that his edit has taken

If we set the Bound Column to column 2, we get a Not In List error.

All the above apply whether or not we use dd-MMM-yy for strValueMember or (as @HansUp answer suggests, yyyy-m-d).

Looks to me like cboDeliveryDate has 2 columns, with strValueMember as the first and it is also the bound column, bound to a Date/Time field. This worked for me by formatting strValueMember in a yyyy-m-d date format.

Private Sub Form_Load()
    Dim i As Long
    Dim anchorDate As Date
    Dim strValueMember As String
    Dim strDisplayMember As String
    Dim strItems As String

    anchorDate = Date

    For i = 0 To 6
        strValueMember = Format(DateAdd("d", i, anchorDate), _
            "yyyy-m-d") & ";"
        strDisplayMember = Format(DateAdd("d", i, anchorDate), _
            "ddd dd-MMM-yy") & ";"
        strItems = strItems & strValueMember & strDisplayMember
    Next i
    strItems = Left(strItems, Len(strItems) - 1)
    Me.cboDeliveryDate.RowSource = strItems
End Sub

I didn't know where anchorDate comes from, so I just substituted today's date.

While a bit complex, you can also create a function that returns the list of values you want. See example here.