Change Data type

[globoman]

Hi, first of all let you know I'm new on this, but this forum has been very helpful and I've learned a lot. But know I'm blocked. Let's try to explain my problem.

I receive from a different set of people a couple of excel files with a report. I've managed to build a module where I can select the file I want to analyse and import it into access and then run a couple of queries I've built.

Everything runs fine for the first file, but for others I start to get problems as some of the queries start to show "type mismatch in expression) I've noticed when people start to play around with the files some of the data types are changed, so what it should be a number is imported in access as text and what it should be text access has decided it will be now number

As the excel files are always going to have the same headers, is there anyway in VBA to tell access to import field " X" always as a text and field "Y" to be always a number. Or in other words how can I change the data type automatically?

[Demosthine]

Evening There.

First order of business, welcome to the Forum.

Now, to answer your questions, there are methods to either use a pre-defined table for the Import or if you are using an actual Query to get the data from Excel, you can use the standard Type Conversions (Str, Int, etc.) when you execute the Query.



SELECT Str([Date/Time Received]) AS [DateTime] FROM OutlookMessages;


Since the Columns will always be the same, you can also open the Excel Workbook prior to Importing, update each Column's Format to be correct, save the file, and then Import it.

Otherwise, please post some code so that we can isolate the problem better.

Enjoy.
Scott