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.

 

[Pat Hartman]

You are holding two pieces of information in a single field and that violates first normal form. If the second year is always first year + 1, there is no need to store it at all. If the range can be longer but not consistant, I would recommend using two fields.

Changing the field's data type to date would not solve your problem since the values you are storing are not valid dates.

FYI, there are both SQL and VBA functions named "Year", you are courting problems by naming one of your objects with the name of a function. Change your name to SchoolYear or AccountingYear or whatever the year represents.