Convert Text to Date

N

Nuno Guerra

I need some solution to this.

I have a MakeTableQuery date converts a text field to date:

Format(CDate(mydatefield), "dd-mm-yyyy")

This MakeTableQuery adds a new column for the conversion text to date,
althought, this new column mantains a text type. When I try to change the
data type tp Date Access returns an memory error. Maybe because my table has
more than 400.000 records.

Is there any way to convert the field to have the data type changed to Date?
 
A

Allen Browne

The result of the Format() function is text, so you don't want to include
it.

If mydatefield is already a Date/Time field, don't apply any function it it:
it's already correct.
 
N

Nuno Guerra

The "mydatefield" field is like a "mmm dd yyyy" format.
I have to change this text to a date like "dd-mm-yyyy". How can I do that to
result in a date field?
 
A

Allen Browne

Is it a Date/Time field in an Access table?

If so, you don't need to convert it. Internally, Access stores date/time
data as a number where the integer part represents the date, and the
fraction part is the time as a fraction of a day (e.g. 0.5 = noon.) The
formatting is only applied at display time, and it depends on the user's
settings.

So, unless the field or text box has something in the Format property that
tells Access to do otherwise, the date/time value will be displayed
correctly for your settings.

If this is actually a text field (not date/time data), that's a different
issue.
 
Top