Editing Data Type Fields

sibemol

Registered User.
Local time
Today, 15:21
Joined
May 11, 2010
Messages
10
Hello,

I am a new user of Access. I am building a database. I have 3500 "date" records in the following format: "04/21/05 01:30 CDT", the field DATA TYPE in table design is set as "TEXT".

I wish to change the DATA TYPE from "TEXT" to "DATE/TIME". However, everytime I attempt this, an error appears: "Access Encountered errors while converting the data -- the content of 3500 records were deleted. Do you want to proceed anyway?" If I click yes... all the records are deleted.

Please help. Thanks.
 
can you run a query that gets the record primary key and the date field, converts the date and makes a new temp table of the data.

Change the original table field format to match the temp table date format and then run an update query that updates the now empty date field in your original table with the date values in your temp table.

You could copy the original table just in case all went :eek:
 
All I have is an Excel File with all the records. I imported to access because I will be expanding upon the database in the future.
 
If you have imported into access then you have at least one table in access and this is the table with the date field you want to change the format of?

Have you done any query work?
 
When I imported, the only way I was able to import was as "Text" instead of "Date/time". Everytime I attempted to import as "Date/time" , the records would get deleted.

I also attempted to change the cell format in excel to "Date" instead of "General", this did not solve the problem either.

I haven't done any query work? what do you mean?
 
There are a few things I think you need to do.

1. check your access help (F1) and search on query just to get a general idea.

2. Create a copy of your table. Ctrl C ctrl V and rename

3. Sort out how to change the format of your date without losing for good the data.

You can change data from date to text and reverse so you should not have lost any data as such, just a mater of converting without losing it. Refer step 2 above.

I can help with the query stuff in 30 mins or so unless others have assisted in the meantime.
 
The problem is that your "DATE/TIME" field is not really a date/time datatype. The correct format for a date/time datatype does NOT include the CDT on it and would instead be:

04/21/05 01:30 AM

Access' date/time field does NOT store the time zone.
 
Do you know of an easier way of deleting the "CDT" rather than deleting it 3500 times in each record? Thanks.
 
You could use an Update Query like this:

UPDATE YourTableNameHere
SET YourTableNameHere.YourTextFielNameHere = Replace([YourTextFielNameHere ],"PDT","");


And then you should be able to convert.
 
I'm sure those are really great ideas. However, I have no clue what an update query is, much less how to run it. As I progress in my learning curve they will make more sense.

Nevertheless, your message triggered something on the left side of my brain that may help others with a similar issue---> The old "Find/Replace". I only had to repeat it three times "CDT", "PDT" and "EDT"... I left the replace box empty and then replaced all, and they were deleted--- that's very rookie! I know!.

After that I was able to successfully change the data field to "Date/time"... Sincerest thanks.
 

Users who are viewing this thread

Back
Top Bottom