Access 2013 Type Conversion Failure

[Gretadadog]

Hello. I ran into an issue that I thought was going to be a simple fix but I just can't seem to get it to work.

I have an old Access 2013 db and I'm trying to add a new file/table that has a field with comma delimited years. For example, "2016, 2019, 2020". When importing via a macro I'm getting a Type Conversion Failure error and the application only imports records where that field has a single value (for example, "2016").

I've tried the Text and Memo data types but the error persists. Is there an obvious answer to this that I'm simply overlooking? Any help would be appreciated.

[jojowhite]

maybe what you have on your old db is a Multivalue field.

[Welshgasman]

If you tried Text, it should work. You should split them afterwards as new records.

[moke123]

I'm trying to add a new file/table

Are you trying to import from Excel?

[Micron]

Here's a guess. In Access you're using import from Excel and your first 8 or 10 spreadsheet rows have a single value in that column.

[davegri]

I'm trying to add a new file/table that has a field with comma delimited years.

That is a multivalue field in an Access table. Access does not support a direct import into a multivalue field.
You need to create a temporary table with the import wizard and then use the code below (suggested by CoPilot) to transfer the temp table data into your new (target) table.

Sub ImportMultiValueDataWithAdditionalFields()
    Dim db As DAO.Database
    Dim rstSource As DAO.Recordset
    Dim rstTarget As DAO.Recordset
    Dim rstMV As DAO.Recordset
    Dim valuesArray As Variant
    Dim i As Integer
    
    Set db = CurrentDb
    Set rstSource = db.OpenRecordset("TempTable") ' Change to your staging table name
    Set rstTarget = db.OpenRecordset("TargetTable") ' Change to your actual table name


    Do While Not rstSource.EOF
        ' Add new record to TargetTable and copy regular fields
        rstTarget.AddNew
        rstTarget!ID = rstSource!ID ' Primary key field
        rstTarget!Field1 = rstSource!Field1 ' Copy additional fields
        rstTarget!Field2 = rstSource!Field2 ' Modify as needed for other fields
        rstTarget.Update
        
        ' Handle multivalue field
        valuesArray = Split(rstSource!MultiValueTextField, ";") ' Adjust delimiter if needed
        
        ' Open recordset for multivalue field
        Set rstMV = db.OpenRecordset("SELECT MultiValueField FROM TargetTable WHERE ID=" & rstSource!ID)


        ' Insert multiple values into multivalue field
        For i = LBound(valuesArray) To UBound(valuesArray)
            rstMV.AddNew
            rstMV!MultiValueField.Value = Trim(valuesArray(i))
            rstMV.Update
        Next i


        rstSource.MoveNext
    Loop


    rstSource.Close
    rstTarget.Close
    rstMV.Close
    Set rstSource = Nothing
    Set rstTarget = Nothing
    Set rstMV = Nothing
    Set db = Nothing
End Sub

[Micron]

That is a multivalue field in an Access table. Access does not support a direct import into a multivalue field.

I disagree that that is a mvf. It's a field with comma separated values and should be recognized as text. Here's an import using csv in the first 2 rows but obviously the rest are going to be treated as text because of the values. My point is that it can be done because this import is correct (but the data is gibberish). I suspect the OP's data in the first x rows are single values, so they are being interpreted as dates/numbers.

ID	Field1	Field2	Field3	Field4	Field5	Field6	Field7	Field8	Field9	Field10	Field11	Field12	Field13	Field14	Field15	Field16	Field17
1	1/1/2024,1/2/2024,1/3/2024	46356
2	1/142024,1/5/2024,1/6/2024,1/7/2024	46387
3	dsaddresses	undisclosed	lsaddresses	Lottery	dsaddresses	collected	cage		Group	B	C		Result
4	dsaddresses	undisclosed	lsaddresses	Lottery	dsaddresses	collected	test cage		33	A	A1		1
5	dsaddresses	undisclosed	lsaddresses	Lottery	dsaddresses	collected	test cage dog		A1	B	A1	p	0

[davegri]

I disagree that that is a mvf.

It's the game we very often have to play here with ambiguous information from original posters. I see so many replies to an OP that go on and on with multiple responder's opinions while the OP remains silent.

[Welshgasman]

It's the game we very often have to play here with ambiguous information from original posters. I see so many replies to an OP that go on and on with multiple responder's opinions while the OP remains silent.

I was thinking the same.

[June7]

Provide a sample of source data. If you want to attach file, follow instructions at bottom of my post.

[Gretadadog]

Sorry for the delay, was stuck on calls most of the day.

I'm importing csv files using a macro as I need to update the tables rather frequently. I thought maybe having commas in a csv file was causing an issue. I also thought this was a good suggestion:

Here's a guess. In Access you're using import from Excel and your first 8 or 10 spreadsheet rows have a single value in that column.

So I tried two things:
1) changed the commas separating the years to " / "
2) moved rows with multiple years to the top of the csv to see if Access was getting confused during the import

Neither worked, still getting the error.

I can't provide the database but the file and field in question are very simple - it's literally either a blank, a single year/number, or multiple numbers separated by commas. So for example there is a SALESPERSON_ID column with a unique identifier and then a SALESPERSON_OF_THE_YEAR column that can have a single year, blank, or multiple years.

[Welshgasman]

Make sure that second column is text.

[Micron]

there is a SALESPERSON_ID column with a unique identifier and then a SALESPERSON_OF_THE_YEAR column that can have a single year, blank, or multiple years.

That doesn't seem proprietary, so you should be able to post real data. If not, then you can create a sheet, copy the info, then do a find/replace to change it to something that's not proprietary yet is the same data type as what you have. Then we could try to import into any Access db (I presume you're importing into Access from Excel and the import is creating a new table). OR, you could preface all the cell data in the first 10 rows with a single apostrophe so that a single value of 01/01/2025 becomes '01/01/2025. Note that 10 rows may not be enough - I've never seen any proof of what the row limit is for determining data types for an import. If you noticed, I posted that it is possible to import csv data as I've described. What I didn't test is importing where the cell values after my csv cells are numbers. That's where your data can help.

[Gretadadog]

That second field is text. Ok I've attached a modified version of the csv file with enough information to show the issue. Processing-wise, I have an existing database with tables and when I receive new files, I run a macro that opens, deletes all, then imports data from the new csv files into the tables. This process works fine for the other 15 files I'm importing, this one table/field is my only issue.

I think the problem is related to the commas in that second column but I'm not sure how to get Access to treat it as one text field.

[Welshgasman]

That is very likely the case.
Open the file in excel, swap comma for something else, like a semicolon. Save file and then import.
Could also open and save as tab delimited and import as that.


Edit:

I'm not sure how to get Access to treat it as one text field.

If you set the text qualifier to ", then you can import fine.
I would save the import spec after correcting and use that from then on.