Seperating info from 1 cell within a form to multiple cells within a query

[Mpeterson1022]

Hello,

I'm trying to do what is typically done within excel using the "Convert Text to Columns" wizard (Delimited) and a "Paste Special - Transpose" function within Access.
For example, within the form in access, I have several dates listed, seperated by a comma (1/3/16, 1/5/16, 1/6/16, 1/9/16, 1/11/16, 1/13/16, 1/17/16, 1/19/16, 1/20/16, 1/22/16, 1/23/16, 1/24/16, 1/27/16). All within one cell.

I want to know if there is a way to seperate by commas and put each date into it's own cell going down a column, so within the cells, it would be listed as something like this:
1/3/16
1/5/16
1/6/16
1/9/16
etc...

Is there a code I could use within a query, so each date would be on it's own line within it's own cell. If this code could also remove any duplicates, that would be amazing.

Thank you for any help anyone can offer.

 

[gruntingmonkey]

You should be able to fiddle it to what and where you want

Sub Macro1()
    
    Range("a1").Select
        
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
        Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _
        )), TrailingMinusNumbers:=True
    Range("A1:M1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Rows("1:1").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    
Range("A1:M16").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
        "B1"), Unique:=True
    
End Sub

 

[Joe4]

gruntingmonkey,
That is an Excel solution, and the OP is asking for an Access solution (mentioned that in the question and the question is posted in the Access forum).

That being said, if at possible, it probably would be easier to clean up the data in Excel than Access. So if that is a possibility, to clean the data up in Excel before importing to Access, that might be a good option.

Otherwise, if done it has to be done in Access, I would probably use VBA and recordsets to loop through the values. That is a little bit of advanced programming.

 

[gruntingmonkey]

Whoops - apologies, was on an answering mission and I completely lost track.

 

[Micron]

Mpeterson1022: this can be done, but not by building a query. I would assign the value list to a variable, extract each date based on a FIXED delimiter (such as a comma), in order to pass the value to a recordset or append to a table directly. I don't know how to deal with non-text delimiters such as TAB. I suppose it's possible but I'd much rather not have to try.
I would need to know:
- how you're getting this csv list, e.g. is it a query or a Dlookup?
- the name of the target table and field name where you want each value to go
- if you need to eliminate duplicates, two ways come to mind. The easiest way for me would be for you to make the target field a primary key (no duplicates allowed) and I would append each value as found to the table and trap the error that will result. This will mean the append query runs for each extracted value - less efficient for sure, and maybe an issue for you if going over a network. If this db is local to you, you'd never notice the lag unless there's thousands of records to append in one go. Then again, on a good network, we're only talking seconds, not minutes.

The other would be to assign the values to a custom collection which has a PK, then loop through its members and write them to the recordset. HOWEVER, if you put a PK on the target field, the update process will fail entirely when it encounters a duplicate table value that is already there, and nothing will get appended. If you do not want any duplicates in the table at any point, the first method is required. You could also allow the process to enter a value that might have been put there in the past and you make a Find Duplicates query and use that as the basis for a delete query.

Some things for you to think about before getting started. Try to be specific about object names (tables, fields, queries) and the process involved if you intend to proceed.