Format Date and Time

  • Thread starter Thread starter Brandy Hansen via AccessMonster.com
  • Start date Start date
B

Brandy Hansen via AccessMonster.com

I have a date/time field that I am importing from another system into Access.
It's in this format: '2005-05-17-00.00.00.000000'.

Access will only import it as a text field. The import Wizard protects the
field so that I cannot change it to a date field during the import.

My problem is that once I have the date field in Access and try to change it
to a date field, it deletes all the dates in that column.

If I leave it as a text field, I can't use it in my query to count days or
compare dates.

Does anyone have a solution?

Thanks,
Brandy
 
Brandy said:
I have a date/time field that I am importing from another system into
Access. It's in this format: '2005-05-17-00.00.00.000000'.

Access will only import it as a text field. The import Wizard
protects the field so that I cannot change it to a date field during
the import.

My problem is that once I have the date field in Access and try to
change it to a date field, it deletes all the dates in that column.

If I leave it as a text field, I can't use it in my query to count
days or compare dates.

Does anyone have a solution?
Click to expand...

Importing data from non-Access sources often requires initially importing
into a work table and then using append queries to move the data to the
final destination tables. The append queries are capable of using whatever
expressions are required to "massage" the data so it is compatable with the
DataTypes in the destination tables (something you cannot do during the
import itself).

In your case your append query would use the expression...

CDate(Left(FieldName, 10))

....to produce a date from the text string you have now. If the time portion
is not always midnight as in your example and you need the time as well as
the date then you will need to replace the periods with colons and grab more
than the first 10 characters.

CDate(Left(FieldName, 10) & Replace(Mid(FieldName, 12, 8), ".", ":"))
 
It still leaves the date fields blank. I created an append query and for the
"enteredDate" field I placed the following in the property format field:
"CDate(Left(ENTEREDDATE,10))"

I also tried to place the code in the criteria section of the query, but
received a data mismatch error.

What am I doing wrong?

Brandy
 
Should I use the SQL view to insert this code? Where would I place it?

Thanks,Confused
 
Ok. It took me all day, but I got it to work with the CDate.
 
Back
Top