Access - change Data from Text to Date

tahirih
tahirih used Ask the Experts™
on
Hi,

I have a text field, in the format 1/1/2009.

I would like to change this to Date/Time so I can do proper date sorting and querying. I get a message indicating there is not enough memory space.

Please offer how I may alleviate this problem?

Thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Use:

CDate(YourTextField)

mx

Author

Commented:
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.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
"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
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Where are you using this ?

mx

Author

Commented:
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.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
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

Author

Commented:
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.

Author

Commented:
The file is not uploading. Sorry
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

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

mx

Author

Commented:
Truly appreciated...thanks!
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
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

Author

Commented:
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

Author

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

Thanks.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

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

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

mx
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
CAST is not supported in Access.

mx

Author

Commented:
Now, I get the error System Resources Executed - Run-Time Error
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
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

Author

Commented:
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.

Author

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

Thanks.
Database Architect / Application Developer
Top Expert 2007
Commented:
"It is a large table - about 165K"

Well, not really that many records.  I would like to try it on my system.

Another work about is ...
Add a new field to that table with DateTime data type.
Run an update query to copy the dates from the Text field to the new field.
Confirm, then delete the old field ... then rename to new field to the old field name.

mx
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

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

mx

Author

Commented:
I restarted my computed, still no luck.

Author

Commented:
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)

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
See my last two posts above.

mx

Author

Commented:
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.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007
Commented:
You need to enter:

[Original Service Date]

No quotes.  Need brackets.

mx

Author

Commented:
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.

Author

Commented:
Thanks.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
So,  you got it to work?

mx

Author

Commented:
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.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
You are welcome.

mx

Author

Commented:
You are all the best...be well.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial