date format help

[mbergman]

I have a text column in a table that contains dates and I was wondering if anyone knows how I convert it to date format so I can do calculations like:
10/10/05 - 10/8/05 = 2?

Here is how it is formated now:
2005-09-27 16:39:00.000

When I try to import it as a date, access doesn't recognize it and gives me import errors and when I try to change the format in design view, it deletes everything.

Any ideas?

 

[SydneyGeek]

You can convert this in a query. Assuming your table field is called ItemDate, build a query that includes all fields from the table except ItemDate. Instead, use this expression --
RealDate: DateSerial(Left([ItemDate],4),Mid([ItemDate],6,2),Mid([ItemDate],9,2))

Now, use the query insted of the table as your record source.
Note -- this assumes that you will always have 2 digits for the month and day components. If not, the expression is quite a bit more complex.

Denis

 

[mbergman]

Awesome! Thanks for the help