DatePart not working... and can't change field type to Date/Time

[sarahji]

Hi all,

I'm trying to group records by year. Tried typing in the following in Design View:

Year: DatePart("yyyy",[SurveyDate]) into the Field row in Design view. It yields #Error all the way down.



Then I realized my SurveyDate field was a "Text" field, not Date/Time. Figured that's probably the problem. So tried to convert it to Date/Time and got the following error:

---"Microsoft Access can't change the data type. There isn't enough disk space or memory."

Did a search on this forum and found that this is a common problem with big databases (mine's 534,000 records). So I tried to create a new table with all the same fields -- but with SurveyDate as a Date/Time type, and append all the data from the old table into it.

HOWEVER!
I'm getting the following error:
---"Microsoft Office can't append all the records in the append query.
Microsoft Office Access set 534582 fields to Null due to a type conversion failure"

Any ideas? I wasn't sure how to do the other option suggested in that thread: creating a make table query and using the str() and lng() function to change the data type. [Here's a link to that thread: http://bit.ly/qvzdN7]

Help, o brilliant Access wizards!

[pbaldy]

What does the data look like in that field? Is it consistent, and are any blank? It sounds like Access isn't recognizing it as a date. You may need to use String functions to populate a date field.

[sarahji]

No blanks, and they're all in the format yyyymmdd.

[pbaldy]

I'd try either in your append query or an update query (to set the value of an actual date field):

DateSerial(Left(FieldName, 4), Mid(FieldName, 5, 2), Right(FieldName, 2))

Replacing FieldName with the name of the text field.

[sarahji]

Ahh, ok, so this would create an additional field in the original table that has a data type of Date/Time? I think that makes sense.

I suppose I could also just do a Left function instead of a DatePart in the query, and return the first four characters, if I just want a quick fix. (Duh, why didn't I think of that earlier?)

[pbaldy]

You can certainly use Left() on the existing text field. I would probably change the field from text to date/time, since that's what it is, particularly if you may want to do any kind of querying or math on the data. You could do that by creating a new field of date/time type, running an update query to set the values, then delete the text field and rename the date field to the original name (if appropriate).