Access - change Data from Text to Date

Use:

CDate(YourTextField)

mx

Ok, I have used CDATE already in the first source file, where the date field was listed in the Text form:

2008-07-23 00:00:00

I use the formula:

CDATE(Left([Original Service Date],10))

and this leaves the date looking like:

7/23/2008 as a Text

I know want to convert this to Date/Time.

I attempted your suggestion above, but this did not change the format to Date/Time

Thanks.

"I know want to convert this to Date/Time."

That's what CDate() does.  It my 'look' like 'text', but it's been converted to a real Date/Time data type:

From the Help File:
Function     Return Type          Range For Expression Argument
CDate         Date                       Any valid date expression.

mx

Where are you using this ?

mx

Not sure what you mean by "Where"

I run an update query, using CDATE.

If the field has been changed to Date/Time, then why in Design viwe of the table does it still indicate Text. Also, when I sort, it does not sort chronologically...that is 1/1/2009 appears before 10/31/2008.

Thanks.

ohhh ... well, that function is not going to change the *design* of the table, only the data type on the fly.

Can you make the change from Text to Date/Time in the table design for that field?

mx

No, I cannot, I run out of memory. The original table has over 165K records

Please find one sample row attached with all information removed except the field with the date in Text format.

The file is not uploading. Sorry

Standby .. I will give you another way to change the data type w/o opening in design view.

mx

Truly appreciated...thanks!

ok .... execute this line of code from the VBA Immediate window ... but first:

Make a backup.
Be sure the table is not open or in use.
Change the table and field names accordingly.


From the Immediate window (note - do not put a ? in front of this - like you sometimes do)
CurrentDb.Execute "ALTER TABLE [" & "YourTableName" & "] ALTER COLUMN " & "YourFieldName" & " DATE"

mx

Yes, you know me...

I have created a form, with the button to launch the code (not sure waht you mean by immediate window). I get an error 3293 message on the line beginning with CurrentDb.

Private Sub Command0_Click()
CurrentDb.Execute "ALTER TABLE [" & "B" & "] ALTER COLUMN " & "Original Service Date" & " DATE"
End Sub

Thanks

Will CAST work on a field? I will look into this as well.

Thanks.

ok ... the field name has spaces ... try this:

CurrentDb.Execute "ALTER TABLE [" & "B" & "] ALTER COLUMN [" & "Original Service Date" & "] DATE"
added brackets .............................................................................^ ..............................................^

mx

CAST is not supported in Access.

mx

Now, I get the error System Resources Executed - Run-Time Error

wow.  Well, can you reboot ... don't open any other apps, then try this?

If not ... can you uploaded the backend ... Compact and Repair first, then zip it up.  

In fact,  do a Compact and Repair and then run it again.

mx

Yes, I am compact and repairing. Our computers are now configured so we log-off, we do not restart, and system maintenance does what it needs to every evening.

I know I could just also hit the on/off switch, and will try this as well.

Thanks.

Still get error 3035 - System resources. It is a large table - about 165K

Thanks.

Actually, is this a local table or a linked table on the server ??

mx

I restarted my computed, still no luck.

This is a local table.

I am now working on the suggestion of copying the Text field, but already know I will get a lack of memory error (this has happened before)

See my last two posts above.

mx

I tried your suggestion.

Created an update query, after a new Date/Time field was created. In the update to space under the newly created field, I entered "Original Service Date" and ran the update query.

No records were updated.

Thanks.

I do not mind leaving the field as Text, as long as I am sort as if it were a date, but in Text format, Access does not read it this way.

Thanks.

Thanks.

So,  you got it to work?

mx

Yes, I have tried the Update query before...but it never worked due to lack of memory space, so I did not even think of doing an update query again.

We recently re-installed all applications on my computer, so this may have had something to do with it.

Thanks again for everything.

You are welcome.

mx

You are all the best...be well.