Hi Didacticone,
Are you working solely with tables/queries or are you trying to accomplish this on the form? Basically, are you trying to store this number/date anywhere or just convert what the user sees?
If you've already established the field as a number field on the table, then you won't be able to turn the existing field into a date field, if by "turn" you mean "convert".
What you can do is create a new field in your table with a date/time data type then use a query to update the value using an expression and move it into the new date/time field.
Let's pretend this is your table structure:
-
Table Name: tblDates
-
Field: ID (Autonumber)
-
Field: NumericDate (Number)
-
First, you would go into Design View for tblDates and add a new field, called ConvertedDate for the sake of explanation, and give it a Date/Time data type. Close the table and save it.
Next, you would create a new query. In Design View, click on the Query menu (I'm using 2003, so forgive me if you're using a later version and the menu isn't right) and select Update Query. Add the ID, NumericDate, and the newly created ConvertedDate fields to the query.
In the Update To section in the query grid, enter the following expression:
-
CDate(IIf(Len([NumericDate])=7,"0" & Left([NumericDate],1) & "/" & Mid([NumericDate],2,2) & "/" & Right([NumericDate],4),Left([NumericDate],2) & "/" & Mid([NumericDate],3,2) & "/" & Right([NumericDate],4)))
-
The expression assumes that you've entered an 8 digit number for the date, but Access has trimmed the leading zero for months 1-9. Then, the expression adds a "/" after the month and day. Finally, since the expression is created as a string, the CDate function is encompasses the expression to convert it back to a date.
The last step would be to go to the Query menu and click Run. You should get a message telling you how many rows are updated. After clicking Ok, you can double-check the table to make sure that the rows were updated properly.
I should also mention that the expression doesn't handle null values, so you'll either want to add that to the expression or ensure that you have no null values in the table before you start.
If you are trying to accomplish this on a form, just add the expression I provided above to an unbound textbox and change the field in the brackets to match the name of the date field on the form.
Hope this helps and good luck,
beacon
266
