Date format

[Rusty]

Hello,

I am working with an existing database and have a table with a field called "Year". However, the data in it is not in date format, it's just text.

The year data is entered in the format of "1997/98" and "1998/99" and so on.

Is there a way of changing the data type to Date/Time and being able to specify a custom date format? I can't be losing the data and some people here are entering actual text into this field by accident (I know don't get me started!)

Thanks,

Rusty

 

[FoFa]

Why not just change it to an integer field? You could even setup a input format in your forms to allow only certain ranges or lengths. If you use a date format, what are you going to require for the month/day? What ever you do, I would setup an additional field, then use queries to move the data from the text field to the new field. When you are done, and certain the data is as good as it gets, delete the old field, then rename the new field to the old field name.

 

[Oy Vey]

Hello,

I am working with an existing database and have a table with a field called "Year". However, the data in it is not in date format, it's just text.

The year data is entered in the format of "1997/98" and "1998/99" and so on.

Is there a way of changing the data type to Date/Time and being able to specify a custom date format? I can't be losing the data and some people here are entering actual text into this field by accident (I know don't get me started!)

Thanks,

Rusty

You could try this... create a lookup table with a key field and the acceptable ranges if you are going to continue with the year/year format. Link back to the original table after creating a new foreign key field or converting the data in that table to match the new lookup IDs. Convert the control to a combo box so the user has to pick one of the valid entries only.