Datatype mismatch

[ellenr]

My table has an item # defined as text, although it is all numbers ( inherited this). I will be receiving an excel file from time to time with the item # and a value for another field to be updated. My problem is that the Excel file sometimes arrives with the item # as text and other times it is numeric, which gives me a type mismatch in the update query. What is the most efficient solution? I can change the type in the table, but that doesn't solve the problem. Thanks in advance for any ideas!

 

[plog]

What is the most efficient solution?

To store data correctly. If its numeric data, store it as numbers.

Of course, I bet your real question is, "How do I store these numbers in my text field?" For that, I'd need more information. Where are you getting this mismatch? Explain the process further. We're in the VBA section--do you have code that does this? Are you using the import tool? This happen in a query?

Back to the admonishment about not storing it as numbers: How can you be certain your "numbers" are saving correctly even when it works? For example, If I type "00012" into a cell in Excel it saves it as 12. Now if I first convert that cell to a text cell and type in "00012" it saves it just like I type it. Are you losing leading zeroes by using Excel when it comes through as text? Are those leading zeroes important in linking data together? Storing it as numbers in Access solves this--"0012" in Excel comes into an Access number field as 12 making Excel's handling of the text/number irrelevant.

 

[ellenr]

I get that storing numbers as numbers is proper for a number of reasons. If I change the definition of the field to integer in my table and the incoming Excel file, the format of which I have no control, comes in as text, I still need to programatically make it match the table def before the user can run the update query.

 

[MarkK]

which gives me a type mismatch in the update query.

You can use a VBA type conversion function in your SQL to ensure a strongly typed variable. You haven't shown your SQL, but consider something like . . .

UPDATE tTable 
SET LongField1 = CLng([prmLongInput]), TextField1 = CStr([prmTextInput])
WHERE LongField2 = CLng([prmPartNumber])

See how the input values are coerced to a particular type in the SQL?

 

[CJ_London]

how are you importing? If you import to a temp table you can specify the datatype, then run a query to append the data

Or try saving the excel file as a .csv

Personally I avoid appending directly from excel because of this issue

 

[ellenr]

I am importing into a temporary table--how do I specify the datatype? The code lines:

    Dim vFilePaths As Variant
    vFilePaths = fGetFile(e_MultiSelect + e_FileMustExist + e_Explorer, , , , , , _
                          "Choose file to Import", , 4096)
    If Len(vFilePaths) > 0 Then
                strImage = CStr(vFilePaths)
    End If

DoCmd.TransferSpreadsheet acImport, 10, "GHINIndexList", strImage, True, ""
DoCmd.OpenQuery "UpdateIndexesFromExcelTable", acViewNormal, acEdit

I haven't had a chance to try MarkK's suggestion yet--that may be the easiest, but still would like to know how to specify the type.

 

[MarkK]

I would do these steps . . .
1) Import the raw excel data into a temp table where ALL the fields are strings.
2) Analyze that imported data to see if it can be coerced into your final, strongly typed table.
3) Then do the update.

 

[ellenr]

Thanks to both MarkK and CJ. The solution ended up being so easy thanks to your insights. All I did was add a query to empty the input file table whose field was defined as numeric, then import the spreadsheet into it. It changed the field in question to the defined type of the existing empty table. It would change it from numeric to text and vice versa, so I can set my table to numeric and the "temp" table to numeric and even if the excel file comes in as text it converts automatically.