Converting TableData Type

[TimMoffy]

I have an access database where the data source is an Excel S/sheet.



One of the fields that i import is a text field that shows when a file has been reopened. Unfortunately the data in it is not just a date but reads " file was reopened on 1/6/2012" ( or whatever the data was)

I have run the query succesfully to split this out into a new field that shows only the date. However because of the way the field is imported it is always text and therefore after running the queryto split the data it is still text, which is of course no good for future queries etc.

The source data is updated daily and therefore even if I change the data type manually for now I will need to move on at some point and I dont really want users to have to go in and change the field on adaily basis.
All of the import is automated so my question is..............Is there a way in VBA to change the data type of this field from Text to Date.

The table is called tblReOpenDates and the field is dtmReopened.

Thanks for your help as always.

[JoeM]

I don't know if all that is really necessary.

If you can create a query that dynamically splits the field so you get your date (and you can use DateSerial or DateValue functions to change the format from Text to Date), there is usually no reason to write/save those values back to a field in a table. Usually, a calculated field is enough, as just about anything that you would use a Table as a data source for, you can use a Query equally as easily (i.e. other queries, forms, reports, data exports, etc).

[TimMoffy]

Hi JoeM
Thanks very much for your reply. Not sure I totally understand.
The query I have created is a make table query , which exports the date and a file ref from the main table that is imported, and i then use a join between the file reference fields going forward for all the queries.

I have set the properties to the field which splits the date out of the text to Date, but this seems not to make any difference.
Are you saying i could or even should set this "splitting" as a dynamic action and not have to create a new table?

[JoeM]

Are you saying i could or even should set this "splitting" as a dynamic action and not have to create a new table?

Yes. Is there any need to physically create a new table if you can just use a dynamic query to get what you need/want from the original table?

[TimMoffy]

Thanks JoeM
This seems to work really well until i add the final part of the query.
What I need to calculate is what is the time lag between a file being closed the first time and the re- open date. I have put the final calculation into the same query but, not surprisingly, i get an error on every occassion, presumably because it is trying ot make a calculation based on a dynamic field? The query grid is shown below.


I'm guessing that because of this I need to store the new date in a table and then join them togehter via a common field to do the calculation?

[JoeM]

presumably because it is trying ot make a calculation based on a dynamic field

That should not be a problem. You might be trying to do too much in a single query. Try doing your TimeLag calculation in a new query where this other query is the Source. Note that you can create queries based on other queries (or use subqueries!).