Access 2010 Multi value combo box selected items exporting to excel spreadsheet

[jcrump230]

Hi AccessForum community,

I have a Access 2010 multi value combo box trying to export the values selected to a excel spreadsheet. Reading microsoft url: http://office.microsoft.com/en-us/ac...001233722.aspx. If you use in criteria for example: tablename.fieldname(selected items location) it should show all the selected items on one row. That does not work. When I try to export to excel it shows only two letters. For example if November, December, March was selected the only thing that show is No(for November) . If I put the other option tablename.fieldname.value. It works as advertised. It put each month on a different row. I looked all over to try to figure why it is stoping after 2 letters. If I run a query the selected items are showing up complete. here is my code: I probably need some code for multi value occurrences but not sure how to. I am a novice user of Access. thanks in advance.................

Dim oRS As DAO.Recordset, i As Long, sformat As String
Dim oExc As Excel.Application
Dim oWB As Excel.Workbook, oWS As Excel.Worksheet

With CurrentDb.QueryDefs("AllIDNumberLastNameFirstNameB irthdateq")
For i = 0 To .Parameters.Count - 1


.Parameters(i).Value = InputBox("Set parameter: " & .Parameters(i).Name)
Next i

Set oRS = .OpenRecordset
If oRS.RecordCount = 0 Then MsgBox "No records": Exit Sub
End With

Set oExc = CreateObject("Excel.Application")
Set oWB = oExc.Workbooks.Add
Set oWS = oWB.Sheets(1)
'oWS.Name = Me.Combo2.Value

For i = 0 To oRS.Fields.Count - 1
oWS.Cells(1, i + 1) = oRS.Fields(i).Name
Select Case oRS.Fields(i).Type
Case dbDate: sformat = "mm/dd/yyy"
Case dbCurrency: sformat = "$#,##0.00"
End Select
oWS.Cells(1, i + 1).EntireColumn.NumberFormat = sformat
Next i


oWS.Cells(2, 1).CopyFromRecordset oRS
oWS.Cells.EntireColumn.AutoFit: oWS.Cells.EntireRow.AutoFit
oExc.Visible = True

oWS.Cells(1, 1).EntireRow.Font.Bold = True
oWS.Cells(1, 1).EntireRow.Font.Italic = True

[June7]

I don't know what you mean by 'use in criteria for example: tablename.fieldname(selected items location) it should show all the selected items on one row'. Where in the article is that stated?

Export of data from multi-value field should result in each value on its own row.

[jcrump230]

Hi June 7

What I tried to say was that I had for the design mode for my query is Field = Field Name(Unavailability_Grid), Table = Table Name(ParticipantsProfileInfoT). when I click the button(code above). I receive only two characters for example November, December, January were selected. I receive in Excel only No will appear for November and nothing else which is not how Microsoft say it should work. November, December, January should show across in one row. Please read the link above

But when I put in Field = Field Name(Unavailability_Grid.value ), Table = Table Name(ParticipantsProfileInfoT). when I click on the button(code above). I receive in Excel the values November, December, January each on different rows which is what Microsoft states it should appear like that.

I have look everywhere why only 2 characters shows up. I tried doing another multi value combo box but received the same results.

I hope I am clear as mud now

thanks in advance

[June7]

I did read the linked article and can't find any reference to export of multi-value field data. I still don't know what you mean by 'use in criteria'.

Yes, values should show on one line in table, query, form, report but the export must be a query that expands each value to a separate row.

Also, are you saving the full month name into the multi-value field or another PK value?

I NEVER use multi-value field. Most professional developers advise against them.
http://answers.microsoft.com/en-us/o...9-1a65f3512883
http://www.access-programmers.co.uk/...d.php?t=236168