access date and time formatted as text - formula to convert it to an actual date/time field when run as a MakeTable?

[meekag]

Hi,

Is there a formula that will make Access recognize a date and time formatted as text as a Access Date/Time field? A lot of times I have wonky source data and bring in dates/times as text when I import to avoid paste errors related to those fields. Then I format them as dates/times through queries (and eventually use a formula that concatenates my individual date and time fields. At the moment I am using the formula ArrivalDateTime: Format([Patient Arrival Date] & " " & [(T) Patient Arrival Time],"m/d/yyyy h:mm:ss am/pm"). When I run my Make Table on the query using my concatenated date and time as text, that field is text until I go in to the table I made and manually change the Data Type to Date/Time in design view. I would like for the field to automatically be recognized as a Date/Time when I run the make table query. Down the line I need to be able to do calcs on the ArrivalDateTime field (using my other Date/Time field - DepartureDateTime).

I tried to find guidance on writing the statement as a date and time but I ended up with the aforementioned statement because I couldn't find anything - if there is something out there I apologize for being redundant to this forum, and am hopeful someone will send a link.

Thanks for any help, it is appreciated!

 

[Micron]

If you're saying you want to alter a table field from text data type to date time there is no query that can do this - it's a vba operation that I suspect you should avoid. Perhaps what you're really on about isn't necessary anyway. Much of this sort of data representation is easily achieved with forms or reports. Perhaps some sample data, the desired results and the reason for the manipulation will get you more focused answers.