Convert Number to Date

[Rob]

I have a number field that I want to convert to a date type field. The field
is in this format: 200706 where the year is 2007 and the 06 is the month. I
want to convert this to a field where I would have a date field of month and
year, i.e. 06/2007. Then I want to delete any data that is 18 months older
than this date. I have a query that first converts the field to a number
field of 06012007 and another query that converts this to a date field
DateSerial(Left([FieldName],4),Mid([FieldName],5,2),Right([FieldName],2)).
Then I was going to calculate 18 months back and run a delete query for that
portion of it. I am sure there is a way to avoid 1 query and just convert
the 200706 to a month year field and calculate 18 months back from there.
Can someone please help.

Rob

 

[Ofer Cohen]

First Back Up your data, so you'll have an option to recover

You can try something like

Select * From TableName
Where DateSerial(Left([FieldName],4),Mid([FieldName],5,2),1) <
DateAdd("m",-18,Date())


Adding 1 to the day in the DateSerial, that way we avoiding another query
And the DateAdd will subtruct 18 month from the current date

 

[Stefan Hoffmann]

hi Rob,

I have a number field that I want to convert to a date type field. The field
is in this format: 200706 where the year is 2007 and the 06 is the month. I
want to convert this to a field where I would have a date field of month and
year, i.e. 06/2007.

=DateSerial(Left([yourField], 4), Right([yourField], 2), 1)



mfG
--> stefan <--