Importing and defining a different date format

J

Jim T

I am importing a large number of files from another system and each file has
a large number of date fields. All of the date fields are in the form of
CCYY-MM-DD. I will be importing the files into temporary tables and then use
a query or program to map them into the permanent tables. The importing
tables will have the date fields will come across as a text fields (I'm not
setting up import specs). I would like the the receiving permanent fields to
be a date field; but, keep (store?) the fields in the CCYY-MM-DD format for
displaying to the users. I would like the permanent fields to be a date type
so the various date functions (dateadd etc.) can be used.

Ideally is there a way to create a format (CCYY-MM-DD) that would appear in
the field definition screen under the drop down list for formatting?

If not, what would be the best alternative so I do not have to format each
field with the format function.
 
J

Jackie L

My experience is that the "-" are not welcome in a date format. You can use
yyyy/mm/dd easily and it is seen as a date. Use an input mask of
9999/99/99;_; with this format for ease of data entry. You can put the
format and input mask on the field in the table and it will carry onto any
subsequent forms. Otherwise, add on the field properties of the form. To
get the data from your original text into a date format, on the query to
append the data into the actual table (or whatever else you are using to
append), use
Format(DateSerial(Left([OrigDate],4),Mid([OrigDate],6,2),Right([OrigDate],2)),"yyyy/mm/dd")

Hope this helps.
 
D

Douglas J. Steele

Dates in Access don't have any format: they're 8 byte floating point numbers
where the integer portion represents the date as the number of days relative
to 30 Dec, 1899, and the decimal portion represents the time as a fraction
of a day.

In other words, as long as you've imported them as dates, you can display
them to your users any way you want. Simply set the format to yyyy-mm-dd
(although you may need to use yyyy\-mm\-dd to ensure the dashes as the date
separator)
 
Top